Tuesday, July 13, 2010

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:


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, 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:


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']

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.


  1. I have read this post. collection of post is a nice one ..that am doing website designing company chennai india and website development company chennai india. That I will inform about your post to my friends and all the best for your future posts..

  2. Thanks for providing this informative information you may also refer.

  3. This comment has been removed by the author.

  4. It has been the prototype for many other tools that have come about since. To learn more about mysql designing, visit on hyperlinked site.

  5. There are some companies who offer to provide this service to a client for the cost of installing a server and setting it up in one place. Other companies also offer hosting for those who wish to use it for themselves. To get more detailed info on London Datacentre, visit on hyperlinked site.