def test_typing_construction(self):
t = text("select * from table :foo :bar :bat")
self._assert_type_map(t, {
"foo": NullType(),
"bar": NullType(),
"bat": NullType()
})
t = t.bindparams(bindparam("foo", type_=String))
self._assert_type_map(t, {
"foo": String(),
"bar": NullType(),
"bat": NullType()
})
t = t.bindparams(bindparam("bar", type_=Integer))
self._assert_type_map(t, {
"foo": String(),
"bar": Integer(),
"bat": NullType()
})
t = t.bindparams(bat=45.564)
self._assert_type_map(t, {
"foo": String(),
"bar": Integer(),
"bat": Float()
})
def test_build_bindparams(self):
t = text("select id from user :foo :bar :bat")
t = t.bindparams(bindparam("foo", type_=Integer))
t = t.columns(id=Integer)
t = t.bindparams(bar=String)
t = t.bindparams(bindparam("bat", value="bat"))
eq_(set(t.element._bindparams), set(["bat", "foo", "bar"]))
def test_missing_bind_posn(self):
assert_raises_message(
exc.ArgumentError,
r"This text\(\) construct doesn't define "
r"a bound parameter named 'bar'",
text(":foo").bindparams,
bindparam("foo", value=5),
bindparam("bar", value=7),
)
def test_insert_values(self):
table1 = self.tables.mytable
values1 = {table1.c.myid: bindparam("userid")}
values2 = {table1.c.name: bindparam("username")}
self.assert_compile(
insert(table1, values=values1).values(values2),
"INSERT INTO mytable (myid, name) VALUES (:userid, :username)",
)
def test_compare_binds(self):
b1 = bindparam("foo", type_=Integer())
b2 = bindparam("foo", type_=Integer())
b3 = bindparam("bar", type_=Integer())
b4 = bindparam("foo", type_=String())
def c1():
return 5
def c2():
return 6
b5 = bindparam("foo", type_=Integer(), callable_=c1)
b6 = bindparam("foo", type_=Integer(), callable_=c2)
b7 = bindparam("foo", type_=Integer(), callable_=c1)
b8 = bindparam("foo", type_=Integer, value=5)
b9 = bindparam("foo", type_=Integer, value=6)
is_false(b1.compare(b5))
is_true(b5.compare(b7))
is_false(b5.compare(b6))
is_true(b1.compare(b2))
# currently not comparing "key", as we often have to compare
# anonymous names. however we should really check for that
is_true(b1.compare(b3))
is_false(b1.compare(b4))
is_false(b1.compare(b8))
is_false(b8.compare(b9))
is_true(b8.compare(b8))
def test_bindparam_name_no_consume_error(self):
t = table("t", column("x"), column("y"))
# bindparam names don't get counted
i = t.insert().values(x=3 + bindparam("x2"))
self.assert_compile(i, "INSERT INTO t (x) VALUES ((:param_1 + :x2))")
# even if in the params list
i = t.insert().values(x=3 + bindparam("x2"))
self.assert_compile(i,
"INSERT INTO t (x) VALUES ((:param_1 + :x2))",
params={"x2": 1})
def test_insert_with_user_supplied_bind_params(self):
table1 = self.tables.mytable
values = {
table1.c.myid: bindparam("userid"),
table1.c.name: bindparam("username"),
}
self.assert_compile(
insert(table1, values),
"INSERT INTO mytable (myid, name) VALUES (:userid, :username)",
)
def test_positional(self):
t = text("select * from foo where lala=:bar and hoho=:whee")
t = t.bindparams(bindparam("bar", 4), bindparam("whee", 7))
self.assert_compile(
t,
"select * from foo where lala=:bar and hoho=:whee",
checkparams={
"bar": 4,
"whee": 7
},
)
def test_bindparam_quote(self):
"""test that bound parameters take on quoting for reserved words,
column names quote flag enabled."""
# note: this is only in cx_oracle at the moment. not sure
# what other hypothetical oracle dialects might need
self.assert_compile(bindparam("option"), ':"option"')
self.assert_compile(bindparam("plain"), ":plain")
t = Table("s", MetaData(), Column("plain", Integer, quote=True))
self.assert_compile(
t.insert().values(plain=5),
'INSERT INTO s ("plain") VALUES (:"plain")',
)
self.assert_compile(t.update().values(plain=5),
'UPDATE s SET "plain"=:"plain"')
def test_unique_binds(self):
# unique binds can be used in text() however they uniquify across
# multiple text() constructs only, not within a single text
t1 = text("select :foo").bindparams(bindparam("foo", 5, unique=True))
t2 = text("select :foo").bindparams(bindparam("foo", 10, unique=True))
stmt = select([t1, t2])
self.assert_compile(
stmt,
"SELECT select :foo_1, select :foo_2",
checkparams={
"foo_1": 5,
"foo_2": 10
},
)
def test_different_limits(self):
User = self.classes.User
bq = self.bakery(
lambda s: s.query(User.id, User.name).order_by(User.id))
bq += lambda q: q.limit(bindparam("limit")).offset(bindparam("offset"))
session = Session(autocommit=True)
for i in range(4):
for limit, offset, exp in [
(2, 1, [(8, "ed"), (9, "fred")]),
(3, 0, [(7, "jack"), (8, "ed"), (9, "fred")]),
(1, 2, [(9, "fred")]),
]:
eq_(bq(session).params(limit=limit, offset=offset).all(), exp)
def test_callable_bind(self):
Address, addresses, users, User = (
self.classes.Address,
self.tables.addresses,
self.tables.users,
self.classes.User,
)
mapper(
User,
users,
properties=dict(addresses=relationship(
mapper(Address, addresses),
lazy="select",
primaryjoin=and_(
users.c.id == addresses.c.user_id,
users.c.name == bindparam("name", callable_=lambda: "ed"),
),
)),
)
s = Session()
ed = s.query(User).filter_by(name="ed").one()
eq_(
ed.addresses,
[
Address(id=2, user_id=8),
Address(id=3, user_id=8),
Address(id=4, user_id=8),
],
)
fred = s.query(User).filter_by(name="fred").one()
eq_(fred.addresses, []) # fred is missing
def test_subqueryload_post_context_w_cancelling_event(
self, before_compile_nobake_fixture):
User = self.classes.User
Address = self.classes.Address
assert_result = [
User(id=7,
addresses=[Address(id=1, email_address="*****@*****.**")])
]
self.bakery = baked.bakery(size=3)
bq = self.bakery(lambda s: s.query(User))
bq += lambda q: q.options(subqueryload(User.addresses))
bq += lambda q: q.order_by(User.id)
bq += lambda q: q.filter(User.name == bindparam("name"))
sess = Session()
def set_params(q):
return q.params(name="jack")
# test that the changes we make using with_post_criteria()
# are also applied to the subqueryload query.
def go():
result = bq(sess).with_post_criteria(set_params).all()
eq_(assert_result, result)
self.assert_sql_count(testing.db, go, 2)
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_binds_that_match_columns(self):
"""test bind params named after column names
replace the normal SET/VALUES generation."""
t = table("foo", column("x"), column("y"))
i = t.insert().values(x=3 + bindparam("x"))
self.assert_compile(i, "INSERT INTO foo (x) VALUES ((:param_1 + :x))")
self.assert_compile(
i,
"INSERT INTO foo (x, y) VALUES ((:param_1 + :x), :y)",
params={
"x": 1,
"y": 2
},
)
i = t.insert().values(x=bindparam("y"))
self.assert_compile(i, "INSERT INTO foo (x) VALUES (:y)")
i = t.insert().values(x=bindparam("y"), y=5)
assert_raises(exc.CompileError, i.compile)
i = t.insert().values(x=3 + bindparam("y"), y=5)
assert_raises(exc.CompileError, i.compile)
i = t.insert().values(x=3 + bindparam("x2"))
self.assert_compile(i, "INSERT INTO foo (x) VALUES ((:param_1 + :x2))")
self.assert_compile(i,
"INSERT INTO foo (x) VALUES ((:param_1 + :x2))",
params={})
self.assert_compile(
i,
"INSERT INTO foo (x, y) VALUES ((:param_1 + :x2), :y)",
params={
"x": 1,
"y": 2
},
)
self.assert_compile(
i,
"INSERT INTO foo (x, y) VALUES ((:param_1 + :x2), :y)",
params={
"x2": 1,
"y": 2
},
)
def test_legacy_bindparam(self):
with testing.expect_deprecated(
"The text.bindparams parameter is deprecated"):
t = text(
"select * from foo where lala=:bar and hoho=:whee",
bindparams=[bindparam("bar", 4),
bindparam("whee", 7)],
)
self.assert_compile(
t,
"select * from foo where lala=:bar and hoho=:whee",
checkparams={
"bar": 4,
"whee": 7
},
)
def test_custom_bind(self):
Address, addresses, users, User = (
self.classes.Address,
self.tables.addresses,
self.tables.users,
self.classes.User,
)
mapper(
User,
users,
properties=dict(addresses=relationship(
mapper(Address, addresses),
lazy="select",
primaryjoin=and_(
users.c.id == addresses.c.user_id,
users.c.name == bindparam("name"),
),
)),
)
canary = mock.Mock()
class MyOption(MapperOption):
propagate_to_loaders = True
def __init__(self, crit):
self.crit = crit
def process_query_conditionally(self, query):
"""process query during a lazyload"""
canary()
query._params = query._params.union(dict(name=self.crit))
s = Session()
ed = s.query(User).options(MyOption("ed")).filter_by(name="ed").one()
eq_(
ed.addresses,
[
Address(id=2, user_id=8),
Address(id=3, user_id=8),
Address(id=4, user_id=8),
],
)
eq_(canary.mock_calls, [mock.call()])
fred = (s.query(User).options(
MyOption("ed")).filter_by(name="fred").one())
eq_(fred.addresses, []) # fred is missing
eq_(canary.mock_calls, [mock.call(), mock.call()])
# the lazy query was not cached; the option is re-applied to the
# Fred object due to populate_existing()
fred = (s.query(User).populate_existing().options(
MyOption("fred")).filter_by(name="fred").one())
eq_(fred.addresses, [Address(id=5, user_id=9)]) # fred is there
eq_(canary.mock_calls, [mock.call(), mock.call(), mock.call()])
def test_bindparam_quote_raise_on_expanding(self):
assert_raises_message(
exc.CompileError,
"Can't use expanding feature with parameter name 'uid' on "
"Oracle; it requires quoting which is not supported in this "
"context",
bindparam("uid", expanding=True).compile,
dialect=cx_oracle.dialect(),
)
def test_no_clobs_for_string_params(self):
"""test that simple string params get a DBAPI type of
VARCHAR, not CLOB. This is to prevent setinputsizes
from setting up cx_oracle.CLOBs on
string-based bind params [ticket:793]."""
class FakeDBAPI(object):
def __getattr__(self, attr):
return attr
dialect = oracle.OracleDialect()
dbapi = FakeDBAPI()
b = bindparam("foo", "hello world!")
eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING")
b = bindparam("foo", "hello world!")
eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING")
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_literal_binds(self):
t = text("select * from foo where lala=:bar and hoho=:whee")
t = t.bindparams(bindparam("bar", 4), whee="whee")
self.assert_compile(
t,
"select * from foo where lala=4 and hoho='whee'",
checkparams={},
literal_binds=True,
)
def test_count_with_bindparams(self):
User = self.classes.User
bq = self.bakery(lambda s: s.query(User))
sess = Session()
eq_(bq(sess).count(), 4)
bq += lambda q: q.filter(User.name == bindparam("uname"))
# calling with *args
eq_(bq(sess).params(uname="fred").count(), 1)
# with multiple params, the **kwargs will be used
bq += lambda q: q.filter(User.id == bindparam("anid"))
eq_(bq(sess).params(uname="fred", anid=9).count(), 1)
eq_(
# wrong id, so 0 results:
bq(sess).params(uname="fred", anid=8).count(),
0,
)
def test_exception_format_hide_parameters_nondbapi_round_trip(self):
foo = Table("foo", MetaData(), Column("data", String))
with self.no_param_engine.connect() as conn:
assert_raises_message(
tsa.exc.StatementError,
r"\(sqlalchemy_1_3.exc.InvalidRequestError\) A value is required "
r"for bind parameter 'the_data_2'\n"
r"\[SQL: SELECT foo.data \nFROM foo \nWHERE "
r"foo.data = \? OR foo.data = \?\]\n"
r"\[SQL parameters hidden due to hide_parameters=True\]",
conn.execute,
select([foo]).where(
or_(
foo.c.data == bindparam("the_data_1"),
foo.c.data == bindparam("the_data_2"),
)
),
{"the_data_1": "some data"},
)
def test_bind_param_truncated_named(self):
table1 = self.table1
bp = bindparam(_truncated_label("this_is_the_long_bindparam_name"))
stmt = table1.insert().values(this_is_the_data_column=bp)
compiled = stmt.compile(dialect=self._length_fixture(length=10))
eq_(
compiled.construct_params(
params={"this_is_the_long_bindparam_name": 5}
),
{"this_1": 5},
)
def test_compiled_insert(self):
table = Table(
"testtable",
self.metadata,
Column("id", Integer, primary_key=True),
Column("data", String(30)),
)
self.metadata.create_all()
ins = table.insert(inline=True, values={
"data": bindparam("x")
}).compile()
ins.execute({"x": "five"}, {"x": "seven"})
eq_(table.select().execute().fetchall(), [(1, "five"), (2, "seven")])
def test_out_params(self):
result = testing.db.execute(
text("begin foo(:x_in, :x_out, :y_out, "
":z_out); end;").bindparams(
bindparam("x_in", Float),
outparam("x_out", Integer),
outparam("y_out", Float),
outparam("z_out", String),
),
x_in=5,
)
eq_(result.out_parameters, {"x_out": 10, "y_out": 75, "z_out": None})
assert isinstance(result.out_parameters["x_out"], int)
def test_binds_that_match_columns(self):
"""test bind params named after column names
replace the normal SET/VALUES generation."""
t = table("foo", column("x"), column("y"))
u = t.update().where(t.c.x == bindparam("x"))
assert_raises(exc.CompileError, u.compile)
self.assert_compile(u, "UPDATE foo SET WHERE foo.x = :x", params={})
assert_raises(exc.CompileError, u.values(x=7).compile)
self.assert_compile(
u.values(y=7), "UPDATE foo SET y=:y WHERE foo.x = :x"
)
assert_raises(
exc.CompileError, u.values(x=7).compile, column_keys=["x", "y"]
)
assert_raises(exc.CompileError, u.compile, column_keys=["x", "y"])
self.assert_compile(
u.values(x=3 + bindparam("x")),
"UPDATE foo SET x=(:param_1 + :x) WHERE foo.x = :x",
)
self.assert_compile(
u.values(x=3 + bindparam("x")),
"UPDATE foo SET x=(:param_1 + :x) WHERE foo.x = :x",
params={"x": 1},
)
self.assert_compile(
u.values(x=3 + bindparam("x")),
"UPDATE foo SET x=(:param_1 + :x), y=:y WHERE foo.x = :x",
params={"x": 1, "y": 2},
)
def test_update_5(self):
table1 = self.tables.mytable
self.assert_compile(
update(
table1,
whereclause=table1.c.name == bindparam("crit"),
values={table1.c.name: "hi"},
),
"UPDATE mytable SET name=:name WHERE mytable.name = :crit",
params={"crit": "notthere"},
checkparams={"crit": "notthere", "name": "hi"},
)
def test_labels_no_collision(self):
t = table("foo", column("id"), column("foo_id"))
self.assert_compile(
t.update().where(t.c.id == 5),
"UPDATE foo SET id=:id, foo_id=:foo_id WHERE foo.id = :id_1",
)
self.assert_compile(
t.update().where(t.c.id == bindparam(key=t.c.id._label)),
"UPDATE foo SET id=:id, foo_id=:foo_id WHERE foo.id = :foo_id_1",
)
def test_update(self):
with self.engine.connect() as conn:
conn.execute(
self.tables.data.insert(),
[
{"x": "x1", "y": "y1"},
{"x": "x2", "y": "y2"},
{"x": "x3", "y": "y3"},
],
)
conn.execute(
self.tables.data.update()
.where(self.tables.data.c.x == bindparam("xval"))
.values(y=bindparam("yval")),
[{"xval": "x1", "yval": "y5"}, {"xval": "x3", "yval": "y6"}],
)
eq_(
conn.execute(
select([self.tables.data]).order_by(self.tables.data.c.id)
).fetchall(),
[(1, "x1", "y5", 5), (2, "x2", "y2", 5), (3, "x3", "y6", 5)],
)
