def test_dialect_specific(self): class AddThingy(ClauseElement): __visit_name__ = 'add_thingy' class DropThingy(ClauseElement): __visit_name__ = 'drop_thingy' @compiles(AddThingy, 'sqlite') def visit_add_thingy(thingy, compiler, **kw): return "ADD SPECIAL SL THINGY" @compiles(AddThingy) def visit_add_thingy(thingy, compiler, **kw): return "ADD THINGY" @compiles(DropThingy) def visit_drop_thingy(thingy, compiler, **kw): return "DROP THINGY" self.assert_compile(AddThingy(), "ADD THINGY" ) self.assert_compile(DropThingy(), "DROP THINGY" ) from sqlalchemy.databases import sqlite as base self.assert_compile(AddThingy(), "ADD SPECIAL SL THINGY", dialect=base.dialect() ) self.assert_compile(DropThingy(), "DROP THINGY", dialect=base.dialect() ) @compiles(DropThingy, 'sqlite') def visit_drop_thingy(thingy, compiler, **kw): return "DROP SPECIAL SL THINGY" self.assert_compile(DropThingy(), "DROP SPECIAL SL THINGY", dialect=base.dialect() ) self.assert_compile(DropThingy(), "DROP THINGY", )
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 testtextbinds(self): self.runtest( text("select * from foo where lala=:bar and hoho=:whee"), "select * from foo where lala=:bar and hoho=:whee", checkparams={'bar':4, 'whee': 7}, params={'bar':4, 'whee': 7, 'hoho':10}, ) dialect = postgres.dialect() self.runtest( text("select * from foo where lala=:bar and hoho=:whee"), "select * from foo where lala=%(bar)s and hoho=%(whee)s", checkparams={'bar':4, 'whee': 7}, params={'bar':4, 'whee': 7, 'hoho':10}, dialect=dialect ) dialect = sqlite.dialect() self.runtest( text("select * from foo where lala=:bar and hoho=:whee"), "select * from foo where lala=? and hoho=?", checkparams=[4, 7], params={'bar':4, 'whee': 7, 'hoho':10}, dialect=dialect )
class SQLTest(TestBase, AssertsCompiledSQL): """Tests SQLite-dialect specific compilation.""" __dialect__ = sqlite.dialect() def test_extract(self): t = sql.table('t', sql.column('col1')) mapping = { 'month': '%m', 'day': '%d', 'year': '%Y', 'second': '%S', 'hour': '%H', 'doy': '%j', 'minute': '%M', 'epoch': '%s', 'dow': '%w', 'week': '%W', } for field, subst in mapping.items(): self.assert_compile( select([extract(field, t.c.col1)]), "SELECT CAST(STRFTIME('%s', t.col1) AS INTEGER) AS anon_1 " "FROM t" % subst)
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_dialect_specific(self): class AddThingy(ClauseElement): __visit_name__ = 'add_thingy' class DropThingy(ClauseElement): __visit_name__ = 'drop_thingy' @compiles(AddThingy, 'sqlite') def visit_add_thingy(thingy, compiler, **kw): return "ADD SPECIAL SL THINGY" @compiles(AddThingy) def visit_add_thingy(thingy, compiler, **kw): return "ADD THINGY" @compiles(DropThingy) def visit_drop_thingy(thingy, compiler, **kw): return "DROP THINGY" self.assert_compile(AddThingy(), "ADD THINGY") self.assert_compile(DropThingy(), "DROP THINGY") from sqlalchemy.databases import sqlite as base self.assert_compile(AddThingy(), "ADD SPECIAL SL THINGY", dialect=base.dialect()) self.assert_compile(DropThingy(), "DROP THINGY", dialect=base.dialect()) @compiles(DropThingy, 'sqlite') def visit_drop_thingy(thingy, compiler, **kw): return "DROP SPECIAL SL THINGY" self.assert_compile(DropThingy(), "DROP SPECIAL SL THINGY", dialect=base.dialect()) self.assert_compile( DropThingy(), "DROP THINGY", )
def test_no_convert_unicode(self): """test no utf-8 encoding occurs""" dialect = sqlite.dialect() for t in (String(convert_unicode=True), CHAR(convert_unicode=True), Unicode(), UnicodeText(), String(assert_unicode=True, convert_unicode=True), CHAR(assert_unicode=True, convert_unicode=True), Unicode(assert_unicode=True), UnicodeText(assert_unicode=True)): bindproc = t.dialect_impl(dialect).bind_processor(dialect) assert not bindproc or isinstance(bindproc(u"some string"), unicode)
def test_no_convert_unicode(self): """test no utf-8 encoding occurs""" dialect = sqlite.dialect() for t in ( String(convert_unicode=True), CHAR(convert_unicode=True), Unicode(), UnicodeText(), String(assert_unicode=True, convert_unicode=True), CHAR(assert_unicode=True, convert_unicode=True), Unicode(assert_unicode=True), UnicodeText(assert_unicode=True) ): bindproc = t.dialect_impl(dialect).bind_processor(dialect) assert not bindproc or isinstance(bindproc(u"some string"), unicode)
def testbindparam(self): for ( stmt, expected_named_stmt, expected_positional_stmt, expected_default_params_dict, expected_default_params_list, test_param_dict, expected_test_params_dict, expected_test_params_list ) in [ ( select( [table1, table2], and_( table1.c.myid == table2.c.otherid, table1.c.name == bindparam('mytablename') )), """SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername FROM mytable, myothertable WHERE mytable.myid = myothertable.otherid AND mytable.name = :mytablename""", """SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, myothertable.othername FROM mytable, myothertable WHERE mytable.myid = myothertable.otherid AND mytable.name = ?""", {'mytablename':None}, [None], {'mytablename':5}, {'mytablename':5}, [5] ), ( select([table1], or_(table1.c.myid==bindparam('myid'), table2.c.otherid==bindparam('myid'))), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable, myothertable WHERE mytable.myid = :myid OR myothertable.otherid = :myid", "SELECT mytable.myid, mytable.name, mytable.description FROM mytable, myothertable WHERE mytable.myid = ? OR myothertable.otherid = ?", {'myid':None}, [None, None], {'myid':5}, {'myid':5}, [5,5] ), ( text("SELECT mytable.myid, mytable.name, mytable.description FROM mytable, myothertable WHERE mytable.myid = :myid OR myothertable.otherid = :myid"), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable, myothertable WHERE mytable.myid = :myid OR myothertable.otherid = :myid", "SELECT mytable.myid, mytable.name, mytable.description FROM mytable, myothertable WHERE mytable.myid = ? OR myothertable.otherid = ?", {'myid':None}, [None, None], {'myid':5}, {'myid':5}, [5,5] ), ( select([table1], or_(table1.c.myid==bindparam('myid', unique=True), table2.c.otherid==bindparam('myid', unique=True))), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable, myothertable WHERE mytable.myid = :myid OR myothertable.otherid = :my_1", "SELECT mytable.myid, mytable.name, mytable.description FROM mytable, myothertable WHERE mytable.myid = ? OR myothertable.otherid = ?", {'myid':None, 'my_1':None}, [None, None], {'myid':5, 'my_1': 6}, {'myid':5, 'my_1':6}, [5,6] ), ( select([table1], or_(table1.c.myid==bindparam('myid', value=7, unique=True), table2.c.otherid==bindparam('myid', value=8, unique=True))), "SELECT mytable.myid, mytable.name, mytable.description FROM mytable, myothertable WHERE mytable.myid = :myid OR myothertable.otherid = :my_1", "SELECT mytable.myid, mytable.name, mytable.description FROM mytable, myothertable WHERE mytable.myid = ? OR myothertable.otherid = ?", {'myid':7, 'my_1':8}, [7,8], {'myid':5, 'my_1':6}, {'myid':5, 'my_1':6}, [5,6] ), ][2:3]: self.runtest(stmt, expected_named_stmt, params=expected_default_params_dict) self.runtest(stmt, expected_positional_stmt, dialect=sqlite.dialect()) nonpositional = stmt.compile() positional = stmt.compile(dialect=sqlite.dialect()) assert positional.get_params().get_raw_list() == expected_default_params_list assert nonpositional.get_params(**test_param_dict).get_raw_dict() == expected_test_params_dict, "expected :%s got %s" % (str(expected_test_params_dict), str(nonpositional.get_params(**test_param_dict).get_raw_dict())) assert positional.get_params(**test_param_dict).get_raw_list() == expected_test_params_list # check that conflicts with "unique" params are caught s = select([table1], or_(table1.c.myid==7, table1.c.myid==bindparam('mytable_myid'))) try: str(s) assert False except exceptions.CompileError, err: assert str(err) == "Bind parameter 'mytable_myid' conflicts with unique bind parameter of the same name"