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