In [1]:

import sqlalchemy
sqlalchemy.__version__

Out[1]:

'1.0.11'

In [2]:

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

In [3]:

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

In [4]:

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 __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (self.name, self.fullname, self.password)

In [5]:

# Create a Schema

In [6]:

User.__table__

Out[6]:

Table('users', MetaData(bind=None), Column('id', Integer(), table=<users>, primary_key=True, nullable=False), Column('name', String(), table=<users>), Column('fullname', String(), table=<users>), Column('password', String(), table=<users>), schema=None)

In [7]:

Base.metadata

Out[7]:

MetaData(bind=None)

In [8]:

Base.metadata.create_all(engine)

2016-01-27 14:48:12,977 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1

INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1

2016-01-27 14:48:12,977 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2016-01-27 14:48:12,979 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1

INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1

2016-01-27 14:48:12,979 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2016-01-27 14:48:12,980 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")

INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("users")

2016-01-27 14:48:12,981 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2016-01-27 14:48:12,982 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE users (
	id INTEGER NOT NULL, 
	name VARCHAR, 
	fullname VARCHAR, 
	password VARCHAR, 
	PRIMARY KEY (id)
)

INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE users (
	id INTEGER NOT NULL, 
	name VARCHAR, 
	fullname VARCHAR, 
	password VARCHAR, 
	PRIMARY KEY (id)
)

2016-01-27 14:48:12,983 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2016-01-27 14:48:12,984 INFO sqlalchemy.engine.base.Engine COMMIT

INFO:sqlalchemy.engine.base.Engine:COMMIT

In [9]:

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

Out[9]:

'ed'

In [10]:

ed_user.password

Out[10]:

'edspassword'

In [11]:

str(ed_user.id)

Out[11]:

'None'

In [12]:

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

In [13]:

session = Session()

In [14]:

session.add(ed_user)

In [15]:

our_user = session.query(User).filter_by(name='ed').first()
our_user

2016-01-27 14:48:27,446 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)

2016-01-27 14:48:27,447 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2016-01-27 14:48:27,448 INFO sqlalchemy.engine.base.Engine ('ed', 'Ed Jones', 'edspassword')

INFO:sqlalchemy.engine.base.Engine:('ed', 'Ed Jones', 'edspassword')

2016-01-27 14:48:27,449 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?
 LIMIT ? OFFSET ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?
 LIMIT ? OFFSET ?

2016-01-27 14:48:27,450 INFO sqlalchemy.engine.base.Engine ('ed', 1, 0)

INFO:sqlalchemy.engine.base.Engine:('ed', 1, 0)

Out[15]:

<User(name='ed', fullname='Ed Jones', password='edspassword')>

In [16]:

user2 = User(name='Sara', fullname='Sara Jones', password='saraspassword')

In [17]:

session.add(user2)

In [18]:

session.add_all([
        User(name='wendy', fullname='Wendy Williams', password='foobar'),
        User(name='mary', fullname='Mary Contrary', password='xxg527'),
        User(name='fred', fullname='Fred Flinstone', password='blah')
    ])

In [19]:

ed_user.password = 'f8s7ccs'

In [22]:

ed_user is our_user

Out[22]:

True

In [23]:

our_user.password = 'test'

In [24]:

session.dirty

Out[24]:

IdentitySet([<User(name='ed', fullname='Ed Jones', password='test')>])

In [21]:

session.new

Out[21]:

IdentitySet([<User(name='mary', fullname='Mary Contrary', password='xxg527')>, <User(name='wendy', fullname='Wendy Williams', password='foobar')>, <User(name='fred', fullname='Fred Flinstone', password='blah')>, <User(name='Sara', fullname='Sara Jones', password='saraspassword')>])

In [22]:

session.commit()

2015-12-20 18:25:49,099 INFO sqlalchemy.engine.base.Engine UPDATE users SET password=? WHERE users.id = ?

INFO:sqlalchemy.engine.base.Engine:UPDATE users SET password=? WHERE users.id = ?

2015-12-20 18:25:49,100 INFO sqlalchemy.engine.base.Engine ('f8s7ccs', 1)

INFO:sqlalchemy.engine.base.Engine:('f8s7ccs', 1)

2015-12-20 18:25:49,102 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2015-12-20 18:25:49,102 INFO sqlalchemy.engine.base.Engine ('Sara', 'Sara Jones', 'saraspassword')

INFO:sqlalchemy.engine.base.Engine:('Sara', 'Sara Jones', 'saraspassword')

2015-12-20 18:25:49,104 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2015-12-20 18:25:49,104 INFO sqlalchemy.engine.base.Engine ('wendy', 'Wendy Williams', 'foobar')

INFO:sqlalchemy.engine.base.Engine:('wendy', 'Wendy Williams', 'foobar')

2015-12-20 18:25:49,105 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2015-12-20 18:25:49,106 INFO sqlalchemy.engine.base.Engine ('mary', 'Mary Contrary', 'xxg527')

INFO:sqlalchemy.engine.base.Engine:('mary', 'Mary Contrary', 'xxg527')

2015-12-20 18:25:49,107 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2015-12-20 18:25:49,108 INFO sqlalchemy.engine.base.Engine ('fred', 'Fred Flinstone', 'blah')

INFO:sqlalchemy.engine.base.Engine:('fred', 'Fred Flinstone', 'blah')

2015-12-20 18:25:49,110 INFO sqlalchemy.engine.base.Engine COMMIT

INFO:sqlalchemy.engine.base.Engine:COMMIT

In [23]:

ed_user.id

2015-12-20 18:25:49,297 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)

2015-12-20 18:25:49,299 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.id = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.id = ?

2015-12-20 18:25:49,300 INFO sqlalchemy.engine.base.Engine (1,)

INFO:sqlalchemy.engine.base.Engine:(1,)

Out[23]:

In [24]:

user2.id

2015-12-20 18:25:49,498 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.id = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.id = ?

2015-12-20 18:25:49,499 INFO sqlalchemy.engine.base.Engine (2,)

INFO:sqlalchemy.engine.base.Engine:(2,)

Out[24]:

In [25]:

ed_user.name = 'Edwardo'
fake_user = User(name = 'fakeuser', fullname='Invalid', password='password')
session.add(fake_user)

In [26]:

session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()

2015-12-20 18:25:49,875 INFO sqlalchemy.engine.base.Engine UPDATE users SET name=? WHERE users.id = ?

INFO:sqlalchemy.engine.base.Engine:UPDATE users SET name=? WHERE users.id = ?

2015-12-20 18:25:49,876 INFO sqlalchemy.engine.base.Engine ('Edwardo', 1)

INFO:sqlalchemy.engine.base.Engine:('Edwardo', 1)

2015-12-20 18:25:49,877 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2015-12-20 18:25:49,878 INFO sqlalchemy.engine.base.Engine ('fakeuser', 'Invalid', 'password')

INFO:sqlalchemy.engine.base.Engine:('fakeuser', 'Invalid', 'password')

2015-12-20 18:25:49,880 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name IN (?, ?)

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name IN (?, ?)

2015-12-20 18:25:49,881 INFO sqlalchemy.engine.base.Engine ('Edwardo', 'fakeuser')

INFO:sqlalchemy.engine.base.Engine:('Edwardo', 'fakeuser')

Out[26]:

[<User(name='Edwardo', fullname='Ed Jones', password='f8s7ccs')>,
 <User(name='fakeuser', fullname='Invalid', password='password')>]

In [27]:

session.dirty

Out[27]:

IdentitySet([])

In [28]:

session.rollback()
ed_user.name

2015-12-20 18:25:50,241 INFO sqlalchemy.engine.base.Engine ROLLBACK

INFO:sqlalchemy.engine.base.Engine:ROLLBACK

2015-12-20 18:25:50,243 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)

2015-12-20 18:25:50,245 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.id = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.id = ?

2015-12-20 18:25:50,246 INFO sqlalchemy.engine.base.Engine (1,)

INFO:sqlalchemy.engine.base.Engine:(1,)

Out[28]:

'ed'

In [29]:

session.query(User).filter(User.name.in_(['ed','fakeuser'])).all()

2015-12-20 18:25:50,443 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name IN (?, ?)

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name IN (?, ?)

2015-12-20 18:25:50,444 INFO sqlalchemy.engine.base.Engine ('ed', 'fakeuser')

INFO:sqlalchemy.engine.base.Engine:('ed', 'fakeuser')

Out[29]:

[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>]

In [30]:

#Querying

In [31]:

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

2015-12-20 18:25:50,811 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users ORDER BY users.id

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users ORDER BY users.id

2015-12-20 18:25:50,812 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

ed Ed Jones
Sara Sara Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flinstone

In [32]:

for name, fullname in session.query(User.name, User.fullname):
    print(name, fullname)

2015-12-20 18:25:50,994 INFO sqlalchemy.engine.base.Engine SELECT users.name AS users_name, users.fullname AS users_fullname 
FROM users

INFO:sqlalchemy.engine.base.Engine:SELECT users.name AS users_name, users.fullname AS users_fullname 
FROM users

2015-12-20 18:25:50,996 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

ed Ed Jones
Sara Sara Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flinstone

In [33]:

for row in session.query(User, User.name).all():
    print(row.User, row.User.id, row.name)

2015-12-20 18:25:51,171 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users

2015-12-20 18:25:51,172 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

<User(name='ed', fullname='Ed Jones', password='f8s7ccs')> 1 ed
<User(name='Sara', fullname='Sara Jones', password='saraspassword')> 2 Sara
<User(name='wendy', fullname='Wendy Williams', password='foobar')> 3 wendy
<User(name='mary', fullname='Mary Contrary', password='xxg527')> 4 mary
<User(name='fred', fullname='Fred Flinstone', password='blah')> 5 fred

In [34]:

for row in session.query(User.name.label('name_label')).all():
    print(row.name_label)

2015-12-20 18:25:51,346 INFO sqlalchemy.engine.base.Engine SELECT users.name AS name_label 
FROM users

INFO:sqlalchemy.engine.base.Engine:SELECT users.name AS name_label 
FROM users

2015-12-20 18:25:51,347 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

ed
Sara
wendy
mary
fred

In [35]:

from sqlalchemy.orm import aliased
user_alias = aliased(User, name='user_alias')
for row in session.query(user_alias, user_alias.name).all():
    print(row.user_alias, row.name)

2015-12-20 18:25:51,524 INFO sqlalchemy.engine.base.Engine SELECT user_alias.id AS user_alias_id, user_alias.name AS user_alias_name, user_alias.fullname AS user_alias_fullname, user_alias.password AS user_alias_password 
FROM users AS user_alias

INFO:sqlalchemy.engine.base.Engine:SELECT user_alias.id AS user_alias_id, user_alias.name AS user_alias_name, user_alias.fullname AS user_alias_fullname, user_alias.password AS user_alias_password 
FROM users AS user_alias

2015-12-20 18:25:51,526 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

<User(name='ed', fullname='Ed Jones', password='f8s7ccs')> ed
<User(name='Sara', fullname='Sara Jones', password='saraspassword')> Sara
<User(name='wendy', fullname='Wendy Williams', password='foobar')> wendy
<User(name='mary', fullname='Mary Contrary', password='xxg527')> mary
<User(name='fred', fullname='Fred Flinstone', password='blah')> fred

In [36]:

for u in session.query(User).order_by(User.id)[1:3]:
    print(u.id)

2015-12-20 18:25:51,707 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users ORDER BY users.id
 LIMIT ? OFFSET ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users ORDER BY users.id
 LIMIT ? OFFSET ?

2015-12-20 18:25:51,708 INFO sqlalchemy.engine.base.Engine (2, 1)

INFO:sqlalchemy.engine.base.Engine:(2, 1)

2
3

In [37]:

for name, in session.query(User.name).filter_by(fullname='Ed Jones'):
    print(name)

2015-12-20 18:25:51,898 INFO sqlalchemy.engine.base.Engine SELECT users.name AS users_name 
FROM users 
WHERE users.fullname = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.name AS users_name 
FROM users 
WHERE users.fullname = ?

2015-12-20 18:25:51,899 INFO sqlalchemy.engine.base.Engine ('Ed Jones',)

INFO:sqlalchemy.engine.base.Engine:('Ed Jones',)

ed

In [38]:

for user in session.query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones'):
    print(user)

2015-12-20 18:25:52,074 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ? AND users.fullname = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ? AND users.fullname = ?

2015-12-20 18:25:52,075 INFO sqlalchemy.engine.base.Engine ('ed', 'Ed Jones')

INFO:sqlalchemy.engine.base.Engine:('ed', 'Ed Jones')

<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>

In [39]:

# Common Filter Operators
#   ==
#   !=
#   .like(), .like('%ed%')
#   .in_([])  .in_(['ed', 'wendy','jack'])
#   Not in: query.filter(~User.name.in_(['ed','wendy','jack']))
#   is null: == None
#   is not null: != None
#   and_()
#   or_()
#   .match() *not available on SQLite

In [40]:

for user in session.query(User).filter(User.name.in_(
        session.query(User.name).filter(User.name.like('%ed%'))
    )):
    print(user)

2015-12-20 18:25:52,485 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name IN (SELECT users.name AS users_name 
FROM users 
WHERE users.name LIKE ?)

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name IN (SELECT users.name AS users_name 
FROM users 
WHERE users.name LIKE ?)

2015-12-20 18:25:52,486 INFO sqlalchemy.engine.base.Engine ('%ed%',)

INFO:sqlalchemy.engine.base.Engine:('%ed%',)

<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>
<User(name='fred', fullname='Fred Flinstone', password='blah')>

In [41]:

# Returning Lists and Scalars
#  all() returns list
#  first() reutrn first result as scalar
#  one() returns error unless result contains exactly one row
#  one_or_none()
#  scalar() invokes 1 and if successful returns first column

In [42]:

from sqlalchemy.orm.exc import MultipleResultsFound
try: 
    user = session.query(User).filter(User.name.like('%ed%')).one()
except MultipleResultsFound as e:
    print(e)

2015-12-20 18:25:52,876 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name LIKE ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name LIKE ?

2015-12-20 18:25:52,877 INFO sqlalchemy.engine.base.Engine ('%ed%',)

INFO:sqlalchemy.engine.base.Engine:('%ed%',)

Multiple rows were found for one()

In [43]:

query=session.query(User.id).filter(User.name == 'ed').order_by(User.id)
query.scalar()

2015-12-20 18:25:53,066 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id 
FROM users 
WHERE users.name = ? ORDER BY users.id

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id 
FROM users 
WHERE users.name = ? ORDER BY users.id

2015-12-20 18:25:53,067 INFO sqlalchemy.engine.base.Engine ('ed',)

INFO:sqlalchemy.engine.base.Engine:('ed',)

Out[43]:

In [44]:

# Using Textual SQL

In [45]:

from sqlalchemy import text
for user in session.query(User).filter(text("id<4")).order_by(text("id")).all():
    print(user.id, user.name)

2015-12-20 18:25:53,483 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE id<4 ORDER BY id

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE id<4 ORDER BY id

2015-12-20 18:25:53,485 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

1 ed
2 Sara
3 wendy

In [46]:

session.query(User).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(User.id).one()

2015-12-20 18:25:53,683 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE id<? and name=? ORDER BY users.id

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE id<? and name=? ORDER BY users.id

2015-12-20 18:25:53,684 INFO sqlalchemy.engine.base.Engine (224, 'fred')

INFO:sqlalchemy.engine.base.Engine:(224, 'fred')

Out[46]:

<User(name='fred', fullname='Fred Flinstone', password='blah')>

In [47]:

session.query(User).from_statement(
    text("SELECT * FROM users where name=:name")).params(name='ed').all()

2015-12-20 18:25:53,865 INFO sqlalchemy.engine.base.Engine SELECT * FROM users where name=?

INFO:sqlalchemy.engine.base.Engine:SELECT * FROM users where name=?

2015-12-20 18:25:53,866 INFO sqlalchemy.engine.base.Engine ('ed',)

INFO:sqlalchemy.engine.base.Engine:('ed',)

Out[47]:

[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>]

In [48]:

# Counting
session.query(User).filter(User.name.like('%ed')).count()

2015-12-20 18:25:54,069 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 
FROM (SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name LIKE ?) AS anon_1

INFO:sqlalchemy.engine.base.Engine:SELECT count(*) AS count_1 
FROM (SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name LIKE ?) AS anon_1

2015-12-20 18:25:54,070 INFO sqlalchemy.engine.base.Engine ('%ed',)

INFO:sqlalchemy.engine.base.Engine:('%ed',)

Out[48]:

In [49]:

from sqlalchemy import func
session.query(func.count(User.name), User.name).group_by(User.name).all()

2015-12-20 18:25:54,266 INFO sqlalchemy.engine.base.Engine SELECT count(users.name) AS count_1, users.name AS users_name 
FROM users GROUP BY users.name

INFO:sqlalchemy.engine.base.Engine:SELECT count(users.name) AS count_1, users.name AS users_name 
FROM users GROUP BY users.name

2015-12-20 18:25:54,267 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

Out[49]:

[(1, 'Sara'), (1, 'ed'), (1, 'fred'), (1, 'mary'), (1, 'wendy')]

In [50]:

session.query(func.count('*')).select_from(User).scalar()

2015-12-20 18:25:54,474 INFO sqlalchemy.engine.base.Engine SELECT count(?) AS count_1 
FROM users

INFO:sqlalchemy.engine.base.Engine:SELECT count(?) AS count_1 
FROM users

2015-12-20 18:25:54,475 INFO sqlalchemy.engine.base.Engine ('*',)

INFO:sqlalchemy.engine.base.Engine:('*',)

Out[50]:

In [51]:

# in terms of primary key:
session.query(func.count(User.id)).scalar()

2015-12-20 18:25:54,698 INFO sqlalchemy.engine.base.Engine SELECT count(users.id) AS count_1 
FROM users

INFO:sqlalchemy.engine.base.Engine:SELECT count(users.id) AS count_1 
FROM users

2015-12-20 18:25:54,699 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

Out[51]:

In [52]:

# Building a Relationship

In [53]:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Address(Base):
    __tablename__='addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    
    user = relationship("User", back_populates="addresses")
    
    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address
    
User.addresses = relationship("Address", order_by=Address.id, back_populates="user")

In [54]:

# create addresses table:
Base.metadata.create_all(engine)

2015-12-20 18:25:55,297 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("addresses")

INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("addresses")

2015-12-20 18:25:55,298 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2015-12-20 18:25:55,299 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")

INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("users")

2015-12-20 18:25:55,300 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2015-12-20 18:25:55,302 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE addresses (
	id INTEGER NOT NULL, 
	email_address VARCHAR NOT NULL, 
	user_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES users (id)
)

INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE addresses (
	id INTEGER NOT NULL, 
	email_address VARCHAR NOT NULL, 
	user_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES users (id)
)

2015-12-20 18:25:55,303 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2015-12-20 18:25:55,304 INFO sqlalchemy.engine.base.Engine COMMIT

INFO:sqlalchemy.engine.base.Engine:COMMIT

In [55]:

# Working with Related Objects

In [56]:

jack = User(name='jack', fullname='Jack Bean', password='gjffdd')
jack.addresses

Out[56]:

[]

In [57]:

ed_user.addresses

2015-12-20 18:25:55,931 INFO sqlalchemy.engine.base.Engine SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE ? = addresses.user_id ORDER BY addresses.id

INFO:sqlalchemy.engine.base.Engine:SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE ? = addresses.user_id ORDER BY addresses.id

2015-12-20 18:25:55,932 INFO sqlalchemy.engine.base.Engine (1,)

INFO:sqlalchemy.engine.base.Engine:(1,)

Out[57]:

[]

In [58]:

jack.addresses = [
    Address(email_address='jack@google.com'),
    Address(email_address='j25@yahoo.com')
]

In [59]:

jack.addresses[1]

Out[59]:

<Address(email_address='j25@yahoo.com')>

In [60]:

jack.addresses[1].user

Out[60]:

<User(name='jack', fullname='Jack Bean', password='gjffdd')>

In [61]:

# commit to database:
session.add(jack)
session.commit()

2015-12-20 18:25:56,738 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)

2015-12-20 18:25:56,739 INFO sqlalchemy.engine.base.Engine ('jack', 'Jack Bean', 'gjffdd')

INFO:sqlalchemy.engine.base.Engine:('jack', 'Jack Bean', 'gjffdd')

2015-12-20 18:25:56,741 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (email_address, user_id) VALUES (?, ?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO addresses (email_address, user_id) VALUES (?, ?)

2015-12-20 18:25:56,742 INFO sqlalchemy.engine.base.Engine ('jack@google.com', 6)

INFO:sqlalchemy.engine.base.Engine:('jack@google.com', 6)

2015-12-20 18:25:56,744 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (email_address, user_id) VALUES (?, ?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO addresses (email_address, user_id) VALUES (?, ?)

2015-12-20 18:25:56,745 INFO sqlalchemy.engine.base.Engine ('j25@yahoo.com', 6)

INFO:sqlalchemy.engine.base.Engine:('j25@yahoo.com', 6)

2015-12-20 18:25:56,747 INFO sqlalchemy.engine.base.Engine COMMIT

INFO:sqlalchemy.engine.base.Engine:COMMIT

In [62]:

jack=session.query(User).filter_by(name='jack').one()

2015-12-20 18:25:56,931 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)

2015-12-20 18:25:56,933 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?

2015-12-20 18:25:56,934 INFO sqlalchemy.engine.base.Engine ('jack',)

INFO:sqlalchemy.engine.base.Engine:('jack',)

In [63]:

jack

Out[63]:

<User(name='jack', fullname='Jack Bean', password='gjffdd')>

In [64]:

jack.addresses

2015-12-20 18:25:57,322 INFO sqlalchemy.engine.base.Engine SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE ? = addresses.user_id ORDER BY addresses.id

INFO:sqlalchemy.engine.base.Engine:SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE ? = addresses.user_id ORDER BY addresses.id

2015-12-20 18:25:57,323 INFO sqlalchemy.engine.base.Engine (6,)

INFO:sqlalchemy.engine.base.Engine:(6,)

Out[64]:

[<Address(email_address='jack@google.com')>,
 <Address(email_address='j25@yahoo.com')>]

In [65]:

# Querying with Joins

In [66]:

for u, a in session.query(User, Address).\
        filter(User.id==Address.user_id).filter(Address.email_address=='jack@google.com').all():
        print(u)
        print(a)

2015-12-20 18:25:57,732 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM users, addresses 
WHERE users.id = addresses.user_id AND addresses.email_address = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM users, addresses 
WHERE users.id = addresses.user_id AND addresses.email_address = ?

2015-12-20 18:25:57,733 INFO sqlalchemy.engine.base.Engine ('jack@google.com',)

INFO:sqlalchemy.engine.base.Engine:('jack@google.com',)

<User(name='jack', fullname='Jack Bean', password='gjffdd')>
<Address(email_address='jack@google.com')>

In [67]:

session.query(User).join(Address).filter(Address.email_address=='jack@google.com').all()

2015-12-20 18:25:57,939 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users JOIN addresses ON users.id = addresses.user_id 
WHERE addresses.email_address = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users JOIN addresses ON users.id = addresses.user_id 
WHERE addresses.email_address = ?

2015-12-20 18:25:57,940 INFO sqlalchemy.engine.base.Engine ('jack@google.com',)

INFO:sqlalchemy.engine.base.Engine:('jack@google.com',)

Out[67]:

[<User(name='jack', fullname='Jack Bean', password='gjffdd')>]

In [68]:

# ways to specify a join:
# query.join(ADdress, User.id==Address.user_id)     # explicit condition
# query.join(User.addresses)                        # specify relationship from left to right
# query.join(Address, User.addresses)               # same, with explicit target
# query.join('addresses')                           # same, using a string

# query.outerjoin(User.addresses)                   # LEFT OUTER JOIN

In [69]:

# Using Aliases

In [70]:

# if the same table is to be referenced twice, it must be aliased with another name. 

In [71]:

from sqlalchemy.orm import aliased
adalias1= aliased(Address)
adalias2= aliased(Address)
for username, email1, email2 in session.query(User.name, adalias1.email_address, adalias2.email_address).\
    join(adalias1, User.addresses).join(adalias2, User.addresses).\
    filter(adalias1.email_address=='jack@google.com').filter(adalias2.email_address=='j25@yahoo.com'):
        print(username, email1, email2)

2015-12-20 18:25:58,838 INFO sqlalchemy.engine.base.Engine SELECT users.name AS users_name, addresses_1.email_address AS addresses_1_email_address, addresses_2.email_address AS addresses_2_email_address 
FROM users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id JOIN addresses AS addresses_2 ON users.id = addresses_2.user_id 
WHERE addresses_1.email_address = ? AND addresses_2.email_address = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.name AS users_name, addresses_1.email_address AS addresses_1_email_address, addresses_2.email_address AS addresses_2_email_address 
FROM users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id JOIN addresses AS addresses_2 ON users.id = addresses_2.user_id 
WHERE addresses_1.email_address = ? AND addresses_2.email_address = ?

2015-12-20 18:25:58,839 INFO sqlalchemy.engine.base.Engine ('jack@google.com', 'j25@yahoo.com')

INFO:sqlalchemy.engine.base.Engine:('jack@google.com', 'j25@yahoo.com')

jack jack@google.com j25@yahoo.com

In [72]:

# Using Subqueries

In [73]:

from sqlalchemy.sql import func
stmt = session.query(Address.user_id, func.count('*').label('address_count')).group_by(Address.user_id).subquery()

In [74]:

for u, count in session.query(User, stmt.c.address_count).outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id):
    print(u,count)

2015-12-20 18:25:59,603 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, anon_1.address_count AS anon_1_address_count 
FROM users LEFT OUTER JOIN (SELECT addresses.user_id AS user_id, count(?) AS address_count 
FROM addresses GROUP BY addresses.user_id) AS anon_1 ON users.id = anon_1.user_id ORDER BY users.id

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, anon_1.address_count AS anon_1_address_count 
FROM users LEFT OUTER JOIN (SELECT addresses.user_id AS user_id, count(?) AS address_count 
FROM addresses GROUP BY addresses.user_id) AS anon_1 ON users.id = anon_1.user_id ORDER BY users.id

2015-12-20 18:25:59,604 INFO sqlalchemy.engine.base.Engine ('*',)

INFO:sqlalchemy.engine.base.Engine:('*',)

<User(name='ed', fullname='Ed Jones', password='f8s7ccs')> None
<User(name='Sara', fullname='Sara Jones', password='saraspassword')> None
<User(name='wendy', fullname='Wendy Williams', password='foobar')> None
<User(name='mary', fullname='Mary Contrary', password='xxg527')> None
<User(name='fred', fullname='Fred Flinstone', password='blah')> None
<User(name='jack', fullname='Jack Bean', password='gjffdd')> 2

In [75]:

# Selecting Entities from Subqueries

In [76]:

stmt= session.query(Address).filter(Address.email_address != 'j25@yahoo.com').subquery()
adalias= aliased(Address,stmt)
for user, address in session.query(User, adalias).join(adalias, User.addresses):
    print(user)
    print(address)

2015-12-20 18:26:00,197 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, anon_1.id AS anon_1_id, anon_1.email_address AS anon_1_email_address, anon_1.user_id AS anon_1_user_id 
FROM users JOIN (SELECT addresses.id AS id, addresses.email_address AS email_address, addresses.user_id AS user_id 
FROM addresses 
WHERE addresses.email_address != ?) AS anon_1 ON users.id = anon_1.user_id

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, anon_1.id AS anon_1_id, anon_1.email_address AS anon_1_email_address, anon_1.user_id AS anon_1_user_id 
FROM users JOIN (SELECT addresses.id AS id, addresses.email_address AS email_address, addresses.user_id AS user_id 
FROM addresses 
WHERE addresses.email_address != ?) AS anon_1 ON users.id = anon_1.user_id

2015-12-20 18:26:00,198 INFO sqlalchemy.engine.base.Engine ('j25@yahoo.com',)

INFO:sqlalchemy.engine.base.Engine:('j25@yahoo.com',)

<User(name='jack', fullname='Jack Bean', password='gjffdd')>
<Address(email_address='jack@google.com')>

In [77]:

# Using Exists

In [78]:

from sqlalchemy.sql import exists
stmt = exists().where(Address.user_id ==User.id)
for name, in session.query(User.name).filter(stmt):
    print(name)

2015-12-20 18:26:00,973 INFO sqlalchemy.engine.base.Engine SELECT users.name AS users_name 
FROM users 
WHERE EXISTS (SELECT * 
FROM addresses 
WHERE addresses.user_id = users.id)

INFO:sqlalchemy.engine.base.Engine:SELECT users.name AS users_name 
FROM users 
WHERE EXISTS (SELECT * 
FROM addresses 
WHERE addresses.user_id = users.id)

2015-12-20 18:26:00,974 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

jack

In [79]:

for name, in session.query(User.name).filter(User.addresses.any()):
    print(name)

2015-12-20 18:26:01,469 INFO sqlalchemy.engine.base.Engine SELECT users.name AS users_name 
FROM users 
WHERE EXISTS (SELECT 1 
FROM addresses 
WHERE users.id = addresses.user_id)

INFO:sqlalchemy.engine.base.Engine:SELECT users.name AS users_name 
FROM users 
WHERE EXISTS (SELECT 1 
FROM addresses 
WHERE users.id = addresses.user_id)

2015-12-20 18:26:01,470 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

jack

In [80]:

session.query(Address).filter(~Address.user.has(User.name=='jack')).all()

2015-12-20 18:26:01,915 INFO sqlalchemy.engine.base.Engine SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE NOT (EXISTS (SELECT 1 
FROM users 
WHERE users.id = addresses.user_id AND users.name = ?))

INFO:sqlalchemy.engine.base.Engine:SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE NOT (EXISTS (SELECT 1 
FROM users 
WHERE users.id = addresses.user_id AND users.name = ?))

2015-12-20 18:26:01,916 INFO sqlalchemy.engine.base.Engine ('jack',)

INFO:sqlalchemy.engine.base.Engine:('jack',)

Out[80]:

[]

In [81]:

# Relationship Operators
# ==   !=  ==None   .contains()   .any()   .has()   .with_parent()

In [82]:

# Eager Loading
# Subquery Load

In [83]:

from sqlalchemy.orm import subqueryload
jack = session.query(User).options(subqueryload(User.addresses)).filter_by(name='jack').one()
jack

2015-12-20 18:26:03,115 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?

2015-12-20 18:26:03,116 INFO sqlalchemy.engine.base.Engine ('jack',)

INFO:sqlalchemy.engine.base.Engine:('jack',)

2015-12-20 18:26:03,121 INFO sqlalchemy.engine.base.Engine SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id, anon_1.users_id AS anon_1_users_id 
FROM (SELECT users.id AS users_id 
FROM users 
WHERE users.name = ?) AS anon_1 JOIN addresses ON anon_1.users_id = addresses.user_id ORDER BY anon_1.users_id, addresses.id

INFO:sqlalchemy.engine.base.Engine:SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id, anon_1.users_id AS anon_1_users_id 
FROM (SELECT users.id AS users_id 
FROM users 
WHERE users.name = ?) AS anon_1 JOIN addresses ON anon_1.users_id = addresses.user_id ORDER BY anon_1.users_id, addresses.id

2015-12-20 18:26:03,122 INFO sqlalchemy.engine.base.Engine ('jack',)

INFO:sqlalchemy.engine.base.Engine:('jack',)

Out[83]:

<User(name='jack', fullname='Jack Bean', password='gjffdd')>

In [84]:

jack.addresses

Out[84]:

[<Address(email_address='jack@google.com')>,
 <Address(email_address='j25@yahoo.com')>]

In [85]:

# Joined Load

In [86]:

from sqlalchemy.orm import joinedload
jack = session.query(User).options(joinedload(User.addresses)).filter_by(name='jack').one()
jack

2015-12-20 18:26:04,455 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id 
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id 
WHERE users.name = ? ORDER BY addresses_1.id

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id 
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id 
WHERE users.name = ? ORDER BY addresses_1.id

2015-12-20 18:26:04,457 INFO sqlalchemy.engine.base.Engine ('jack',)

INFO:sqlalchemy.engine.base.Engine:('jack',)

Out[86]:

<User(name='jack', fullname='Jack Bean', password='gjffdd')>

In [87]:

# Explicit join + Eagerload

In [88]:

from sqlalchemy.orm import contains_eager
jacks_addresses=session.query(Address).join(Address.user).filter(User.name=='jack').\
        options(contains_eager(Address.user)).all()
jacks_addresses

2015-12-20 18:26:05,349 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses JOIN users ON users.id = addresses.user_id 
WHERE users.name = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses JOIN users ON users.id = addresses.user_id 
WHERE users.name = ?

2015-12-20 18:26:05,351 INFO sqlalchemy.engine.base.Engine ('jack',)

INFO:sqlalchemy.engine.base.Engine:('jack',)

Out[88]:

[<Address(email_address='jack@google.com')>,
 <Address(email_address='j25@yahoo.com')>]

In [89]:

jacks_addresses[0].user

Out[89]:

<User(name='jack', fullname='Jack Bean', password='gjffdd')>

In [90]:

#Deleting

In [91]:

session.delete(jack)
session.query(User).filter_by(name='jack').count()

2015-12-20 18:26:06,680 INFO sqlalchemy.engine.base.Engine UPDATE addresses SET user_id=? WHERE addresses.id = ?

INFO:sqlalchemy.engine.base.Engine:UPDATE addresses SET user_id=? WHERE addresses.id = ?

2015-12-20 18:26:06,681 INFO sqlalchemy.engine.base.Engine ((None, 1), (None, 2))

INFO:sqlalchemy.engine.base.Engine:((None, 1), (None, 2))

2015-12-20 18:26:06,683 INFO sqlalchemy.engine.base.Engine DELETE FROM users WHERE users.id = ?

INFO:sqlalchemy.engine.base.Engine:DELETE FROM users WHERE users.id = ?

2015-12-20 18:26:06,684 INFO sqlalchemy.engine.base.Engine (6,)

INFO:sqlalchemy.engine.base.Engine:(6,)

2015-12-20 18:26:06,687 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 
FROM (SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?) AS anon_1

INFO:sqlalchemy.engine.base.Engine:SELECT count(*) AS count_1 
FROM (SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?) AS anon_1

2015-12-20 18:26:06,687 INFO sqlalchemy.engine.base.Engine ('jack',)

INFO:sqlalchemy.engine.base.Engine:('jack',)

Out[91]:

In [92]:

session.query(Address).filter(Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])).count()

2015-12-20 18:26:07,088 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 
FROM (SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE addresses.email_address IN (?, ?)) AS anon_1

INFO:sqlalchemy.engine.base.Engine:SELECT count(*) AS count_1 
FROM (SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE addresses.email_address IN (?, ?)) AS anon_1

2015-12-20 18:26:07,089 INFO sqlalchemy.engine.base.Engine ('jack@google.com', 'j25@yahoo.com')

INFO:sqlalchemy.engine.base.Engine:('jack@google.com', 'j25@yahoo.com')

Out[92]:

In [93]:

session.close()

2015-12-20 18:26:07,561 INFO sqlalchemy.engine.base.Engine ROLLBACK

INFO:sqlalchemy.engine.base.Engine:ROLLBACK

In [94]:

Base=declarative_base()

In [95]:

class User(Base):
    __tablename__='users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)
    
    addresses = relationship("Address", back_populates='user', cascade="all, delete, delete-orphan")
    
    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
            self.name, self.fullname, self.password)

In [96]:

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship("User", back_populates="addresses")
    
    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address

In [98]:

jack = session.query(User).get(6)
del jack.addresses[1]
session.query(Address).filter(
    Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])).count()

2015-12-20 18:26:41,779 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.id = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.id = ?

2015-12-20 18:26:41,780 INFO sqlalchemy.engine.base.Engine (6,)

INFO:sqlalchemy.engine.base.Engine:(6,)

2015-12-20 18:26:41,783 INFO sqlalchemy.engine.base.Engine SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE ? = addresses.user_id

INFO:sqlalchemy.engine.base.Engine:SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE ? = addresses.user_id

2015-12-20 18:26:41,784 INFO sqlalchemy.engine.base.Engine (6,)

INFO:sqlalchemy.engine.base.Engine:(6,)

2015-12-20 18:26:41,790 INFO sqlalchemy.engine.base.Engine DELETE FROM addresses WHERE addresses.id = ?

INFO:sqlalchemy.engine.base.Engine:DELETE FROM addresses WHERE addresses.id = ?

2015-12-20 18:26:41,791 INFO sqlalchemy.engine.base.Engine (2,)

INFO:sqlalchemy.engine.base.Engine:(2,)

2015-12-20 18:26:41,794 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 
FROM (SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE addresses.email_address IN (?, ?)) AS anon_1

INFO:sqlalchemy.engine.base.Engine:SELECT count(*) AS count_1 
FROM (SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE addresses.email_address IN (?, ?)) AS anon_1

2015-12-20 18:26:41,795 INFO sqlalchemy.engine.base.Engine ('jack@google.com', 'j25@yahoo.com')

INFO:sqlalchemy.engine.base.Engine:('jack@google.com', 'j25@yahoo.com')

Out[98]:

In [99]:

session.delete(jack)

In [100]:

session.query(User).filter_by(name='jack').count()

2015-12-20 18:27:20,381 INFO sqlalchemy.engine.base.Engine DELETE FROM addresses WHERE addresses.id = ?

INFO:sqlalchemy.engine.base.Engine:DELETE FROM addresses WHERE addresses.id = ?

2015-12-20 18:27:20,383 INFO sqlalchemy.engine.base.Engine (1,)

INFO:sqlalchemy.engine.base.Engine:(1,)

2015-12-20 18:27:20,385 INFO sqlalchemy.engine.base.Engine DELETE FROM users WHERE users.id = ?

INFO:sqlalchemy.engine.base.Engine:DELETE FROM users WHERE users.id = ?

2015-12-20 18:27:20,386 INFO sqlalchemy.engine.base.Engine (6,)

INFO:sqlalchemy.engine.base.Engine:(6,)

2015-12-20 18:27:20,388 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 
FROM (SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?) AS anon_1

INFO:sqlalchemy.engine.base.Engine:SELECT count(*) AS count_1 
FROM (SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?) AS anon_1

2015-12-20 18:27:20,389 INFO sqlalchemy.engine.base.Engine ('jack',)

INFO:sqlalchemy.engine.base.Engine:('jack',)

Out[100]:

In [101]:

session.query(Address).filter(Address.email_address.in_(['jack@google.com','j25@yahoo.com'])).count()

2015-12-20 18:29:28,163 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 
FROM (SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE addresses.email_address IN (?, ?)) AS anon_1

INFO:sqlalchemy.engine.base.Engine:SELECT count(*) AS count_1 
FROM (SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE addresses.email_address IN (?, ?)) AS anon_1

2015-12-20 18:29:28,165 INFO sqlalchemy.engine.base.Engine ('jack@google.com', 'j25@yahoo.com')

INFO:sqlalchemy.engine.base.Engine:('jack@google.com', 'j25@yahoo.com')

Out[101]:

In [102]:

# Building a Many to Many Relationship

In [103]:

from sqlalchemy import Table, Text
# association table
post_keywords = Table('post_keywords', Base.metadata, 
                      Column('post_id', ForeignKey('posts.id'), primary_key=True), 
                      Column('keyword_id', ForeignKey('keywords.id'), primary_key=True))

In [104]:

class BlogPost(Base):
    __tablename__='posts'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    headline = Column(String(255), nullable=False)
    body = Column(Text)
    
    # many to many BlogPost<->Keyword
    keywords = relationship('Keyword', secondary = post_keywords, back_populates='posts')
    
    def __init__(self, headline, body, author):
        self.author = author
        self.headline = headline
        self.body = body
        
    def __repr__(self):
        return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)
    

In [105]:

class Keyword(Base):
    __tablename__='keywords'
    
    id = Column(Integer, primary_key=True)
    keyword=Column(String(50), nullable=False, unique=True)
    posts = relationship('BlogPost',
                        secondary=post_keywords,
                        back_populates='keywords')
    
    def __init__(self, keyword):
        self.keyword = keyword

In [107]:

BlogPost.author= relationship(User, back_populates="posts")
User.posts=relationship(BlogPost, back_populates="author", lazy="dynamic")

In [108]:

Base.metadata.create_all(engine)

2015-12-20 18:47:11,395 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("keywords")

INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("keywords")

2015-12-20 18:47:11,396 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2015-12-20 18:47:11,398 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("addresses")

INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("addresses")

2015-12-20 18:47:11,399 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2015-12-20 18:47:11,400 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")

INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("users")

2015-12-20 18:47:11,401 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2015-12-20 18:47:11,402 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("posts")

INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("posts")

2015-12-20 18:47:11,403 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2015-12-20 18:47:11,404 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("post_keywords")

INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("post_keywords")

2015-12-20 18:47:11,405 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2015-12-20 18:47:11,407 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE keywords (
	id INTEGER NOT NULL, 
	keyword VARCHAR(50) NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (keyword)
)

INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE keywords (
	id INTEGER NOT NULL, 
	keyword VARCHAR(50) NOT NULL, 
	PRIMARY KEY (id), 
	UNIQUE (keyword)
)

2015-12-20 18:47:11,407 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2015-12-20 18:47:11,409 INFO sqlalchemy.engine.base.Engine COMMIT

INFO:sqlalchemy.engine.base.Engine:COMMIT

2015-12-20 18:47:11,410 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE posts (
	id INTEGER NOT NULL, 
	user_id INTEGER, 
	headline VARCHAR(255) NOT NULL, 
	body TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES users (id)
)

INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE posts (
	id INTEGER NOT NULL, 
	user_id INTEGER, 
	headline VARCHAR(255) NOT NULL, 
	body TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES users (id)
)

2015-12-20 18:47:11,411 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2015-12-20 18:47:11,412 INFO sqlalchemy.engine.base.Engine COMMIT

INFO:sqlalchemy.engine.base.Engine:COMMIT

2015-12-20 18:47:11,414 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE post_keywords (
	post_id INTEGER NOT NULL, 
	keyword_id INTEGER NOT NULL, 
	PRIMARY KEY (post_id, keyword_id), 
	FOREIGN KEY(post_id) REFERENCES posts (id), 
	FOREIGN KEY(keyword_id) REFERENCES keywords (id)
)

INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE post_keywords (
	post_id INTEGER NOT NULL, 
	keyword_id INTEGER NOT NULL, 
	PRIMARY KEY (post_id, keyword_id), 
	FOREIGN KEY(post_id) REFERENCES posts (id), 
	FOREIGN KEY(keyword_id) REFERENCES keywords (id)
)

2015-12-20 18:47:11,415 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2015-12-20 18:47:11,416 INFO sqlalchemy.engine.base.Engine COMMIT

INFO:sqlalchemy.engine.base.Engine:COMMIT

In [109]:

wendy=session.query(User).filter_by(name='wendy').one()
post=BlogPost("Wendy's Blog Post", "This is a test", wendy)
session.add(post)

2015-12-20 18:48:02,424 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?

INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users 
WHERE users.name = ?

2015-12-20 18:48:02,425 INFO sqlalchemy.engine.base.Engine ('wendy',)

INFO:sqlalchemy.engine.base.Engine:('wendy',)

In [110]:

post.keywords.append(Keyword('wendy'))
post.keywords.append(Keyword('firstpost'))

In [111]:

session.query(BlogPost).filter(BlogPost.keywords.any(keyword='firstpost')).all()

2015-12-20 18:49:25,029 INFO sqlalchemy.engine.base.Engine INSERT INTO keywords (keyword) VALUES (?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO keywords (keyword) VALUES (?)

2015-12-20 18:49:25,030 INFO sqlalchemy.engine.base.Engine ('wendy',)

INFO:sqlalchemy.engine.base.Engine:('wendy',)

2015-12-20 18:49:25,031 INFO sqlalchemy.engine.base.Engine INSERT INTO keywords (keyword) VALUES (?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO keywords (keyword) VALUES (?)

2015-12-20 18:49:25,032 INFO sqlalchemy.engine.base.Engine ('firstpost',)

INFO:sqlalchemy.engine.base.Engine:('firstpost',)

2015-12-20 18:49:25,034 INFO sqlalchemy.engine.base.Engine INSERT INTO posts (user_id, headline, body) VALUES (?, ?, ?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO posts (user_id, headline, body) VALUES (?, ?, ?)

2015-12-20 18:49:25,035 INFO sqlalchemy.engine.base.Engine (3, "Wendy's Blog Post", 'This is a test')

INFO:sqlalchemy.engine.base.Engine:(3, "Wendy's Blog Post", 'This is a test')

2015-12-20 18:49:25,037 INFO sqlalchemy.engine.base.Engine INSERT INTO post_keywords (post_id, keyword_id) VALUES (?, ?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO post_keywords (post_id, keyword_id) VALUES (?, ?)

2015-12-20 18:49:25,037 INFO sqlalchemy.engine.base.Engine ((1, 2), (1, 1))

INFO:sqlalchemy.engine.base.Engine:((1, 2), (1, 1))

2015-12-20 18:49:25,041 INFO sqlalchemy.engine.base.Engine SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body 
FROM posts 
WHERE EXISTS (SELECT 1 
FROM post_keywords, keywords 
WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?)

INFO:sqlalchemy.engine.base.Engine:SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body 
FROM posts 
WHERE EXISTS (SELECT 1 
FROM post_keywords, keywords 
WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?)

2015-12-20 18:49:25,042 INFO sqlalchemy.engine.base.Engine ('firstpost',)

INFO:sqlalchemy.engine.base.Engine:('firstpost',)

Out[111]:

[BlogPost("Wendy's Blog Post", 'This is a test', <User(name='wendy', fullname='Wendy Williams', password='foobar')>)]

In [112]:

session.query(BlogPost).filter(BlogPost.author==wendy).filter(BlogPost.keywords.any(keyword='firstpost')).all()

2015-12-20 18:53:06,715 INFO sqlalchemy.engine.base.Engine SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body 
FROM posts 
WHERE ? = posts.user_id AND (EXISTS (SELECT 1 
FROM post_keywords, keywords 
WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?))

INFO:sqlalchemy.engine.base.Engine:SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body 
FROM posts 
WHERE ? = posts.user_id AND (EXISTS (SELECT 1 
FROM post_keywords, keywords 
WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?))

2015-12-20 18:53:06,716 INFO sqlalchemy.engine.base.Engine (3, 'firstpost')

INFO:sqlalchemy.engine.base.Engine:(3, 'firstpost')

Out[112]:

[BlogPost("Wendy's Blog Post", 'This is a test', <User(name='wendy', fullname='Wendy Williams', password='foobar')>)]

In [113]:

wendy.posts.filter(BlogPost.keywords.any(keyword='firstpost')).all()

2015-12-20 18:53:40,289 INFO sqlalchemy.engine.base.Engine SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body 
FROM posts 
WHERE ? = posts.user_id AND (EXISTS (SELECT 1 
FROM post_keywords, keywords 
WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?))

INFO:sqlalchemy.engine.base.Engine:SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body 
FROM posts 
WHERE ? = posts.user_id AND (EXISTS (SELECT 1 
FROM post_keywords, keywords 
WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?))

2015-12-20 18:53:40,291 INFO sqlalchemy.engine.base.Engine (3, 'firstpost')

INFO:sqlalchemy.engine.base.Engine:(3, 'firstpost')

Out[113]:

[BlogPost("Wendy's Blog Post", 'This is a test', <User(name='wendy', fullname='Wendy Williams', password='foobar')>)]

In [ ]: