Initializing

First we import SQLAlchemy create_engine function, which tells us what dialect and connection parameters to use
Next we create an engine to an in memory SQLite database



In [23]:

    
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')

Building Tables

It’s not like a normal class definition, but a more list a list of table columns. The metadata is used as a catalog to provide a mapping between the actual tables and our pythonic representation of them.

First we import the components we need from SQLAlchemy
Next we define our actors table
Finally we define our roles table

This doesn't do us much good since there is not association between the engine we established above, and the tables objects we created.



In [24]:

    
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
actors = Table('actors', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('fullname', String),
    Column('body_count', Integer)
)
roles = Table('roles', metadata,
    Column('id', Integer, primary_key=True),
    Column('actor_id', None, ForeignKey('actors.id')),
    Column('character_name', String, nullable=False)
)

Assocating MetaData to the Engine

We take the MetaData object we created early, and bind it to the engine as see below. This will create our tables in the actually database. This is conditional by default, will not attempt to recreate tables already present in the target database. NOTE: It doesn't check to make sure they are the same just that an existing table is already present.



In [25]:

    
metadata.create_all(engine)

Table Objects

We can look at the tables attributes, and use this to reference parts of them later. In this example, we're looking at the columns of the table. This can also be abbreviated as just actors.c.items()



In [26]:

    
actors.columns.items()









    Out[26]:





[('id',
  Column('id', Integer(), table=<actors>, primary_key=True, nullable=False)),
 ('name', Column('name', String(), table=<actors>)),
 ('fullname', Column('fullname', String(), table=<actors>)),
 ('body_count', Column('body_count', Integer(), table=<actors>))]

Opening a Connection

We're going to open a connection to that engine so we can make queries etc.



In [27]:

    
conn = engine.connect()

Single Insert

First we're going to build an insert statement for putting a single record in our actors table
Then, we're excute that statement and store the result
Next, we'll get the primary key of that record back



In [28]:

    
ins = actors.insert().values(name='Graham', fullname='Graham Chapman', body_count=3)
result = conn.execute(ins)



In [29]:

    
result.inserted_primary_key









    Out[29]:





[1]

Looking at the output

Below, we're going to print the SQL statement as build via the dialect we choose above, and then print the params from that statement



In [30]:

    
print str(ins)
ins.compile().params









    



INSERT INTO actors (name, fullname, body_count) VALUES (:name, :fullname, :body_count)






    Out[30]:





{'body_count': 3, 'fullname': 'Graham Chapman', 'name': 'Graham'}

Insert some more test data



In [31]:

    
ins = actors.insert().values(name='John', fullname='John Cleese', body_count=2)
result = conn.execute(ins)
ins = actors.insert().values(name='Terry', fullname='Terry Gilliam', body_count=6)
result = conn.execute(ins)

Multiple (Bulk) Insert

This let's us bulk insert data into our table. Transactions can be manually started, but by default wrap each execute.



In [32]:

    
results = conn.execute(roles.insert(), [ 
    {'actor_id': 1, 'character_name' : 'King Arthur'},
    {'actor_id': 1, 'character_name' : 'Voice of God'},
    {'actor_id': 2, 'character_name' : 'Sir Lancelot'},
    {'actor_id': 2, 'character_name' : 'Black Knight'},
    {'actor_id': 3, 'character_name' : 'Patsy'},
    {'actor_id': 3, 'character_name' : 'Sir Bors'},
])



In [33]:

    
results.rowcount









    Out[33]:





6

Updating Records

When looking at updating data we often want to do it to just a particular record, or a certain group of records. The use of the .where() clause let's us do just that. Then we supply a list of values to update.



In [34]:

    
stmt = actors.update().where(actors.c.name == 'Graham').values(name='Gram')

result = conn.execute(stmt)



In [35]:

    
result.rowcount









    Out[35]:





1

Deleting Records

Same concept as update above, except it removes the record.



In [36]:

    
result = conn.execute(actors.delete().where(actors.c.name == 'Terry'))
result.rowcount









    Out[36]:





1

Selecting Records

So unlike the other statements that were clauses off of the table object, this time we're going to use a new select stmt and operate on the table that way. You can use a clause off the table object; however, this is more commonly used as you'll normally be joining data and doing other things.



In [37]:

    
from sqlalchemy.sql import select
s = select([actors.c.name, actors.c.fullname])
result = conn.execute(s)
for row in result:
    print row









    



(u'Gram', u'Graham Chapman')
(u'John', u'John Cleese')

Ordering

Here we're sorting by the name in descending alphabetical order. .asc() will do the accending order



In [45]:

    
stmt = select([actors.c.name]).order_by(actors.c.name.desc())
conn.execute(stmt).fetchall()









    Out[45]:





[(u'John',), (u'Gram',)]

Limiting

Here we are limiting the query to return only one record and offset it by 1. So it should be the second record in the table. Notice the use of the .first() clause on the statement execution. This gives us back the first result instead of the list we had been dealing with previously.



In [46]:

    
stmt = select([actors.c.name, actors.c.fullname]).limit(1).offset(1)
conn.execute(stmt).first()









    Out[46]:





(u'John', u'John Cleese')

Counting

First we import func
Next we build a select statement that counts the actors in our table
Finally, we execute the statement, and since this returns only a number I use the scalar() method to return to get back just the number. It actually returns the first column of the first row.



In [51]:

    
from sqlalchemy.sql import func
stmt = select([func.count(actors)])
conn.execute(stmt).scalar()









    Out[51]:





2

Summing

Here we are building a select statement that counts the actors in our table, and sums their body counts



In [55]:

    
stmt = select([func.count(actors), func.sum(actors.c.body_count)])
conn.execute(stmt).first()









    Out[55]:





(2, 5)

Joins

Now let's join the actors and the roles tables on the ID of the actor.



In [56]:

    
s = select([actors, roles]).where(actors.c.id == roles.c.actor_id)
for row in conn.execute(s):
    print row









    



(1, u'Gram', u'Graham Chapman', 3, 1, 1, u'King Arthur')
(1, u'Gram', u'Graham Chapman', 3, 2, 1, u'Voice of God')
(2, u'John', u'John Cleese', 2, 3, 2, u'Sir Lancelot')
(2, u'John', u'John Cleese', 2, 4, 2, u'Black Knight')

Grouping

We can also use the .join() clause on the table object to join to tables, and then group them to get a count of roles by actor.



In [57]:

    
stmt = select([actors.c.name, func.count(roles.c.id)]).\
    select_from(actors.join(roles)).\
    group_by(actors.c.name)
conn.execute(stmt).fetchall()









    Out[57]:





[(u'Gram', 2), (u'John', 2)]

Filtering

First, let's pull in some additional parts of the sqlalchemy.sql module. NOTE: we're only using and_() in this example, but they all work the same way and can be nested.
Next we're going to use the .where() clause and nest an and_() cause inside of it.



In [22]:

    
from sqlalchemy.sql import and_, or_, not_
stmt = select([actors.c.name, roles.c.character_name]).\
    where(
        and_(
          actors.c.name.like('Gra%'),
          roles.c.character_name.like('Vo%'),
          actors.c.id == roles.c.actor_id
        )
    )
conn.execute(stmt).fetchall()









    Out[22]:





[(u'Graham', u'Voice of God')]



In [53]: