SQLAlchemy is an open source toolkit for database management mainly though ORM for Python and is release under MIT License. This blog post neither a tutorial nor a self-explanatory sketch of SQLAlchemy. Its merely the info I would need from time to time for my small foolish and dumb applications . I write them so that I can come to them from time to time and I publish them so that if ever anyone needs intersect mine, then great. I’m going to concentrate only on using ORM here.

Connection to the database is done through the engine as shown below:

Now, since I’m going to use MySQL, I need a dialect tailored for MySQL. The following codes shows how to create an engine:

1
2

from sqlalchemy import create_engine
db = create_engine(URL,arg1..)

where the URL is of the form dialect+driver://user:password@host/dbname[?key=value..]. You can have a look at the list of argument to be passed when creating the engine here. For a list of URLs for specific system, have a look here. Whenever I work, I like to set echo=True just to see the SQL statements being generated.

For MySQL, ensure that you have the library that perform the connection between Python and MySQL. Here is how you can install it:

1. easy_install mysql-python (mix os)
2. pip install mysql-python (mix os)
3. apt-get install python-mysqldb (Linux Ubuntu, …)
4. cd /usr/ports/databases/py-MySQLdb && make install clean (FreeBSD)
5. yum install MySQL-python (Linux Fedora, CentOS …)

Now that we have configured the system, we can actually start creating our classes and using the proper construct, all tables gonna generate automatically using the ORM.

1
2
3

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('mysql://username:password@localhost/databasename')

Ok, build from the previous code, now suppose that we have define a user like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    username = Column(String(15))
    displayName = Column(String(10))
    password = Column(String(30))
    def __init__(self, username, displayName, password):
        self.username = username
        self.displayName = displayName
        self.password = password
        
    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.username, self.displayName, self.password)
Base.metadata.create_all(engine)

The last line, you can comment it from time to time, its this line that automatically creates the tables in the database. Now, lets check in our database. So, we login on mysql and we view all tables show tables;. We view the table user and if we want to see its struture, we just describe user;

Add data to the database

1
2
3
4
5
6
7

username = "noorani"
password = "bakenoor"
displayName = "noor"
emailAddress = "noor@gmail.com"
newUser = User(username, displayName, password,emailAddress)
session = getSession()
session.add(obj)

In fact, several objects can be added in a single command by passing all the objects in a list and then passing this list as an argument to session like session.add_all([newUser,newUser1,newUser2]). Whatever operation done by session stays pending until you finally commit through session.commit().

Querying data from the database

Suppose we want to validate a user with a particular username and password:

1
2
3

username = "noorani"
password = "bakenoor"
user = session.query(db.User.id).filter_by(username=username,password=password).first()

By doing user.username or user.password, you get the username or password respectively. SQLAlchemy has a number of filters:

• Equals:query.filter(User.name == ‘ed’)
• Not equals: query.filter(User.name != ‘ed’)
• Like: query.filter(User.name.like(‘%ed%’))
• In: query.filter(User.name.in_(session.query(User.name).filter(User.name.like(‘%ed%’))))

As I use SQLAlchemy and gets new stuff, I’m going to post it here

All info to write this post came from:

1. http://stackoverflow.com/questions/454854/no-module-named-mysqldb
2. http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html