SQLAlchemy

Links

http://www.sqlalchemy.org/
Elixir is a declarative layer on top of the SQLAlchemy library.
Object Relational Tutorial
SqlSoup provides a convenient way to access database tables without having to declare table or mapper classes ahead of time.

Install

easy_install SQLAlchemy

Verify

>>> import sqlalchemy
>>> sqlalchemy.__version__
0.4.0

Connection

In Memory (sqlite)

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)

Create Table

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

Base.metadata.create_all(engine)

Note: create_all will check for the presence of a table first before creating, so it’s safe to call multiple times.

We want to save an object, so lets create an instance of our class:

>>> ed_user = User('ed', 'Ed Jones', 'edspassword')
>>> ed_user.name
'ed'
>>> ed_user.password
'edspassword'
>>> str(ed_user.id)
'None'

Session

>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)

Note: Just create a single Session object for your application.

Whenever you need to have a conversation with the database, you instantiate a Session:

session = Session()

Saving Objects

session.add(ed_user)
session.commit()

If we look at Ed’s id attribute, which earlier was None, it now has a value:

>>> ed_user.id
1

Query

for instance in session.query(User).order_by(User.id):
    print instance.name

jack = session.query(User).get(5)