SQL Alchemy Notes

This is some notes when I was playing with SQL Alchemy in Python.

SQL Alchemy is an “Object Relational Mapper” (ORM) model for mapping user defined Python classes with database tables.

To setup the connection, there are a few steps.

(1) Set up the local database

(2) Create the Python classes corresponding to the tables

(3) Create a session to interact with the database

(4) Interact with the database through ORM

For each of these steps, there are a few steps

(1) Set up the local database

>>> import sqlalchemy
>>> sqlalchemy.__version__ 

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

>>> Base = declarative_base()

(2) Create the Python classes corresponding to the tables 

>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = 'users'
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s'')>" % (
...                             self.name, self.fullname)

>>> Base.metadata.create_all(engine) 

This creates a “User” table with columns “name” and “fullname”. The “__repr__” method is similar to “toString()” method for an object. It helps to display the information of the object, but it is optional.

The create_all command would generate the following output

PRAGMA table_info("users")
    fullname VARCHAR,
    password VARCHAR,
    PRIMARY KEY (id)

This shows the table has actually been created in the local database, and is commited.


(3) Create a session to interact with the database

Interactions are conducted through a Session object. To start, you need to make a session object that is connected to the current database. There are two ways to do it, either you can create the database first and then set up the session (Approach1). Or you can set up the Session first, then configure it to connect to a specific engine(Approach2).

Approach 1

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

Approach 2

>>> Session = sessionmaker()
>>> Session.configure(bind=engine)  # once engine is available

(4) Interact with the database through ORM

To interact with the local database, you create local objects, and use session.add() to add the object to the specific table. Here is an example of creating the user class object and add it to the local database.

>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
>>> session.add(ed_user)

NOTE: at this point, the change is NOT yet committed to the database. It is still stored in the object side of things.

At this point, you can do session.new and the following output should show

>> session.new # doctest: +SKIP

IdentitySet([<User(name=‘ed’, fullname=‘Ed Jones’)>])

This means this change is still not yet committed. To commit this to the SQL database, do

>>> session.commit()

This would flush the changes to the database. FLUSH would also happen when you do a query,

>>> our_user = session.query(User).filter_by(name='ed').first() # doctest:+NORMALIZE_WHITESPACE

This would first insert the entry, and then perform a “select”.

Details of this tutorial can be found in this post


This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s