def test_executing(self):
# re-create a new INSERT object
self.ins = self.users.insert()
# execute the insert statement
res = self.conn.execute(self.ins, uid=1, name='jack', fullname='Jack Jones')
assert(res.inserted_primary_key == [1])
res = self.conn.execute(self.ins, uid=2, name='wendy', fullname='Wendy Williams')
assert(res.inserted_primary_key == [2])
# the res variable is a ResultProxy object, analagous to DBAPI cursor
# issue many inserts, the same is possible for update and delete
self.conn.execute(self.addresses.insert(), [
{'id': 1, 'user_id': 1, 'email_address': '*****@*****.**'},
{'id': 2, 'user_id': 1, 'email_address': '*****@*****.**'},
{'id': 3, 'user_id': 2, 'email_address': '*****@*****.**'},
{'id': 4, 'user_id': 2, 'email_address': '*****@*****.**'}
])
# test selects on the inserted values
from sqlalchemy.sql import select
s = select([self.users])
res = self.conn.execute(s)
u1 = res.fetchone()
u2 = res.fetchone()
# accessing rows
assert(u1['name'] == u'jack')
assert(u1['fullname'] == u'Jack Jones')
assert(u2['name'] == u'wendy')
assert(u2['fullname'] == u'Wendy Williams')
assert(u1[1] == u1['name'])
assert(u1[2] == u1['fullname'])
assert(u2[1] == u2['name'])
assert(u2[2] == u2['fullname'])
# be sure to close the result set
res.close()
# use cols to access rows
res = self.conn.execute(s)
u3 = res.fetchone()
u4 = res.fetchone()
assert(u3[self.users.c.name] == u1['name'])
assert(u3[self.users.c.fullname] == u1['fullname'])
assert(u4[self.users.c.name] == u2['name'])
assert(u4[self.users.c.fullname] == u2['fullname'])
# reference individual columns in select clause
s = select([self.users.c.name, self.users.c.fullname])
res = self.conn.execute(s)
u3 = res.fetchone()
u4 = res.fetchone()
assert(u3[self.users.c.name] == u1['name'])
assert(u3[self.users.c.fullname] == u1['fullname'])
assert(u4[self.users.c.name] == u2['name'])
assert(u4[self.users.c.fullname] == u2['fullname'])
# test joins
# cartesian product
usrs = [row for row in self.conn.execute(select([self.users]))]
addrs = [row for row in self.conn.execute(select([self.addresses]))]
prod = [row for row in self.conn.execute(select([self.users, self.addresses]))]
assert(len(prod) == len(usrs) * len(addrs))
# inner join on id
s = select([self.users, self.addresses]).where(self.users.c.uid == self.addresses.c.user_id)
inner = [row for row in self.conn.execute(s)]
assert(len(inner) == 4)
# operators between columns objects & other col objects/literals
expr = self.users.c.uid == self.addresses.c.user_id
assert('my_users.uid = addresses.user_id' == str(expr))
# see how Teradata concats two strings
assert(str((self.users.c.name + self.users.c.fullname).compile(bind=self.engine)) ==
'my_users.name || my_users.fullname')
# built-in conjunctions
from sqlalchemy.sql import and_, or_
s = select([(self.users.c.fullname +
", " +
self.addresses.c.email_address).label('titles')]).where(
and_(
self.users.c.uid == self.addresses.c.user_id,
self.users.c.name.between('m', 'z'),
or_(
self.addresses.c.email_address.like('*****@*****.**'),
self.addresses.c.email_address.like('*****@*****.**')
)
)
)
# print(s)
res = self.conn.execute(s)
for row in res:
assert(str(row[0]) == u'Wendy Williams, [email protected]')
# more joins
# ON condition auto generated based on ForeignKey
assert(str(self.users.join(self.addresses)) ==
'my_users JOIN addresses ON my_users.uid = addresses.user_id')
# specify the join ON condition
self.users.join(self.addresses,
self.addresses.c.email_address.like(self.users.c.name + '%'))
# select from clause to specify tables and the ON condition
s = select([self.users.c.fullname]).select_from(
self.users.join(self.addresses, self.addresses.c.email_address.like(self.users.c.name + '%')))
res = self.conn.execute(s)
assert(len(res.fetchall()) == 3)
# left outer joins
s = select([self.users.c.fullname]).select_from(self.users.outerjoin(self.addresses))
# outer join works with teradata dialect (unlike oracle dialect < version9)
assert(str(s) == str(s.compile(dialect=self.dialect)))
# test bind params (positional)
from sqlalchemy import text
s = self.users.select(self.users.c.name.like(
bindparam('username', type_=String)+text("'%'")))
res = self.conn.execute(s, username='******').fetchall()
assert(len(res), 1)
# functions
from sqlalchemy.sql import func, column
# certain function names are known by sqlalchemy
assert(str(func.current_timestamp()), 'CURRENT_TIMESTAMP')
# functions can be used in the select
res = self.conn.execute(select(
[func.max(self.addresses.c.email_address, type_=String).label(
'max_email')])).scalar()
assert(res, '*****@*****.**')
# func result sets, define a function taking params x,y return q,z,r
# useful for nested queries, subqueries - w/ dynamic params
calculate = select([column('q'), column('z'), column('r')]).\
select_from(
func.calculate(
bindparam('x'),
bindparam('y')
)
)
calc = calculate.alias()
s = select([self.users]).where(self.users.c.uid > calc.c.z)
assert('SELECT my_users.uid, my_users.name, my_users.fullname\
FROM my_users, (SELECT q, z, r\
FROM calculate(:x, :y)) AS anon_1\
WHERE my_users.uid > anon_1.z', s)
# instantiate the func
calc1 = calculate.alias('c1').unique_params(x=17, y=45)
calc2 = calculate.alias('c2').unique_params(x=5, y=12)
s = select([self.users]).where(self.users.c.uid.between(calc1.c.z, calc2.c.z))
parms = s.compile().params
assert('x_2' in parms, 'x_1' in parms)
assert('y_2' in parms, 'y_1' in parms)
assert(parms['x_1'] == 17, parms['y_1'] == 45)
assert(parms['x_2'] == 5, parms['y_2'] == 12)
# order by asc
stmt = select([self.users.c.name]).order_by(self.users.c.name)
res = self.conn.execute(stmt).fetchall()
assert('jack' == res[0][0])
assert('wendy' == res[1][0])
# order by desc
stmt = select([self.users.c.name]).order_by(self.users.c.name.desc())
res = self.conn.execute(stmt).fetchall()
assert('wendy' == res[0][0])
assert('jack' == res[1][0])
# group by
stmt = select([self.users.c.name, func.count(self.addresses.c.id)]).\
select_from(self.users.join(self.addresses)).\
group_by(self.users.c.name)
res = self.conn.execute(stmt).fetchall()
assert(res[0][0] == 'jack')
assert(res[1][0] == 'wendy')
assert(res[0][1] == res[1][1])
# group by having
stmt = select([self.users.c.name, func.count(self.addresses.c.id)]).\
select_from(self.users.join(self.addresses)).\
group_by(self.users.c.name).\
having(func.length(self.users.c.name) > 4)
res = self.conn.execute(stmt).fetchall()
assert(res[0] == ('wendy', 2))
# distinct
stmt = select([self.users.c.name]).\
where(self.addresses.c.email_address.contains(self.users.c.name)).distinct()
res = self.conn.execute(stmt).fetchall()
assert(len(res) == 2)
assert(res[0][0] != res[1][0])
# limit
stmt = select([self.users.c.name, self.addresses.c.email_address]).\
select_from(self.users.join(self.addresses)).\
limit(1)
res = self.conn.execute(stmt).fetchall()
assert(len(res) == 1)
# offset
# test union and except
from sqlalchemy.sql import except_, union
u = union(
self.addresses.select().where(self.addresses.c.email_address == '*****@*****.**'),
self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**')),)# .order_by(self.addresses.c.email_address)
# print(u)
# #res = self.conn.execute(u) this fails, syntax error order by expects pos integer?
u = except_(
self.addresses.select().where(self.addresses.c.email_address.like('%@%.com')),
self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**')))
res = self.conn.execute(u).fetchall()
assert(1, len(res))
u = except_(
union(
self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**')),
self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**'))
).alias().select(), self.addresses.select(self.addresses.c.email_address.like('*****@*****.**'))
)
res = self.conn.execute(u).fetchall()
assert(1, len(res))
# scalar subqueries
stmt = select([func.count(self.addresses.c.id)]).where(self.users.c.uid == self.addresses.c.user_id).as_scalar()
# we can place stmt as any other column within another select
res = self.conn.execute(select([self.users.c.name, stmt])).fetchall()
# res is a list of tuples, one tuple per user's name
assert(2, len(res))
u1 = res[0]
u2 = res[1]
assert(len(u1) == len(u2))
assert(u1[0] == u'jack')
assert(u1[1] == u2[1])
assert(u2[0] == u'wendy')
# we can label the inner query
stmt = select([func.count(self.addresses.c.id)]).\
where(self.users.c.uid == self.addresses.c.user_id).\
label("address_count")
res = self.conn.execute(select([self.users.c.name, stmt])).fetchall()
assert(2, len(res))
u1 = res[0]
u2 = res[1]
assert(len(u1) == 2)
assert(len(u2) == 2)
# inserts, updates, deletes
stmt = self.users.update().values(fullname="Fullname: " + self.users.c.name)
res = self.conn.execute(stmt)
assert('name_1' in res.last_updated_params())
assert(res.last_updated_params()['name_1'] == 'Fullname: ')
stmt = self.users.insert().values(name=bindparam('_name') + " .. name")
res = self.conn.execute(stmt, [{'uid': 4, '_name': 'name1'}, {'uid': 5, '_name': 'name2'}, {'uid': 6, '_name': 'name3'}, ])
# updates
stmt = self.users.update().where(self.users.c.name == 'jack').values(name='ed')
res = self.conn.execute(stmt)
assert(res.rowcount == 1)
assert(res.returns_rows is False)
# update many with bound params
stmt = self.users.update().where(self.users.c.name == bindparam('oldname')).\
values(name=bindparam('newname'))
res = self.conn.execute(stmt, [
{'oldname': 'jack', 'newname': 'ed'},
{'oldname': 'wendy', 'newname': 'mary'},
])
assert(res.returns_rows is False)
assert(res.rowcount == 1)
res = self.conn.execute(select([self.users]).where(self.users.c.name == 'ed'))
r = res.fetchone()
assert(r['name'] == 'ed')
# correlated updates
stmt = select([self.addresses.c.email_address]).\
where(self.addresses.c.user_id == self.users.c.uid).\
limit(1)
# this fails, syntax error bc of LIMIT - need TOP/SAMPLE instead
# Note: TOP can't be in a subquery
# res = self.conn.execute(self.users.update().values(fullname=stmt))
# multiple table updates
stmt = self.users.update().\
values(name='ed wood').\
where(self.users.c.uid == self.addresses.c.id).\
where(self.addresses.c.email_address.startswith('ed%'))
# this fails, teradata does update from set where not update set from where
# #res = self.conn.execute(stmt)
stmt = self.users.update().\
values({
self.users.c.name: 'ed wood',
self.addresses.c.email_address: '*****@*****.**'
}).\
where(self.users.c.uid == self.addresses.c.id).\
where(self.addresses.c.email_address.startswith('ed%'))
# fails but works on MySQL, should this work for us?
# #res = self.conn.execute(stmt)
# deletes
self.conn.execute(self.addresses.delete())
self.conn.execute(self.users.delete().where(self.users.c.name > 'm'))
# matched row counts
# updates + deletes have a number indicating # rows matched by WHERE clause
res = self.conn.execute(self.users.delete())
assert(res.rowcount == 1)
def test_executing(self):
# re-create a new INSERT object
self.ins = self.users.insert()
# execute the insert statement
res = self.conn.execute(self.ins, uid=1, name='jack', fullname='Jack Jones')
assert(res.inserted_primary_key == [1])
res = self.conn.execute(self.ins, uid=2, name='wendy', fullname='Wendy Williams')
assert(res.inserted_primary_key == [2])
# the res variable is a ResultProxy object, analagous to DBAPI cursor
# issue many inserts, the same is possible for update and delete
self.conn.execute(self.addresses.insert(), [
{'id': 1, 'user_id': 1, 'email_address': '*****@*****.**'},
{'id': 2, 'user_id': 1, 'email_address': '*****@*****.**'},
{'id': 3, 'user_id': 2, 'email_address': '*****@*****.**'},
{'id': 4, 'user_id': 2, 'email_address': '*****@*****.**'}
])
# test selects on the inserted values
from sqlalchemy.sql import select
s = select([self.users])
res = self.conn.execute(s)
u1 = res.fetchone()
u2 = res.fetchone()
# accessing rows
assert(u1['name'] == u'jack')
assert(u1['fullname'] == u'Jack Jones')
assert(u2['name'] == u'wendy')
assert(u2['fullname'] == u'Wendy Williams')
assert(u1[1] == u1['name'])
assert(u1[2] == u1['fullname'])
assert(u2[1] == u2['name'])
assert(u2[2] == u2['fullname'])
# be sure to close the result set
res.close()
# use cols to access rows
res = self.conn.execute(s)
u3 = res.fetchone()
u4 = res.fetchone()
assert(u3[self.users.c.name] == u1['name'])
assert(u3[self.users.c.fullname] == u1['fullname'])
assert(u4[self.users.c.name] == u2['name'])
assert(u4[self.users.c.fullname] == u2['fullname'])
# reference individual columns in select clause
s = select([self.users.c.name, self.users.c.fullname])
res = self.conn.execute(s)
u3 = res.fetchone()
u4 = res.fetchone()
assert(u3[self.users.c.name] == u1['name'])
assert(u3[self.users.c.fullname] == u1['fullname'])
assert(u4[self.users.c.name] == u2['name'])
assert(u4[self.users.c.fullname] == u2['fullname'])
# test joins
# cartesian product
usrs = [row for row in self.conn.execute(select([self.users]))]
addrs = [row for row in self.conn.execute(select([self.addresses]))]
prod = [row for row in self.conn.execute(select([self.users, self.addresses]))]
assert(len(prod) == len(usrs) * len(addrs))
# inner join on id
s = select([self.users, self.addresses]).where(self.users.c.uid == self.addresses.c.user_id)
inner = [row for row in self.conn.execute(s)]
assert(len(inner) == 4)
# operators between columns objects & other col objects/literals
expr = self.users.c.uid == self.addresses.c.user_id
assert('my_users.uid = addresses.user_id' == str(expr))
# see how Teradata concats two strings
assert(str((self.users.c.name + self.users.c.fullname).compile(bind=self.engine)) ==
'my_users.name || my_users.fullname')
# built-in conjunctions
from sqlalchemy.sql import and_, or_
s = select([(self.users.c.fullname +
", " +
self.addresses.c.email_address).label('titles')]).where(
and_(
self.users.c.uid == self.addresses.c.user_id,
self.users.c.name.between('m', 'z'),
or_(
self.addresses.c.email_address.like('*****@*****.**'),
self.addresses.c.email_address.like('*****@*****.**')
)
)
)
# print(s)
res = self.conn.execute(s)
for row in res:
assert(str(row[0]) == u'Wendy Williams, [email protected]')
# more joins
# ON condition auto generated based on ForeignKey
assert(str(self.users.join(self.addresses)) ==
'my_users JOIN addresses ON my_users.uid = addresses.user_id')
# specify the join ON condition
self.users.join(self.addresses,
self.addresses.c.email_address.like(self.users.c.name + '%'))
# select from clause to specify tables and the ON condition
s = select([self.users.c.fullname]).select_from(
self.users.join(self.addresses, self.addresses.c.email_address.like(self.users.c.name + '%')))
res = self.conn.execute(s)
assert(len(res.fetchall()) == 3)
# left outer joins
s = select([self.users.c.fullname]).select_from(self.users.outerjoin(self.addresses))
# outer join works with teradata dialect (unlike oracle dialect < version9)
assert(str(s) == str(s.compile(dialect=self.dialect)))
# test bind params (positional)
from sqlalchemy import text
s = self.users.select(self.users.c.name.like(
bindparam('username', type_=String)+text("'%'")))
res = self.conn.execute(s, username='******').fetchall()
assert(len(res), 1)
# functions
from sqlalchemy.sql import func, column
# certain function names are known by sqlalchemy
assert(str(func.current_timestamp()), 'CURRENT_TIMESTAMP')
# functions can be used in the select
res = self.conn.execute(select(
[func.max(self.addresses.c.email_address, type_=String).label(
'max_email')])).scalar()
assert(res, '*****@*****.**')
# func result sets, define a function taking params x,y return q,z,r
# useful for nested queries, subqueries - w/ dynamic params
calculate = select([column('q'), column('z'), column('r')]).\
select_from(
func.calculate(
bindparam('x'),
bindparam('y')
)
)
calc = calculate.alias()
s = select([self.users]).where(self.users.c.uid > calc.c.z)
assert('SELECT my_users.uid, my_users.name, my_users.fullname\
FROM my_users, (SELECT q, z, r\
FROM calculate(:x, :y)) AS anon_1\
WHERE my_users.uid > anon_1.z', s)
# instantiate the func
calc1 = calculate.alias('c1').unique_params(x=17, y=45)
calc2 = calculate.alias('c2').unique_params(x=5, y=12)
s = select([self.users]).where(self.users.c.uid.between(calc1.c.z, calc2.c.z))
parms = s.compile().params
assert('x_2' in parms, 'x_1' in parms)
assert('y_2' in parms, 'y_1' in parms)
assert(parms['x_1'] == 17, parms['y_1'] == 45)
assert(parms['x_2'] == 5, parms['y_2'] == 12)
# order by asc
stmt = select([self.users.c.name]).order_by(self.users.c.name)
res = self.conn.execute(stmt).fetchall()
assert('jack' == res[0][0])
assert('wendy' == res[1][0])
# order by desc
stmt = select([self.users.c.name]).order_by(self.users.c.name.desc())
res = self.conn.execute(stmt).fetchall()
assert('wendy' == res[0][0])
assert('jack' == res[1][0])
# group by
stmt = select([self.users.c.name, func.count(self.addresses.c.id)]).\
select_from(self.users.join(self.addresses)).\
group_by(self.users.c.name)
res = self.conn.execute(stmt).fetchall()
assert(res[1][0] == 'jack')
assert(res[0][0] == 'wendy')
assert(res[0][1] == res[1][1])
# group by having
stmt = select([self.users.c.name, func.count(self.addresses.c.id)]).\
select_from(self.users.join(self.addresses)).\
group_by(self.users.c.name).\
having(func.length(self.users.c.name) > 4)
res = self.conn.execute(stmt).fetchall()
assert(res[0] == ('wendy', 2))
# distinct
stmt = select([self.users.c.name]).\
where(self.addresses.c.email_address.contains(self.users.c.name)).distinct()
res = self.conn.execute(stmt).fetchall()
assert(len(res) == 2)
assert(res[0][0] != res[1][0])
# limit
stmt = select([self.users.c.name, self.addresses.c.email_address]).\
select_from(self.users.join(self.addresses)).\
limit(1)
res = self.conn.execute(stmt).fetchall()
assert(len(res) == 1)
# offset
# test union and except
from sqlalchemy.sql import except_, union
u = union(
self.addresses.select().where(self.addresses.c.email_address == '*****@*****.**'),
self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**')),)# .order_by(self.addresses.c.email_address)
# print(u)
# #res = self.conn.execute(u) this fails, syntax error order by expects pos integer?
u = except_(
self.addresses.select().where(self.addresses.c.email_address.like('%@%.com')),
self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**')))
res = self.conn.execute(u).fetchall()
assert(1, len(res))
u = except_(
union(
self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**')),
self.addresses.select().where(self.addresses.c.email_address.like('*****@*****.**'))
).alias().select(), self.addresses.select(self.addresses.c.email_address.like('*****@*****.**'))
)
res = self.conn.execute(u).fetchall()
assert(1, len(res))
# scalar subqueries
stmt = select([func.count(self.addresses.c.id)]).where(self.users.c.uid == self.addresses.c.user_id).as_scalar()
# we can place stmt as any other column within another select
res = self.conn.execute(select([self.users.c.name, stmt])).fetchall()
# res is a list of tuples, one tuple per user's name
assert(2, len(res))
u1 = res[0]
u2 = res[1]
assert(len(u1) == len(u2))
assert(u1[0] == u'jack')
assert(u1[1] == u2[1])
assert(u2[0] == u'wendy')
# we can label the inner query
stmt = select([func.count(self.addresses.c.id)]).\
where(self.users.c.uid == self.addresses.c.user_id).\
label("address_count")
res = self.conn.execute(select([self.users.c.name, stmt])).fetchall()
assert(2, len(res))
u1 = res[0]
u2 = res[1]
assert(len(u1) == 2)
assert(len(u2) == 2)
# inserts, updates, deletes
stmt = self.users.update().values(fullname="Fullname: " + self.users.c.name)
res = self.conn.execute(stmt)
assert('name_1' in res.last_updated_params())
assert(res.last_updated_params()['name_1'] == 'Fullname: ')
stmt = self.users.insert().values(name=bindparam('_name') + " .. name")
res = self.conn.execute(stmt, [{'uid': 4, '_name': 'name1'}, {'uid': 5, '_name': 'name2'}, {'uid': 6, '_name': 'name3'}, ])
# updates
stmt = self.users.update().where(self.users.c.name == 'jack').values(name='ed')
res = self.conn.execute(stmt)
assert(res.rowcount == 1)
assert(res.returns_rows is False)
# update many with bound params
stmt = self.users.update().where(self.users.c.name == bindparam('oldname')).\
values(name=bindparam('newname'))
res = self.conn.execute(stmt, [
{'oldname': 'jack', 'newname': 'ed'},
{'oldname': 'wendy', 'newname': 'mary'},
])
assert(res.returns_rows is False)
assert(res.rowcount == 1)
res = self.conn.execute(select([self.users]).where(self.users.c.name == 'ed'))
r = res.fetchone()
assert(r['name'] == 'ed')
# correlated updates
stmt = select([self.addresses.c.email_address]).\
where(self.addresses.c.user_id == self.users.c.uid).\
limit(1)
# this fails, syntax error bc of LIMIT - need TOP/SAMPLE instead
# Note: TOP can't be in a subquery
# res = self.conn.execute(self.users.update().values(fullname=stmt))
# multiple table updates
stmt = self.users.update().\
values(name='ed wood').\
where(self.users.c.uid == self.addresses.c.id).\
where(self.addresses.c.email_address.startswith('ed%'))
# this fails, teradata does update from set where not update set from where
# #res = self.conn.execute(stmt)
stmt = self.users.update().\
values({
self.users.c.name: 'ed wood',
self.addresses.c.email_address: '*****@*****.**'
}).\
where(self.users.c.uid == self.addresses.c.id).\
where(self.addresses.c.email_address.startswith('ed%'))
# fails but works on MySQL, should this work for us?
# #res = self.conn.execute(stmt)
# deletes
self.conn.execute(self.addresses.delete())
self.conn.execute(self.users.delete().where(self.users.c.name > 'm'))
# matched row counts
# updates + deletes have a number indicating # rows matched by WHERE clause
res = self.conn.execute(self.users.delete())
assert(res.rowcount == 1)
from sqlalchemy.sql import bindparam
# now the generated clauses can serve as templates for use in conn.execute()
s = users.select(users.c.name == bindparam('username'))
for row in conn.execute(s, username='******'):
print row
for row in conn.execute(s, username='******'):
print row
### calculate function with different bind parameters
from sqlalchemy.sql import func, column
# calculate is a function accepting two parameters and returning a result set
# of three values, supported in Oracle/PostgreSQL.
calculate = select([column('q'), column('z'), column('r')]
).select_from(
func.calculate(
bindparam('x'),
bindparam('y')
)
)
calc = calculate.alias()
print select([users]).where(users.c.id > calc.c.z)
# unique_params() is used such that our calculate statement can be used twice.
calc1 = calculate.alias('c1').unique_params(x=17, y=45)
calc2 = calculate.alias('c2').unique_params(x=5, y=12)
s = select([users]).where(users.c.id.between(calc1.c.z, calc2.c.z))
print s
print s.compile().params
# window function
s = select([
users.c.id,
func.row_number().over(order_by=users.c.name)
