Connecting to a MySQL database using SQLAlchemy

Pylons uses SQLAlchemy by default, but before we start building models in a Pylons project, lets see if we can connect to MYSQL database from a regular python script....

First step, lets make sure you have a MYSQL server running on your machine. I am developing on a MAC, so go look for the mysqld process in your Activity Monitor:

If you have the mysqld process, your good to go because you have a server up and running. I believe OSX starts this server at boot by default. If, for some reason, you do not have the server running, please following these instructions to get it up and running before moving on:

http://developer.apple.com/internet/opensource/osdb.html

Next, lets connect to the database using the open-source tool Sequel Pro. Connect via the standard tab, and set your host to the local ip address 127.0.0.1, your username to root, and keep the password blank. Use the default port setting of 3306 also. Unless you have explicitly set the root password for your SQL server, your password is blank by default.

After you connect to the server successfully, create a new database called "mydatabase". In the database, create a single table called "mytable" and add one column to the table of type varchar(50). The column (attribute) should be called "name". Add three names to the table. You should have something like this when you are finished?

Now everything is setup, lets create the python script to connect to your database, execute a simple query, and then display the results of that query to the terminal. First things first, you need to import the SQLAlchemy package into your namespace so you can use the module:

from sqlalchemy import *

Next, use the create_engine function to connect to your new database. SQLAlchemy using the following format for connect to MYSQL databases:

'mysql://username:password@serverlocation/mysqldb_databasename?charset=utf8&'
use_unicode=0'

Following this format, our connection call will look something like this:

engine = create_engine('mysql://root@localhost/mydatabase?charset=utf8&use_unicode=0', pool_recycle=3600)
connection = engine.connect()

Notice I am leaving off the password section of the string because our password is blank. I had to try a few different methods before I figure out this is how you get the connection to work successfully.

If your current script executes without an error, you are successfully connecting to your database. The final step is to execute a query and display the results. Lets just display all of the names in the mytable table using a SQL SELECT statement:

result = engine.execute("select name from mytable")
for row in result:
print "name:", row['name']
result.close()

Execute the script one last time and you should see the contents of your name column printed to your terminal. Note: I have no clue how to do this on Windows, and if your serious about building a website, you should be using a MAC anyways.