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)], )