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)