15.07.2009

From the toolkit website, “SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.
It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and
Pythonic domain language.”

The SQLAlchemy used for this tutorial is 0.5.5 (with python 2.6, sqlite included). To find out what version do you have, open a python console and type…

$ python
Python 2.6.1 (r261:67515, Mar 29 2009, 14:48:32) 
[GCC 4.1.2 20071124 (Red Hat 4.1.2-42)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'0.5.5'

If you don't have it, take a look at the Howto install section from the bottom of this page.

For this first step, we'll be using a separate file, in which we'll include the main stuff, later used in the other files. We'll call this common.py.

Create a table in a database, a class and map these two.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
 
# --------------------------------------------------------
# create a table in a database, a class and map these two
Base = declarative_base()
class Customers(Base):
    '''inherits the Base class'''
    __tablename__ = 'customers'
 
    # table fields
    rec_number = Column(Integer, primary_key = True)
    name = Column(String)
    phone = Column(String)
 
    def __init__(self, rec_number, name, phone):
        self.rec_number = rec_number
        self.name = name
        self.phone = phone
 
    def __repr__(self):
        return "<Metadata('%d','%s','%s')>" % (self.rec_number,self.name,self.phone)

We've created a table called Customers with three fields (rec_number, name, phone). Because we'll be using as a database engine, sqlite, we don't need to specify the length for String type, but in other engines this can be mandatory (e.g. mysql, postgresql), so just put there String(50) or whatever length do you need.

This method is called declarative, and it should be used preferably from now on.

Next, we'll create a StartDB class, where we're gonna initialize the database engine and a session. Read the next code to find out how:

class StartDB():
    engine = None
    session = None
 
    # --------------------------------------------------------
    # define what database engine SQLAlchemy will use
    # we choose for now, sqlite3
    def __init__(self):
        self.engine = create_engine('sqlite:///customers.db', echo = False)
        Base.metadata.create_all(self.engine)
 
        # --------------------------------------------------------
        # create a session (handle to the database)
        # establish Session type, just once for all sessions (configured class)
        Session = sessionmaker(bind=self.engine)
        # create a session
        self.session = Session()
 
    def reset(self):
        # start over if you changed your mind, w/out deleting the db file
        Base.metadata.drop_all(self.engine)

You can modify echo = False to True and you'll have a very verbose screen ;).

You must also include in the file header:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

You can grab the full python sources from here.

Ok, let's try now to populate our table with some records. For that, we'll be using another file, called main.py where we'll put the rest of the code. In this tutorial page you're gonna see pieces of code, for the whole picture just download all the files and play with them.

# create one customer
onecust = Customers(784, "somename", "12334234")
# or several at once as list
customers = [
    Customers(1323, "Ronnie Ab PFA", "+4072312233"),
    Customers(1324, "Metalex SRL", "+40022312233"),
    Customers(1254, "Crystal SA", "+4073231977468") ]
 
# work with sessions, add customers to our table
session.add(onecust)
session.add_all(customers)

If you set echo=True in the common.py file, you're gonna see something like this:

2009-07-15 17:44:36,346 INFO sqlalchemy.engine.base.Engine.0x...88ec BEGIN
2009-07-15 17:44:36,349 INFO sqlalchemy.engine.base.Engine.0x...88ec INSERT INTO customers (rec_number, name, phone) VALUES (?, ?, ?)
2009-07-15 17:44:36,350 INFO sqlalchemy.engine.base.Engine.0x...88ec [784, 'somename', '12334234']
2009-07-15 17:44:36,440 INFO sqlalchemy.engine.base.Engine.0x...88ec INSERT INTO customers (rec_number, name, phone) VALUES (?, ?, ?)
2009-07-15 17:44:36,440 INFO sqlalchemy.engine.base.Engine.0x...88ec [1323, 'Ronnie Ab PFA', '+4072312233']
2009-07-15 17:44:36,441 INFO sqlalchemy.engine.base.Engine.0x...88ec INSERT INTO customers (rec_number, name, phone) VALUES (?, ?, ?)
2009-07-15 17:44:36,441 INFO sqlalchemy.engine.base.Engine.0x...88ec [1324, 'Metalex SRL', '+40022312233']
2009-07-15 17:44:36,442 INFO sqlalchemy.engine.base.Engine.0x...88ec INSERT INTO customers (rec_number, name, phone) VALUES (?, ?, ?)
2009-07-15 17:44:36,442 INFO sqlalchemy.engine.base.Engine.0x...88ec [1254, 'Crystal SA', '+4073231977468']
2009-07-15 17:44:36,443 INFO sqlalchemy.engine.base.Engine.0x...88ec COMMIT

Get all the customers

all_customers = session.query(Customers).order_by(Customers.name)
print "RecNo\tName\t\tPhone"
for customer in all_customers:
    print "%s\t%s\t\t%s" % (customer.rec_number,customer.name,customer.phone)

Get only desired fields

all_customers = session.query(Customers.name)
for customer in all_customers:
    print customer.name

Aliased and label

from sqlalchemy.orm import aliased
cust_alias = aliased(Customers, name="ca")
# all(), one(), first() immediately issue SQL and return a non-iterator value
for row in session.query(cust_alias, cust_alias.name.label("label_for_name_field")).all():
    print row.ca, row.label_for_name_field
# row.ca will return Customers.__repr__ result

Filters

Equals: query.filter(Customers.name == 'testname')
Not Equals: query.filter(Customers.name != 'testname')
IN: query.filter(Customers.name in_(['testname1', 'testname2']))
Is NULL: query.filter(Customers.name == None)
AND/OR: from sqlalchemy import and_, or_
query.filter(and_(Customers.name == None, Customer.phone == None))
query.filter(or_(Customers.name == None, Customer.phone == None))

for customer in session.query(Customers).filter(Customers.name.like("Ronnie%")):
    print customer.rec_number

You can define your own filter:

for customer in session.query(Customers).filter("rec_number < 1400").order_by("rec_number"):
    print customer.name

You can grab the full python sources from here.

Couldn't be much simpler. Go to Download section from SQLAlchemy Site, grab the archive, unpack, and in that folder run python setup.py install.