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
)
def test_insert_returning(self):
dialect = postgres.dialect()
table1 = table(
'mytable',
column('myid', Integer),
column('name', String(128)),
column('description', String(128)),
)
i = insert(table1,
values=dict(name='foo'),
postgres_returning=[table1.c.myid, table1.c.name])
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (%(name)s) RETURNING mytable.myid, mytable.name",
dialect=dialect)
i = insert(table1,
values=dict(name='foo'),
postgres_returning=[table1])
self.assert_compile(i, "INSERT INTO mytable (name) VALUES (%(name)s) "\
"RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)
i = insert(table1,
values=dict(name='foo'),
postgres_returning=[func.length(table1.c.name)])
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (%(name)s) RETURNING length(mytable.name)",
dialect=dialect)
def test_update_returning(self):
dialect = postgres.dialect()
table1 = table(
'mytable',
column('myid', Integer),
column('name', String(128)),
column('description', String(128)),
)
u = update(table1,
values=dict(name='foo'),
postgres_returning=[table1.c.myid, table1.c.name])
self.assert_compile(
u,
"UPDATE mytable SET name=%(name)s RETURNING mytable.myid, mytable.name",
dialect=dialect)
u = update(table1,
values=dict(name='foo'),
postgres_returning=[table1])
self.assert_compile(u, "UPDATE mytable SET name=%(name)s "\
"RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)
u = update(table1,
values=dict(name='foo'),
postgres_returning=[func.length(table1.c.name)])
self.assert_compile(
u,
"UPDATE mytable SET name=%(name)s RETURNING length(mytable.name)",
dialect=dialect)
def test_insert_returning(self):
dialect = postgres.dialect()
table1 = table('mytable',
column('myid', Integer),
column('name', String(128)),
column('description', String(128)),
)
i = insert(table1, values=dict(name='foo'), postgres_returning=[table1.c.myid, table1.c.name])
self.assert_compile(i, "INSERT INTO mytable (name) VALUES (%(name)s) RETURNING mytable.myid, mytable.name", dialect=dialect)
i = insert(table1, values=dict(name='foo'), postgres_returning=[table1])
self.assert_compile(i, "INSERT INTO mytable (name) VALUES (%(name)s) "\
"RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)
i = insert(table1, values=dict(name='foo'), postgres_returning=[func.length(table1.c.name)])
self.assert_compile(i, "INSERT INTO mytable (name) VALUES (%(name)s) RETURNING length(mytable.name)", dialect=dialect)
def test_update_returning(self):
dialect = postgres.dialect()
table1 = table('mytable',
column('myid', Integer),
column('name', String(128)),
column('description', String(128)),
)
u = update(table1, values=dict(name='foo'), postgres_returning=[table1.c.myid, table1.c.name])
self.assert_compile(u, "UPDATE mytable SET name=%(name)s RETURNING mytable.myid, mytable.name", dialect=dialect)
u = update(table1, values=dict(name='foo'), postgres_returning=[table1])
self.assert_compile(u, "UPDATE mytable SET name=%(name)s "\
"RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)
u = update(table1, values=dict(name='foo'), postgres_returning=[func.length(table1.c.name)])
self.assert_compile(u, "UPDATE mytable SET name=%(name)s RETURNING length(mytable.name)", dialect=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))
class CompileTest(TestBase, AssertsCompiledSQL):
__dialect__ = postgres.dialect()
def test_update_returning(self):
dialect = postgres.dialect()
table1 = table(
'mytable',
column('myid', Integer),
column('name', String(128)),
column('description', String(128)),
)
u = update(table1,
values=dict(name='foo'),
postgres_returning=[table1.c.myid, table1.c.name])
self.assert_compile(
u,
"UPDATE mytable SET name=%(name)s RETURNING mytable.myid, mytable.name",
dialect=dialect)
u = update(table1,
values=dict(name='foo'),
postgres_returning=[table1])
self.assert_compile(u, "UPDATE mytable SET name=%(name)s "\
"RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)
u = update(table1,
values=dict(name='foo'),
postgres_returning=[func.length(table1.c.name)])
self.assert_compile(
u,
"UPDATE mytable SET name=%(name)s RETURNING length(mytable.name)",
dialect=dialect)
def test_insert_returning(self):
dialect = postgres.dialect()
table1 = table(
'mytable',
column('myid', Integer),
column('name', String(128)),
column('description', String(128)),
)
i = insert(table1,
values=dict(name='foo'),
postgres_returning=[table1.c.myid, table1.c.name])
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (%(name)s) RETURNING mytable.myid, mytable.name",
dialect=dialect)
i = insert(table1,
values=dict(name='foo'),
postgres_returning=[table1])
self.assert_compile(i, "INSERT INTO mytable (name) VALUES (%(name)s) "\
"RETURNING mytable.myid, mytable.name, mytable.description", dialect=dialect)
i = insert(table1,
values=dict(name='foo'),
postgres_returning=[func.length(table1.c.name)])
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (%(name)s) RETURNING length(mytable.name)",
dialect=dialect)
def test_extract(self):
t = table('t', column('col1'))
for field in 'year', 'month', 'day':
self.assert_compile(
select([extract(field, t.c.col1)]),
"SELECT EXTRACT(%s FROM t.col1::timestamp) AS anon_1 "
"FROM t" % field)