def test_update_ordereddict(self): table1 = self.tables.mytable # Confirm that ordered dicts are treated as normal dicts, # columns sorted in table order values = util.OrderedDict( ( (table1.c.name, table1.c.name + "lala"), (table1.c.myid, func.do_stuff(table1.c.myid, literal("hoho"))), ) ) self.assert_compile( update( table1, (table1.c.myid == func.hoho(4)) & ( table1.c.name == literal("foo") + table1.c.name + literal("lala") ), values=values, ), "UPDATE mytable " "SET " "myid=do_stuff(mytable.myid, :param_1), " "name=(mytable.name || :name_1) " "WHERE " "mytable.myid = hoho(:hoho_1) AND " "mytable.name = :param_2 || mytable.name || :param_3", )
def test_update_11(self): table1 = self.tables.mytable values = { table1.c.name: table1.c.name + "lala", table1.c.myid: func.do_stuff(table1.c.myid, literal("hoho")), } self.assert_compile( update( table1, (table1.c.myid == func.hoho(4)) & ( table1.c.name == literal("foo") + table1.c.name + literal("lala") ), values=values, ), "UPDATE mytable " "SET " "myid=do_stuff(mytable.myid, :param_1), " "name=(mytable.name || :name_1) " "WHERE " "mytable.myid = hoho(:hoho_1) AND " "mytable.name = :param_2 || mytable.name || :param_3", )
def test_update_ordered_parameters_2(self): table1 = self.tables.mytable # Confirm that we can pass values as list value pairs # note these are ordered *differently* from table.c values = [ (table1.c.name, table1.c.name + "lala"), ("description", "some desc"), (table1.c.myid, func.do_stuff(table1.c.myid, literal("hoho"))), ] self.assert_compile( update( table1, (table1.c.myid == func.hoho(4)) & ( table1.c.name == literal("foo") + table1.c.name + literal("lala") ), preserve_parameter_order=True, ).values(values), "UPDATE mytable " "SET " "name=(mytable.name || :name_1), " "description=:description, " "myid=do_stuff(mytable.myid, :param_1) " "WHERE " "mytable.myid = hoho(:hoho_1) AND " "mytable.name = :param_2 || mytable.name || :param_3", )
def test_match_compile_kw(self): expr = literal("x").match(literal("y")) self.assert_compile( expr, "MATCH ('x') AGAINST ('y' IN BOOLEAN MODE)", literal_binds=True, )
def test_literal_binds_plain(self): m = MetaData() t = Table( "t", m, Column("x", Integer, server_default=literal("a") + literal("b")), ) self.assert_compile(CreateTable(t), "CREATE TABLE t (x INTEGER DEFAULT 'a' || 'b')")
def test_seven(self): self._test( literal(datetime.timedelta(seconds=10)) - literal(datetime.timedelta(seconds=10)), "all", overrides={ "hour": 0, "minute": 0, "month": 0, "year": 0, "day": 0, "epoch": 0, }, )
def __clause_element__(self): # helper method for SQLAlchemy to interpret # the Amount object as a SQL element if isinstance(self.amount, (float, int, Decimal)): return literal(self.amount) else: return self.amount
def test_string_text_explicit_literal_binds(self): # the literal experssion here coerces the right side to # Unicode on Python 3 for plain string, test with unicode # string just to confirm literal is doing this self.assert_compile( column("x", String()) == literal(util.u("foo")), "x = N'foo'", literal_binds=True, )
def test_assorted(self): table1 = table("mytable", column("myid", Integer)) table2 = table("myothertable", column("otherid", Integer)) # test an expression with a function self.assert_compile( func.lala(3, 4, literal("five"), table1.c.myid) * table2.c.otherid, "lala(:lala_1, :lala_2, :param_1, mytable.myid) * " "myothertable.otherid", ) # test it in a SELECT self.assert_compile( select([func.count(table1.c.myid)]), "SELECT count(mytable.myid) AS count_1 FROM mytable", ) # test a "dotted" function name self.assert_compile( select([func.foo.bar.lala(table1.c.myid)]), "SELECT foo.bar.lala(mytable.myid) AS lala_1 FROM mytable", ) # test the bind parameter name with a "dotted" function name is # only the name (limits the length of the bind param name) self.assert_compile( select([func.foo.bar.lala(12)]), "SELECT foo.bar.lala(:lala_2) AS lala_1", ) # test a dotted func off the engine itself self.assert_compile(func.lala.hoho(7), "lala.hoho(:hoho_1)") # test None becomes NULL self.assert_compile( func.my_func(1, 2, None, 3), "my_func(:my_func_1, :my_func_2, NULL, :my_func_3)", ) # test pickling self.assert_compile( util.pickle.loads(util.pickle.dumps(func.my_func(1, 2, None, 3))), "my_func(:my_func_1, :my_func_2, NULL, :my_func_3)", ) # assert func raises AttributeError for __bases__ attribute, since # its not a class fixes pydoc try: func.__bases__ assert False except AttributeError: assert True
def test_strict_binds(self): """test the 'strict' compiler binds.""" from sqlalchemy_1_3.dialects.mssql.base import MSSQLStrictCompiler mxodbc_dialect = mxodbc.dialect() mxodbc_dialect.statement_compiler = MSSQLStrictCompiler t = table("sometable", column("foo")) for expr, compiled in [ ( select([literal("x"), literal("y")]), "SELECT 'x' AS anon_1, 'y' AS anon_2", ), ( select([t]).where(t.c.foo.in_(["x", "y", "z"])), "SELECT sometable.foo FROM sometable WHERE sometable.foo " "IN ('x', 'y', 'z')", ), (t.c.foo.in_([None]), "sometable.foo IN (NULL)"), ]: self.assert_compile(expr, compiled, dialect=mxodbc_dialect)
def test_percent_signs_literal_binds(self): stmt = select([literal("percent % signs %%")]) self.assert_compile( stmt, "SELECT 'percent % signs %%' AS anon_1", dialect="sqlite", literal_binds=True, ) self.assert_compile( stmt, "SELECT 'percent %% signs %%%%' AS anon_1", dialect="mysql", literal_binds=True, )
def test_quoted_name_bindparam_ok(self): from sqlalchemy_1_3.sql.elements import quoted_name with testing.db.connect() as conn: eq_( conn.scalar( select( [ cast( literal(quoted_name("some_name", False)), String, ) ] ) ), "some_name", )
def test_concat_compile_kw(self): expr = literal("x", type_=String) + literal("y", type_=String) self.assert_compile(expr, "concat('x', 'y')", literal_binds=True)
def test_insert_values_col_expression(self): with testing.db.connect() as conn: conn.execute(cattable.insert().values({cattable.c.id: literal(5)})) eq_(conn.scalar(select([cattable.c.id])), 5)
def test_literal_binds_w_quotes(self): m = MetaData() t = Table("t", m, Column("x", Integer, server_default=literal("5 ' 8"))) self.assert_compile(CreateTable(t), """CREATE TABLE t (x INTEGER DEFAULT '5 '' 8')""")
def test_literal_default_no_label(self): self._run_test(default=literal("INT_1", type_=self.MyInteger))