def test_select_composition_seven(self):
self.assert_compile(
select(
[literal_column("col1"),
literal_column("col2")],
from_obj=table("tablename"),
).alias("myalias"),
"SELECT col1, col2 FROM tablename",
)
def test_limit_special_quoting(self):
"""Oracle-specific test for #4730.
Even though this issue is generic, test the originally reported Oracle
use case.
"""
col = literal_column("SUM(ABC)").label("SUM(ABC)")
tbl = table("my_table")
query = select([col]).select_from(tbl).order_by(col).limit(100)
self.assert_compile(
query,
'SELECT "SUM(ABC)" FROM '
'(SELECT SUM(ABC) AS "SUM(ABC)" '
"FROM my_table ORDER BY SUM(ABC)) "
"WHERE ROWNUM <= :param_1",
)
col = literal_column("SUM(ABC)").label(quoted_name("SUM(ABC)", True))
tbl = table("my_table")
query = select([col]).select_from(tbl).order_by(col).limit(100)
self.assert_compile(
query,
'SELECT "SUM(ABC)" FROM '
'(SELECT SUM(ABC) AS "SUM(ABC)" '
"FROM my_table ORDER BY SUM(ABC)) "
"WHERE ROWNUM <= :param_1",
)
col = literal_column("SUM(ABC)").label("SUM(ABC)_")
tbl = table("my_table")
query = select([col]).select_from(tbl).order_by(col).limit(100)
self.assert_compile(
query,
'SELECT "SUM(ABC)_" FROM '
'(SELECT SUM(ABC) AS "SUM(ABC)_" '
"FROM my_table ORDER BY SUM(ABC)) "
"WHERE ROWNUM <= :param_1",
)
col = literal_column("SUM(ABC)").label(quoted_name("SUM(ABC)_", True))
tbl = table("my_table")
query = select([col]).select_from(tbl).order_by(col).limit(100)
self.assert_compile(
query,
'SELECT "SUM(ABC)_" FROM '
'(SELECT SUM(ABC) AS "SUM(ABC)_" '
"FROM my_table ORDER BY SUM(ABC)) "
"WHERE ROWNUM <= :param_1",
)
def test_text_in_select_nonfrom(self):
generate_series = text(
"generate_series(:x, :y, :z) as s(a)").bindparams(x=None,
y=None,
z=None)
s = select([
(func.current_date() + literal_column("s.a")).label("dates")
]).select_from(generate_series)
self.assert_compile(
s,
"SELECT CURRENT_DATE + s.a AS dates FROM "
"generate_series(:x, :y, :z) as s(a)",
checkparams={
"y": None,
"x": None,
"z": None
},
)
self.assert_compile(
s.params(x=5, y=6, z=7),
"SELECT CURRENT_DATE + s.a AS dates FROM "
"generate_series(:x, :y, :z) as s(a)",
checkparams={
"y": 6,
"x": 5,
"z": 7
},
)
def test_select_composition_one(self):
self.assert_compile(
select(
[
literal_column("foobar(a)"),
literal_column("pk_foo_bar(syslaal)"),
],
text("a = 12"),
from_obj=[
text(
"foobar left outer join lala on foobar.foo = lala.foo")
],
),
"SELECT foobar(a), pk_foo_bar(syslaal) FROM foobar "
"left outer join lala on foobar.foo = lala.foo WHERE a = 12",
)
def test_order_by_literal_col_quoting_one_explict_quote(self):
col = literal_column("SUM(ABC)").label(quoted_name("SUM(ABC)", True))
tbl = table("my_table")
query = select([col]).select_from(tbl).order_by(col)
self.assert_compile(
query,
'SELECT SUM(ABC) AS "SUM(ABC)" FROM my_table ORDER BY "SUM(ABC)"',
)
def test_numeric_bind_round_trip(self):
eq_(
testing.db.scalar(
select([
literal_column("2", type_=Integer()) +
bindparam("2_1", value=2)
])),
4,
)
def test_order_by_literal_col_quoting_two(self):
col = literal_column("SUM(ABC)").label("SUM(ABC)_")
tbl = table("my_table")
query = select([col]).select_from(tbl).order_by(col)
self.assert_compile(
query,
'SELECT SUM(ABC) AS "SUM(ABC)_" FROM my_table ORDER BY '
'"SUM(ABC)_"',
)
def test_select_composition_six(self):
# test that "auto-labeling of subquery columns"
# doesn't interfere with literal columns,
# exported columns don't get quoted.
# [ticket:4730] refines this but for the moment the behavior with
# no columns is being maintained.
self.assert_compile(
select(
[
literal_column("column1 AS foobar"),
literal_column("column2 AS hoho"),
table1.c.myid,
],
from_obj=[table1],
).select(),
"SELECT column1 AS foobar, column2 AS hoho, myid FROM "
"(SELECT column1 AS foobar, column2 AS hoho, "
"mytable.myid AS myid FROM mytable)",
)
def test_tuple_containment(self):
for test, exp in [
([("a", "b")], True),
([("a", "c")], False),
([("f", "q"), ("a", "b")], True),
([("f", "q"), ("a", "c")], False),
]:
eq_(
testing.db.execute(
select([
tuple_(literal_column("'a'"),
literal_column("'b'")).in_([
tuple_(*[
literal_column("'%s'" % letter)
for letter in elem
]) for elem in test
])
])).scalar(),
exp,
)
def test_as_comparison_many_argument(self):
fn = func.some_comparison("x", "y", "z", "p", "q", "r").as_comparison(
2, 5
)
is_(fn.type._type_affinity, Boolean)
self.assert_compile(
fn.left,
":some_comparison_1",
checkparams={"some_comparison_1": "y"},
)
self.assert_compile(
fn.right,
":some_comparison_1",
checkparams={"some_comparison_1": "q"},
)
from sqlalchemy_1_3.sql import visitors
fn_2 = visitors.cloned_traverse(fn, {}, {})
fn_2.right = literal_column("ABC")
self.assert_compile(
fn,
"some_comparison(:some_comparison_1, :some_comparison_2, "
":some_comparison_3, "
":some_comparison_4, :some_comparison_5, :some_comparison_6)",
checkparams={
"some_comparison_1": "x",
"some_comparison_2": "y",
"some_comparison_3": "z",
"some_comparison_4": "p",
"some_comparison_5": "q",
"some_comparison_6": "r",
},
)
self.assert_compile(
fn_2,
"some_comparison(:some_comparison_1, :some_comparison_2, "
":some_comparison_3, "
":some_comparison_4, ABC, :some_comparison_5)",
checkparams={
"some_comparison_1": "x",
"some_comparison_2": "y",
"some_comparison_3": "z",
"some_comparison_4": "p",
"some_comparison_5": "r",
},
)
def test_funcfilter_fromobj_fromfunc(self):
# test from_obj generation.
# from func:
self.assert_compile(
select(
[
func.max(table1.c.name).filter(
literal_column("description") != None # noqa
)
]
),
"SELECT max(mytable.name) FILTER (WHERE description "
"IS NOT NULL) AS anon_1 FROM mytable",
)
def test_insert_w_newlines(self):
from psycopg2 import extras
t = self.tables.data
ins = t.insert(inline=True).values(
id=bindparam("id"),
x=select([literal_column("5")]).select_from(self.tables.data),
y=bindparam("y"),
z=bindparam("z"),
)
# compiled SQL has a newline in it
eq_(
str(ins.compile(testing.db)),
"INSERT INTO data (id, x, y, z) VALUES (%(id)s, "
"(SELECT 5 \nFROM data), %(y)s, %(z)s)",
)
meth = extras.execute_values
with mock.patch.object(
extras, "execute_values", side_effect=meth
) as mock_exec:
with self.engine.connect() as conn:
conn.execute(
ins,
[
{"id": 1, "y": "y1", "z": 1},
{"id": 2, "y": "y2", "z": 2},
{"id": 3, "y": "y3", "z": 3},
],
)
eq_(
mock_exec.mock_calls,
[
mock.call(
mock.ANY,
"INSERT INTO data (id, x, y, z) VALUES %s",
(
{"id": 1, "y": "y1", "z": 1},
{"id": 2, "y": "y2", "z": 2},
{"id": 3, "y": "y3", "z": 3},
),
template="(%(id)s, (SELECT 5 \nFROM data), %(y)s, %(z)s)",
)
],
)
def test_query_one(self):
q = (
Session.query(User)
.filter(User.name == "ed")
.options(joinedload(User.addresses))
)
q2 = serializer.loads(serializer.dumps(q, -1), users.metadata, Session)
def go():
eq_(
q2.all(),
[
User(
name="ed",
addresses=[
Address(id=2),
Address(id=3),
Address(id=4),
],
)
],
)
self.assert_sql_count(testing.db, go, 1)
eq_(
q2.join(User.addresses)
.filter(Address.email == "*****@*****.**")
.value(func.count(literal_column("*"))),
1,
)
u1 = Session.query(User).get(8)
q = (
Session.query(Address)
.filter(Address.user == u1)
.order_by(desc(Address.email))
)
q2 = serializer.loads(serializer.dumps(q, -1), users.metadata, Session)
eq_(
q2.all(),
[
Address(email="*****@*****.**"),
Address(email="*****@*****.**"),
Address(email="*****@*****.**"),
],
)
def test_simple_limit_expression_offset_using_window(self):
t = table("t", column("x", Integer), column("y", Integer))
s = (select([t]).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(
literal_column("20")))
self.assert_compile(
s,
"SELECT anon_1.x, anon_1.y "
"FROM (SELECT t.x AS x, t.y AS y, "
"ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn "
"FROM t "
"WHERE t.x = :x_1) AS anon_1 "
"WHERE mssql_rn > 20 AND mssql_rn <= :param_1 + 20",
checkparams={
"param_1": 10,
"x_1": 5
},
)
def test_text_doesnt_explode(self):
for s in [
select([
case(
[(info_table.c.info == "pk_4_data", text("'yes'"))],
else_=text("'no'"),
)
]).order_by(info_table.c.info),
select([
case(
[(
info_table.c.info == "pk_4_data",
literal_column("'yes'"),
)],
else_=literal_column("'no'"),
)
]).order_by(info_table.c.info),
]:
if testing.against("firebird"):
eq_(
s.execute().fetchall(),
[
("no ", ),
("no ", ),
("no ", ),
("yes", ),
("no ", ),
("no ", ),
],
)
else:
eq_(
s.execute().fetchall(),
[("no", ), ("no", ), ("no", ), ("yes", ), ("no", ),
("no", )],
)
def define_tables(cls, metadata):
default_generator = cls.default_generator = {"x": 50}
def mydefault():
default_generator["x"] += 1
return default_generator["x"]
def myupdate_with_ctx(ctx):
conn = ctx.connection
return conn.execute(sa.select([sa.text("13")])).scalar()
def mydefault_using_connection(ctx):
conn = ctx.connection
return conn.execute(sa.select([sa.text("12")])).scalar()
use_function_defaults = testing.against("postgresql", "mssql")
is_oracle = testing.against("oracle")
class MyClass(object):
@classmethod
def gen_default(cls, ctx):
return "hi"
class MyType(TypeDecorator):
impl = String(50)
def process_bind_param(self, value, dialect):
if value is not None:
value = "BIND" + value
return value
cls.f = 6
cls.f2 = 11
with testing.db.connect() as conn:
currenttime = cls.currenttime = func.current_date(type_=sa.Date)
if is_oracle:
ts = conn.scalar(
sa.select([
func.trunc(
func.current_timestamp(),
sa.literal_column("'DAY'"),
type_=sa.Date,
)
]))
currenttime = cls.currenttime = func.trunc(
currenttime, sa.literal_column("'DAY'"), type_=sa.Date)
def1 = currenttime
def2 = func.trunc(
sa.text("current_timestamp"),
sa.literal_column("'DAY'"),
type_=sa.Date,
)
deftype = sa.Date
elif use_function_defaults:
def1 = currenttime
deftype = sa.Date
if testing.against("mssql"):
def2 = sa.text("getdate()")
else:
def2 = sa.text("current_date")
ts = conn.scalar(func.current_date())
else:
def1 = def2 = "3"
ts = 3
deftype = Integer
cls.ts = ts
Table(
"default_test",
metadata,
# python function
Column("col1", Integer, primary_key=True, default=mydefault),
# python literal
Column(
"col2",
String(20),
default="imthedefault",
onupdate="im the update",
),
# preexecute expression
Column(
"col3",
Integer,
default=func.length("abcdef"),
onupdate=func.length("abcdefghijk"),
),
# SQL-side default from sql expression
Column("col4", deftype, server_default=def1),
# SQL-side default from literal expression
Column("col5", deftype, server_default=def2),
# preexecute + update timestamp
Column("col6", sa.Date, default=currenttime, onupdate=currenttime),
Column("boolcol1", sa.Boolean, default=True),
Column("boolcol2", sa.Boolean, default=False),
# python function which uses ExecutionContext
Column(
"col7",
Integer,
default=mydefault_using_connection,
onupdate=myupdate_with_ctx,
),
# python builtin
Column(
"col8",
sa.Date,
default=datetime.date.today,
onupdate=datetime.date.today,
),
# combo
Column("col9", String(20), default="py", server_default="ddl"),
# python method w/ context
Column("col10", String(20), default=MyClass.gen_default),
# fixed default w/ type that has bound processor
Column("col11", MyType(), default="foo"),
)
def _modify_query(query):
m1(query.column_descriptions[0]["entity"])
query = query.enable_assertions(False).filter(
literal_column("1") == 1)
return query
def test_insert_modified_by_event(self):
from psycopg2 import extras
t = self.tables.data
ins = t.insert(inline=True).values(
id=bindparam("id"),
x=select([literal_column("5")]).select_from(self.tables.data),
y=bindparam("y"),
z=bindparam("z"),
)
# compiled SQL has a newline in it
eq_(
str(ins.compile(testing.db)),
"INSERT INTO data (id, x, y, z) VALUES (%(id)s, "
"(SELECT 5 \nFROM data), %(y)s, %(z)s)",
)
meth = extras.execute_batch
with mock.patch.object(
extras, "execute_values"
) as mock_values, mock.patch.object(
extras, "execute_batch", side_effect=meth
) as mock_batch:
with self.engine.connect() as conn:
# create an event hook that will change the statement to
# something else, meaning the dialect has to detect that
# insert_single_values_expr is no longer useful
@event.listens_for(conn, "before_cursor_execute", retval=True)
def before_cursor_execute(
conn, cursor, statement, parameters, context, executemany
):
statement = (
"INSERT INTO data (id, y, z) VALUES "
"(%(id)s, %(y)s, %(z)s)"
)
return statement, parameters
conn.execute(
ins,
[
{"id": 1, "y": "y1", "z": 1},
{"id": 2, "y": "y2", "z": 2},
{"id": 3, "y": "y3", "z": 3},
],
)
eq_(mock_values.mock_calls, [])
eq_(
mock_batch.mock_calls,
[
mock.call(
mock.ANY,
"INSERT INTO data (id, y, z) VALUES "
"(%(id)s, %(y)s, %(z)s)",
(
{"id": 1, "y": "y1", "z": 1},
{"id": 2, "y": "y2", "z": 2},
{"id": 3, "y": "y3", "z": 3},
),
)
],
)