SQLAlchemy tutorial¶

Inserting¶

Let’s create a new file called sqlexpression_test.py and add the following to it:

from metadata_test import engine, page_table

print "\nInserting\n"

connection = engine.connect()
ins = page_table.insert(
    values=dict(name=u'test', title=u'Test Page', content=u'Some content!')
)
print ins
result = connection.execute(ins)
print result

connection.close()

The ins object automatically generates the correct SQL to insert the values specified. It is to be noted that SQLAlchemy handles any type conversion of the values specified to insert() using its type system, thus removing any chance of SQL injection attacks.

This simple example shows how to insert and select data through the SQL Expression API.

Selecting¶

Edit sqlexpression_test.py and add the following before the connection.close() statement:

print "\nSelecting\n"

from sqlalchemy.sql import select

s = select([page_table])
result = connection.execute(s)
for row in result:
    print row

The above code should be self-explained.

To add WHERE clauses, you will pass a SQLAlchemy expression as the second argument to the select call. To examplify this, edit sqlexpression_test.py and replace the above code with:

print "\nSelecting\n"

from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_

s = select([page_table], and_(page_table.c.id<=10, page_table.c.name.like(u't%')))
s = s.order_by(page_table.c.title.desc(), page_table.c.id)
result = connection.execute(s)
print result.fetchall()

Examine the output to understand the SQLAlchemy expression used here.

Updating¶

Update statements look like this:

print "\nUpdating Results\n"

from sqlalchemy import update

u = update(page_table, page_table.c.title==u'New Title')
connection.execute(u, title=u"Updated Title")

You can add the above to sqlexpression_test.py.

Deleting¶

Delete statements look like this:

print "\nDeleting Row\n"

from sqlalchemy import delete

d = delete(page_table, page_table.c.id==1)
connection.execute(d)

Again, you can add the above code block to the sqlexpression_test.py file.

Mapping¶

Now that you have defined the table structures, you need to define classes and mappers to work with the Object-Relational API. Add the following to the model.py file:

class Page(object):
    pass

class Comment(object):
    pass

class Tag(object):
    pass

orm.mapper(Page, page_table, properties={
    'comments':orm.relation(Comment, backref='page'),
    'tags':orm.relation(Tag, secondary=pagetag_table)
})
orm.mapper(Comment, comment_table)
orm.mapper(Tag, tag_table)

With the above, the Page class is mapped to the page_table object. Likewise, the Comment and Tag classes are mapped to the comment_table and tag_table objects, respectively.

The first mapper statement also tell SQLAlchemy that a Page object should have extra properties called comments and tags, which should return all the Comment and Tag objects related to that page. The mapper for Comment doesn’t need the page property specified because the mapper for Page has already specified it via backref. The mapper for Tag doesn’t need to have the relation to Page specified because SQLAlchemy can already work it out via the secondary argument.

SQLAlchemy’s Object-Relational API includes lots of features. Look at the SQLAlchemy documentation to know about them.

Create the Session¶

SQLAlchemy manages the mapped objects in a so-called session.

Create a file called object_test.py and add the following content:

import model
from sqlalchemy import orm
from sqlalchemy import create_engine

# Create an engine and create all the tables we need
engine = create_engine('sqlite:///:memory:', echo=True)
model.metadata.bind = engine
model.metadata.create_all()

# Set up the session
sm = orm.sessionmaker(bind=engine, autoflush=True, autocommit=False,
    expire_on_commit=True)
session = orm.scoped_session(sm)

The sessionmaker function returns an object for building the particular session you want. To understand the options passed to sessionmaker you need to know some terminology:

• flushing is the process of updating the database with the objects you have been working with,
• committing is the process of sending a COMMIT statement to the database to make those flushes permanent.

Let’s now look at the arguments being passed to sessionmaker:

• bind=engine: this binds the session to the engine, the session will automatically create the connections it needs.
• autoflush=True: if you commit your changes to the database before they have been flushed, this option tells SQLAlchemy to flush them before the commit is gone.
• autocommit=False: this tells SQLAlchemy to wrap all changes between commits in a transaction. If autocommit=True is specified, SQLAlchemy automatically commits any changes after each flush; this is undesired in most cases.
• expire_on_commit=True: this means that all instances attached to the session will be fully expired after each commit so that all attribute/object access subsequent to a completed transaction will load from the most recent database state.

The scoped_session() object ensures that a different session is used for each thread so that every request can have its own access to the database.

Use the Session¶

In this section you are going to insert, delete, update and query database objects using the session created in the previous section.

>>> from object_test import session

>>> import model

>>> test_page = model.Page()
>>> test_page.title = u'Test Page'
>>> test_page.content = u'Test content'
>>> test_page.title
u'Test Page'

>>> session.add(test_page)
>>> print test_page.id
None

>>> session.flush()
>>> print test_page.id
1

>>> session.commit()

>>> session.delete(test_page)
>>> session.flush()

>>> session.rollback()

>>> page_q = session.query(model.Page)
>>> for page in page_q:
...     print page.title
Test Page

>>> page_q.all()

>>> page = page_q.first()
>>> page.title

>>> page_q[2:5]

>>> page_q.get(1)

>>> comment1 = model.Comment()
>>> comment1.name= u'James'
>>> comment1.email = u'james@example.com'
>>> comment1.content = u'This page needs a bit more detail ;-)'
>>> comment2 = model.Comment()
>>> comment2.name = u'Mike'
>>> comment2.email = u'mike@example.com'
>>> page.comments.append(comment1)
>>> page.comments.append(comment2)
>>> session.commit()