SQL Alchemy ORM Examples

So, these are the same as the CORE expression language, but using the ORM toolkit

Create an in memory SQLite database engine



In [2]:

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

Create some tables using ORM declarative



In [3]:

    
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

declarative_base is a factory that makes a class on which to define ORM classes. We use it to create our models like this:



In [4]:

    
from sqlalchemy import Column, Integer, String, MetaData, ForeignKey

class User(Base):
    __tablename__ = 'user'
    
    id_user = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    
    def __repr__(self):
        return "<User(id_user={}, name={}, age={})".format(self.id_user, self.name, self.age)

from sqlalchemy.orm import relationship, backref
    
class Item(Base):
    __tablename__ = 'item'
    
    id_item = Column(Integer, primary_key=True)
    id_user = Column(Integer, ForeignKey('user.id_user'))
    thing = Column(String)
    
    user = relationship("User", backref=backref('items', order_by=id_item))
    
    def __repr__(self):
        return "<Item(id_item={}, id_user={}, thing={})".format(self.id_item, self.id_user, self.thing)



In [5]:

    
User.__table__









    Out[5]:





Table('user', MetaData(bind=None), Column('id_user', Integer(), table=<user>, primary_key=True, nullable=False), Column('name', String(), table=<user>), Column('age', Integer(), table=<user>), schema=None)

Now create the tables in the `engine`

This is the equivalent of metadata.create_all(engine).



In [6]:

    
Base.metadata.create_all(engine)

Create a User instance - this is just in Python memory - not in the DB!



In [7]:

    
u = User(id_user=1, name="Billy", age=40)
print(u)









    



<User(id_user=1, name=Billy, age=40)

Sessions

And now for something different. We need to talk about sessions.



In [8]:

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

Let's add a some users



In [9]:

    
people = [
    (1, 'Bob', '20'),
    (2, 'Sally', '25'),
    (3, 'John', '30')]
for (id_user, name, age) in people:
    u = User(id_user=id_user, name=name, age=age)
    session.add(u)

Let's query for a user



In [10]:

    
u1 = session.query(User).get(1)
print(u1)









    



<User(id_user=1, name=Bob, age=20)

So this is similar to:

user_tuple = connection.execute(select([user]).where(user.c.id_user == 1)).fetchone()

And we can also count the users in the table:



In [11]:

    
count = session.query(User).count()
print(count)

Let's add the `item`s to the database too



In [12]:

    
items = (
    (1, 1, 'Peanuts'),
    (2, 1, 'VW'),
    (3, 1, 'iPad'),
    (4, 2, 'Raisins'),
    (5, 2, 'Fiat'),
    (6, 2, 'Nexus 10'),
    (7, 2, 'Timex'),
    (8, 3, 'Caviar'),
    (9, 3, 'Porche'),
    (10, 3, 'Surface Pro'),
    (11, 3, 'Rolex'),
    (12, 3, 'Boat'),
    (13, 3, 'Plane'))
for (id_item, id_user, thing) in items:
    i = Item(id_item=id_item, id_user=id_user, thing=thing)
    session.add(i)



In [13]:

    
print(session.query(Item).count())

Inspecting the data

As we're in the domain model now, we need to look at things like objects. Let's look at the John User() item and see what's there:



In [14]:

    
john = session.query(User).get(1)
print(john)









    



<User(id_user=1, name=Bob, age=20)



In [15]:

    
for i in john.items:
    print(i)









    



<Item(id_item=1, id_user=1, thing=Peanuts)
<Item(id_item=2, id_user=1, thing=VW)
<Item(id_item=3, id_user=1, thing=iPad)



In [16]:

    
item1 = john.items[0]
print(item1)
print(item1.user)









    



<Item(id_item=1, id_user=1, thing=Peanuts)
<User(id_user=1, name=Bob, age=20)

Let's list out all of the users and items:



In [17]:

    
for (u, i) in session.query(User, Item).filter(User.id_user == Item.id_user).all():
    print(u, i)









    



<User(id_user=1, name=Bob, age=20) <Item(id_item=1, id_user=1, thing=Peanuts)
<User(id_user=1, name=Bob, age=20) <Item(id_item=2, id_user=1, thing=VW)
<User(id_user=1, name=Bob, age=20) <Item(id_item=3, id_user=1, thing=iPad)
<User(id_user=2, name=Sally, age=25) <Item(id_item=4, id_user=2, thing=Raisins)
<User(id_user=2, name=Sally, age=25) <Item(id_item=5, id_user=2, thing=Fiat)
<User(id_user=2, name=Sally, age=25) <Item(id_item=6, id_user=2, thing=Nexus 10)
<User(id_user=2, name=Sally, age=25) <Item(id_item=7, id_user=2, thing=Timex)
<User(id_user=3, name=John, age=30) <Item(id_item=8, id_user=3, thing=Caviar)
<User(id_user=3, name=John, age=30) <Item(id_item=9, id_user=3, thing=Porche)
<User(id_user=3, name=John, age=30) <Item(id_item=10, id_user=3, thing=Surface Pro)
<User(id_user=3, name=John, age=30) <Item(id_item=11, id_user=3, thing=Rolex)
<User(id_user=3, name=John, age=30) <Item(id_item=12, id_user=3, thing=Boat)
<User(id_user=3, name=John, age=30) <Item(id_item=13, id_user=3, thing=Plane)

Let's find the user who has a Timex:



In [18]:

    
u = session.query(User).join(Item).filter(Item.thing.ilike('timex')).one()
print(u)









    



<User(id_user=2, name=Sally, age=25)

How about func.count() and friends?



In [19]:

    
from sqlalchemy import func



In [20]:

    
results = session.query(User, func.count(Item.id_item)).join(Item).group_by(Item.id_user).all()
for r in results:
    print(r)









    



(<User(id_user=1, name=Bob, age=20), 3)
(<User(id_user=2, name=Sally, age=25), 4)
(<User(id_user=3, name=John, age=30), 6)