def testcast(self): tbl = table('casttest', column('id', Integer), column('v1', Float), column('v2', Float), column('ts', TIMESTAMP), ) def check_results(dialect, expected_results, literal): self.assertEqual(len(expected_results), 5, 'Incorrect number of expected results') self.assertEqual(str(cast(tbl.c.v1, Numeric).compile(dialect=dialect)), 'CAST(casttest.v1 AS %s)' %expected_results[0]) self.assertEqual(str(cast(tbl.c.v1, Numeric(12, 9)).compile(dialect=dialect)), 'CAST(casttest.v1 AS %s)' %expected_results[1]) self.assertEqual(str(cast(tbl.c.ts, Date).compile(dialect=dialect)), 'CAST(casttest.ts AS %s)' %expected_results[2]) self.assertEqual(str(cast(1234, TEXT).compile(dialect=dialect)), 'CAST(%s AS %s)' %(literal, expected_results[3])) self.assertEqual(str(cast('test', String(20)).compile(dialect=dialect)), 'CAST(%s AS %s)' %(literal, expected_results[4])) sel = select([tbl, cast(tbl.c.v1, Numeric)]).compile(dialect=dialect) self.assertEqual(str(sel), "SELECT casttest.id, casttest.v1, casttest.v2, casttest.ts, CAST(casttest.v1 AS NUMERIC(10, 2)) \nFROM casttest") # first test with Postgres engine check_results(postgres.dialect(), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '%(literal)s') # then the Oracle engine check_results(oracle.dialect(), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'CLOB', 'VARCHAR(20)'], ':literal') # then the sqlite engine check_results(sqlite.dialect(), ['NUMERIC(10, 2)', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '?') # MySQL seems to only support DATE types for cast self.assertEqual(str(cast(tbl.c.ts, Date).compile(dialect=mysql.dialect())), 'CAST(casttest.ts AS DATE)') self.assertEqual(str(cast(tbl.c.ts, Numeric).compile(dialect=mysql.dialect())), 'casttest.ts')
def compileQuery(self,dbsInst,sel): if type(sel) is types.StringType: return sel if self.dbType[dbsInst]=='oracle': return sel.compile(dialect=oracle.dialect()) if self.dbType[dbsInst]=='mysql': return sel.compile(dialect=mysql.dialect()) if self.dbType[dbsInst]=='sqlite': return sel.compile(dialect=sqlite.dialect())
def test_outer_join(self): table1 = table('mytable', column('myid', Integer), column('name', String), column('description', String), ) table2 = table( 'myothertable', column('otherid', Integer), column('othername', String), ) table3 = table( 'thirdtable', column('userid', Integer), column('otherstuff', String), ) query = select( [table1, table2], or_( table1.c.name == 'fred', table1.c.myid == 10, table2.c.othername != 'jack', "EXISTS (select yay from foo where boo = lar)" ), from_obj = [ outerjoin(table1, table2, table1.c.myid == table2.c.otherid) ] ) self.assert_compile(query, "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername \ FROM mytable, myothertable WHERE \ (mytable.name = :name_1 OR mytable.myid = :myid_1 OR \ myothertable.othername != :othername_1 OR EXISTS (select yay from foo where boo = lar)) \ AND mytable.myid = myothertable.otherid(+)", dialect=oracle.OracleDialect(use_ansi = False)) query = table1.outerjoin(table2, table1.c.myid==table2.c.otherid).outerjoin(table3, table3.c.userid==table2.c.otherid) self.assert_compile(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = myothertable.otherid LEFT OUTER JOIN thirdtable ON thirdtable.userid = myothertable.otherid") self.assert_compile(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND mytable.myid = myothertable.otherid(+)", dialect=oracle.dialect(use_ansi=False)) query = table1.join(table2, table1.c.myid==table2.c.otherid).join(table3, table3.c.userid==table2.c.otherid) self.assert_compile(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE thirdtable.userid = myothertable.otherid AND mytable.myid = myothertable.otherid", dialect=oracle.dialect(use_ansi=False)) query = table1.join(table2, table1.c.myid==table2.c.otherid).outerjoin(table3, table3.c.userid==table2.c.otherid) self.assert_compile(query.select().order_by(table1.c.name).limit(10).offset(5), "SELECT myid, name, description, otherid, othername, userid, " "otherstuff FROM (SELECT myid, name, description, " "otherid, othername, userid, otherstuff, ROWNUM AS ora_rn FROM (SELECT " "mytable.myid AS myid, mytable.name AS name, mytable.description AS description, " "myothertable.otherid AS otherid, myothertable.othername AS othername, " "thirdtable.userid AS userid, thirdtable.otherstuff AS otherstuff FROM mytable, " "myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND " "mytable.myid = myothertable.otherid ORDER BY mytable.name) WHERE " "ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1", dialect=oracle.dialect(use_ansi=False))
def test_long_labels(self): dialect = default.DefaultDialect() dialect.max_identifier_length = 30 ora_dialect = oracle.dialect() m = MetaData() a_table = Table( 'thirty_characters_table_xxxxxx', m, Column('id', Integer, primary_key=True) ) other_table = Table( 'other_thirty_characters_table_', m, Column('id', Integer, primary_key=True), Column('thirty_characters_table_id', Integer, ForeignKey('thirty_characters_table_xxxxxx.id'), primary_key=True ) ) anon = a_table.alias() self.assert_compile( select([other_table, anon]).select_from( other_table.outerjoin(anon) ).apply_labels(), "SELECT other_thirty_characters_table_.id AS other_thirty_characters__1, " "other_thirty_characters_table_.thirty_characters_table_id AS other_thirty_characters__2, " "thirty_characters_table__1.id AS thirty_characters_table__3 FROM other_thirty_characters_table_ " "LEFT OUTER JOIN thirty_characters_table_xxxxxx AS thirty_characters_table__1 ON " "thirty_characters_table__1.id = other_thirty_characters_table_.thirty_characters_table_id", dialect=dialect ) self.assert_compile( select([other_table, anon]).select_from( other_table.outerjoin(anon) ).apply_labels(), "SELECT other_thirty_characters_table_.id AS other_thirty_characters__1, " "other_thirty_characters_table_.thirty_characters_table_id AS other_thirty_characters__2, " "thirty_characters_table__1.id AS thirty_characters_table__3 FROM other_thirty_characters_table_ " "LEFT OUTER JOIN thirty_characters_table_xxxxxx thirty_characters_table__1 ON " "thirty_characters_table__1.id = other_thirty_characters_table_.thirty_characters_table_id", dialect=ora_dialect )
def test_long_labels(self): dialect = default.DefaultDialect() dialect.max_identifier_length = 30 ora_dialect = oracle.dialect() m = MetaData() a_table = Table('thirty_characters_table_xxxxxx', m, Column('id', Integer, primary_key=True)) other_table = Table( 'other_thirty_characters_table_', m, Column('id', Integer, primary_key=True), Column('thirty_characters_table_id', Integer, ForeignKey('thirty_characters_table_xxxxxx.id'), primary_key=True)) anon = a_table.alias() self.assert_compile( select([other_table, anon ]).select_from(other_table.outerjoin(anon)).apply_labels(), "SELECT other_thirty_characters_table_.id AS other_thirty_characters__1, " "other_thirty_characters_table_.thirty_characters_table_id AS other_thirty_characters__2, " "thirty_characters_table__1.id AS thirty_characters_table__3 FROM other_thirty_characters_table_ " "LEFT OUTER JOIN thirty_characters_table_xxxxxx AS thirty_characters_table__1 ON " "thirty_characters_table__1.id = other_thirty_characters_table_.thirty_characters_table_id", dialect=dialect) self.assert_compile( select([other_table, anon ]).select_from(other_table.outerjoin(anon)).apply_labels(), "SELECT other_thirty_characters_table_.id AS other_thirty_characters__1, " "other_thirty_characters_table_.thirty_characters_table_id AS other_thirty_characters__2, " "thirty_characters_table__1.id AS thirty_characters_table__3 FROM other_thirty_characters_table_ " "LEFT OUTER JOIN thirty_characters_table_xxxxxx thirty_characters_table__1 ON " "thirty_characters_table__1.id = other_thirty_characters_table_.thirty_characters_table_id", dialect=ora_dialect)
def testouterjoin(self): # test an outer join. the oracle module should take the ON clause of the join and # move it up to the WHERE clause of its parent select, and append (+) to all right-hand-side columns # within the original onclause, but leave right-hand-side columns unchanged outside of the onclause # parameters. query = select( [table1, table2], and_( table1.c.name == 'fred', table1.c.myid == 10, table2.c.othername != 'jack', "EXISTS (select yay from foo where boo = lar)" ), from_obj = [ outerjoin(table1, table2, table1.c.myid == table2.c.otherid) ] ) self.runtest(query, "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername \ FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = myothertable.otherid \ WHERE mytable.name = %(mytable_name)s AND mytable.myid = %(mytable_myid)s AND \ myothertable.othername != %(myothertable_othername)s AND \ EXISTS (select yay from foo where boo = lar)", dialect=postgres.dialect() ) self.runtest(query, "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername \ FROM mytable, myothertable WHERE mytable.myid = myothertable.otherid(+) AND \ mytable.name = :mytable_name AND mytable.myid = :mytable_myid AND \ myothertable.othername != :myothertable_othername AND EXISTS (select yay from foo where boo = lar)", dialect=oracle.OracleDialect(use_ansi = False)) query = table1.outerjoin(table2, table1.c.myid==table2.c.otherid).outerjoin(table3, table3.c.userid==table2.c.otherid) self.runtest(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = myothertable.otherid LEFT OUTER JOIN thirdtable ON thirdtable.userid = myothertable.otherid") self.runtest(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE mytable.myid = myothertable.otherid(+) AND thirdtable.userid(+) = myothertable.otherid", dialect=oracle.dialect(use_ansi=False))
def testforupdate(self): self.runtest(table1.select(table1.c.myid==7, for_update=True), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid FOR UPDATE") self.runtest(table1.select(table1.c.myid==7, for_update="nowait"), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid FOR UPDATE") self.runtest(table1.select(table1.c.myid==7, for_update="nowait"), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid FOR UPDATE NOWAIT", dialect=oracle.dialect()) self.runtest(table1.select(table1.c.myid==7, for_update="read"), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = %s LOCK IN SHARE MODE", dialect=mysql.dialect()) self.runtest(table1.select(table1.c.myid==7, for_update=True), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = %s FOR UPDATE", dialect=mysql.dialect()) self.runtest(table1.select(table1.c.myid==7, for_update=True), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.myid = :mytable_myid FOR UPDATE", dialect=oracle.dialect())
def testoraclelimit(self): metadata = MetaData() users = Table('users', metadata, Column('name', String(10), key='username')) self.runtest(select([users.c.username], limit=5), "SELECT name FROM (SELECT users.name AS name, ROW_NUMBER() OVER (ORDER BY users.rowid) AS ora_rn FROM users) WHERE ora_rn<=5", dialect=oracle.dialect())
def test_outer_join(self): table1 = table( 'mytable', column('myid', Integer), column('name', String), column('description', String), ) table2 = table( 'myothertable', column('otherid', Integer), column('othername', String), ) table3 = table( 'thirdtable', column('userid', Integer), column('otherstuff', String), ) query = select([table1, table2], or_(table1.c.name == 'fred', table1.c.myid == 10, table2.c.othername != 'jack', "EXISTS (select yay from foo where boo = lar)"), from_obj=[ outerjoin(table1, table2, table1.c.myid == table2.c.otherid) ]) self.assert_compile( query, "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername \ FROM mytable, myothertable WHERE \ (mytable.name = :name_1 OR mytable.myid = :myid_1 OR \ myothertable.othername != :othername_1 OR EXISTS (select yay from foo where boo = lar)) \ AND mytable.myid = myothertable.otherid(+)", dialect=oracle.OracleDialect(use_ansi=False)) query = table1.outerjoin(table2, table1.c.myid == table2.c.otherid).outerjoin( table3, table3.c.userid == table2.c.otherid) self.assert_compile( query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = myothertable.otherid LEFT OUTER JOIN thirdtable ON thirdtable.userid = myothertable.otherid" ) self.assert_compile( query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND mytable.myid = myothertable.otherid(+)", dialect=oracle.dialect(use_ansi=False)) query = table1.join(table2, table1.c.myid == table2.c.otherid).join( table3, table3.c.userid == table2.c.otherid) self.assert_compile( query.select(), "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM mytable, myothertable, thirdtable WHERE thirdtable.userid = myothertable.otherid AND mytable.myid = myothertable.otherid", dialect=oracle.dialect(use_ansi=False)) query = table1.join(table2, table1.c.myid == table2.c.otherid).outerjoin( table3, table3.c.userid == table2.c.otherid) self.assert_compile( query.select().order_by(table1.c.name).limit(10).offset(5), "SELECT myid, name, description, otherid, othername, userid, " "otherstuff FROM (SELECT myid, name, description, " "otherid, othername, userid, otherstuff, ROWNUM AS ora_rn FROM (SELECT " "mytable.myid AS myid, mytable.name AS name, mytable.description AS description, " "myothertable.otherid AS otherid, myothertable.othername AS othername, " "thirdtable.userid AS userid, thirdtable.otherstuff AS otherstuff FROM mytable, " "myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND " "mytable.myid = myothertable.otherid ORDER BY mytable.name) WHERE " "ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1", dialect=oracle.dialect(use_ansi=False))