def test_person_selectin_subclasses(self): s = fixture_session() q = s.query(Person).options( selectin_polymorphic(Person, [Engineer, Manager])) result = self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT people.person_id AS people_person_id, " "people.company_id AS people_company_id, " "people.name AS people_name, " "people.type AS people_type FROM people", {}, ), AllOf( CompiledSQL( "SELECT engineers.person_id AS engineers_person_id, " "people.person_id AS people_person_id, " "people.type AS people_type, " "engineers.status AS engineers_status, " "engineers.engineer_name AS engineers_engineer_name, " "engineers.primary_language AS engineers_primary_language " "FROM people JOIN engineers " "ON people.person_id = engineers.person_id " "WHERE people.person_id IN ([POSTCOMPILE_primary_keys]) " "ORDER BY people.person_id", {"primary_keys": [1, 2, 5]}, ), CompiledSQL( "SELECT managers.person_id AS managers_person_id, " "people.person_id AS people_person_id, " "people.type AS people_type, " "managers.status AS managers_status, " "managers.manager_name AS managers_manager_name " "FROM people JOIN managers " "ON people.person_id = managers.person_id " "WHERE people.person_id IN ([POSTCOMPILE_primary_keys]) " "ORDER BY people.person_id", {"primary_keys": [3, 4]}, ), ), ) eq_(result, self.all_employees)
def test_execute_full_uncached(self, user_address_fixture): users, addresses = user_address_fixture def go(name): stmt = lambda_stmt( lambda: select(users.c.id).where(users.c.name == name) # noqa ) with testing.db.connect().execution_options( compiled_cache=None) as conn: conn.execute(stmt) with self.sql_execution_asserter(testing.db) as asserter: go("name1") go("name2") go("name1") go("name3") asserter.assert_( CompiledSQL( "SELECT users.id FROM users WHERE users.name = :name_1", lambda ctx: [{ "name_1": "name1" }], ), CompiledSQL( "SELECT users.id FROM users WHERE users.name = :name_1", lambda ctx: [{ "name_1": "name2" }], ), CompiledSQL( "SELECT users.id FROM users WHERE users.name = :name_1", lambda ctx: [{ "name_1": "name1" }], ), CompiledSQL( "SELECT users.id FROM users WHERE users.name = :name_1", lambda ctx: [{ "name_1": "name3" }], ), )
def _assert_all_selectin(self, q): result = self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT a.id AS a_id, a.adata AS a_adata, " "a.type AS a_type FROM a ORDER BY a.id", {} ), AllOf( EachOf( CompiledSQL( "SELECT asub.id AS asub_id, a.id AS a_id, a.type AS a_type, " "asub.asubdata AS asub_asubdata FROM a JOIN asub " "ON a.id = asub.id WHERE a.id IN ([EXPANDING_primary_keys]) " "ORDER BY a.id", {"primary_keys": [2]} ), CompiledSQL( "SELECT anon_1.a_id AS anon_1_a_id, c.id AS c_id, " "c.a_sub_id AS c_a_sub_id FROM (SELECT a.id AS a_id, a.adata " "AS a_adata, a.type AS a_type, asub.id AS asub_id, " "asub.asubdata AS asub_asubdata FROM a JOIN asub " "ON a.id = asub.id) AS anon_1 JOIN c " "ON anon_1.asub_id = c.a_sub_id " "WHERE anon_1.a_id IN ([EXPANDING_primary_keys]) " "ORDER BY anon_1.a_id", {"primary_keys": [2]} ), ), CompiledSQL( "SELECT a_1.id AS a_1_id, b.id AS b_id, b.a_id AS b_a_id " "FROM a AS a_1 JOIN b ON a_1.id = b.a_id " "WHERE a_1.id IN ([EXPANDING_primary_keys]) ORDER BY a_1.id", {"primary_keys": [1, 2]} ) ) ) self.assert_sql_execution( testing.db, lambda: self._run_query(result), )
def test_update_fetch_returning(self): User = self.classes.User sess = Session() john, jack, jill, jane = sess.query(User).order_by(User.id).all() with self.sql_execution_asserter() as asserter: sess.query(User).filter(User.age > 29).update( {"age": User.age - 10}, synchronize_session="fetch") # these are simple values, these are now evaluated even with # the "fetch" strategy, new in 1.4, so there is no expiry eq_([john.age, jack.age, jill.age, jane.age], [25, 37, 29, 27]) if testing.db.dialect.full_returning: asserter.assert_( CompiledSQL( "UPDATE users SET age_int=(users.age_int - %(age_int_1)s) " "WHERE users.age_int > %(age_int_2)s RETURNING users.id", [{ "age_int_1": 10, "age_int_2": 29 }], dialect="postgresql", ), ) else: asserter.assert_( CompiledSQL( "SELECT users.id FROM users " "WHERE users.age_int > :age_int_1", [{ "age_int_1": 29 }], ), CompiledSQL( "UPDATE users SET age_int=(users.age_int - :age_int_1) " "WHERE users.age_int > :age_int_2", [{ "age_int_1": 10, "age_int_2": 29 }], ), )
def _assert_all_selectin(self, q): result = self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT a.id AS a_id, a.adata AS a_adata, " "a.type AS a_type FROM a ORDER BY a.id", {}, ), AllOf( EachOf( CompiledSQL( "SELECT asub.id AS asub_id, a.id AS a_id, " "a.type AS a_type, " "asub.asubdata AS asub_asubdata FROM a JOIN asub " "ON a.id = asub.id " "WHERE a.id IN ([POSTCOMPILE_primary_keys]) " "ORDER BY a.id", {"primary_keys": [2]}, ), CompiledSQL( # note this links c.a_sub_id to a.id, even though # primaryjoin is to asub.id. this is because the # cols a.id / asub.id are listed in the mapper's # equivalent_columns so they are guaranteed to store # the same value. "SELECT c.a_sub_id AS c_a_sub_id, " "c.id AS c_id " "FROM c WHERE c.a_sub_id " "IN ([POSTCOMPILE_primary_keys]) ORDER BY c.a_sub_id", {"primary_keys": [2]}, ), ), CompiledSQL( "SELECT b.a_id AS b_a_id, b.id AS b_id FROM b " "WHERE b.a_id IN ([POSTCOMPILE_primary_keys]) " "ORDER BY b.a_id", {"primary_keys": [1, 2]}, ), ), ) self.assert_sql_execution(testing.db, lambda: self._run_query(result))
def test_all_subq_query(self): A, B, B2, C, C2, D = self.classes('A', 'B', 'B2', 'C', 'C2', 'D') session = Session(testing.db) b_b2 = with_polymorphic(B, [B2], flat=True) c_c2 = with_polymorphic(C, [C2], flat=True) q = session.query(A).options( subqueryload(A.bs.of_type(b_b2)).subqueryload( b_b2.cs.of_type(c_c2)).subqueryload(c_c2.ds)) self.assert_sql_execution( testing.db, q.all, CompiledSQL("SELECT t_a.id AS t_a_id FROM t_a", {}), CompiledSQL( "SELECT t_b_1.type AS t_b_1_type, t_b_1.id AS t_b_1_id, " "t_b_1.a_id AS t_b_1_a_id, t_b2_1.id AS t_b2_1_id, " "anon_1.t_a_id AS anon_1_t_a_id FROM " "(SELECT t_a.id AS t_a_id FROM t_a) AS anon_1 " "JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 " "ON t_b_1.id = t_b2_1.id) ON anon_1.t_a_id = t_b_1.a_id " "ORDER BY anon_1.t_a_id", {}), CompiledSQL( "SELECT t_c_1.type AS t_c_1_type, t_c_1.id AS t_c_1_id, " "t_c_1.b_id AS t_c_1_b_id, t_c2_1.id AS t_c2_1_id, " "t_b_1.id AS t_b_1_id FROM (SELECT t_a.id AS t_a_id FROM t_a) " "AS anon_1 JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 " "ON t_b_1.id = t_b2_1.id) ON anon_1.t_a_id = t_b_1.a_id " "JOIN (t_c AS t_c_1 LEFT OUTER JOIN t_c2 AS t_c2_1 ON " "t_c_1.id = t_c2_1.id) ON t_b_1.id = t_c_1.b_id " "ORDER BY t_b_1.id", {}), CompiledSQL( "SELECT t_d.id AS t_d_id, t_d.c_id AS t_d_c_id, " "t_c_1.id AS t_c_1_id " "FROM (SELECT t_a.id AS t_a_id FROM t_a) AS anon_1 " "JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 " "ON t_b_1.id = t_b2_1.id) " "ON anon_1.t_a_id = t_b_1.a_id " "JOIN (t_c AS t_c_1 LEFT OUTER JOIN t_c2 AS t_c2_1 " "ON t_c_1.id = t_c2_1.id) " "ON t_b_1.id = t_c_1.b_id " "JOIN t_d ON t_c_1.id = t_d.c_id ORDER BY t_c_1.id", {}))
def test_selectinload_local_criteria(self, user_address_fixture): User, Address = user_address_fixture s = Session(testing.db, future=True) stmt = ( select(User) .options( selectinload( User.addresses.and_(Address.email_address != "*****@*****.**") ), ) .order_by(User.id) ) with self.sql_execution_asserter() as asserter: result = s.execute(stmt) eq_( result.scalars().unique().all(), self._user_minus_edwood(*user_address_fixture), ) asserter.assert_( CompiledSQL( "SELECT users.id, users.name FROM users ORDER BY users.id" ), CompiledSQL( "SELECT addresses.user_id AS addresses_user_id, " "addresses.id AS addresses_id, addresses.email_address " "AS addresses_email_address FROM addresses " "WHERE addresses.user_id IN ([POSTCOMPILE_primary_keys]) " "AND addresses.email_address != :email_address_1 " "ORDER BY addresses.id", [ { "primary_keys": [7, 8, 9, 10], "email_address_1": "*****@*****.**", } ], ), )
def test_update_attrs(self): asserter = self._test_update(self.classes.PersonAttrs) asserter.assert_( CompiledSQL( "UPDATE people_attrs SET name=:name " "WHERE people_attrs.person_id = :people_attrs_person_id", [{ 'name': 'newname', 'people_attrs_person_id': 5 }]), )
def test_issue_6793(self): User = self.classes.User session = fixture_session() with self.sql_execution_asserter() as asserter: session.bulk_save_objects([User(name="A"), User(name="B")]) session.add(User(name="C")) session.add(User(name="D")) session.flush() asserter.assert_( Conditional( testing.db.dialect.insert_executemany_returning, [ CompiledSQL( "INSERT INTO users (name) VALUES (:name)", [{"name": "A"}, {"name": "B"}], ), CompiledSQL( "INSERT INTO users (name) VALUES (:name)", [{"name": "C"}, {"name": "D"}], ), ], [ CompiledSQL( "INSERT INTO users (name) VALUES (:name)", [{"name": "A"}, {"name": "B"}], ), CompiledSQL( "INSERT INTO users (name) VALUES (:name)", [{"name": "C"}], ), CompiledSQL( "INSERT INTO users (name) VALUES (:name)", [{"name": "D"}], ), ], ) )
def test_update_both(self): # want to make sure that before [ticket:3849], this did not have # a successful behavior or workaround asserter = self._test_update(self.classes.PersonBoth) asserter.assert_( CompiledSQL( "UPDATE people_both SET name=:name_key " "WHERE people_both.person_id = :people_both_person_id", [{"name_key": "newname", "people_both_person_id": 5}], ) )
def test_insert_both(self): asserter = self._test_insert(self.classes.PersonBoth) asserter.assert_( CompiledSQL( "INSERT INTO people_both (person_id, name) " "VALUES (:id_key, :name_key)", [{ "id_key": 5, "name_key": "thename" }], ))
def test_insert_keys(self): asserter = self._test_insert(self.classes.PersonKeys) asserter.assert_( CompiledSQL( "INSERT INTO people_keys (person_id, name) " "VALUES (:id, :personname)", [{ "id": 5, "personname": "thename" }], ))
def test_update_attrs(self): asserter = self._test_update(self.classes.PersonAttrs) asserter.assert_( CompiledSQL( "UPDATE people_attrs SET name=:name " "WHERE people_attrs.person_id = :people_attrs_person_id", [{ "name": "newname", "people_attrs_person_id": 5 }], ))
def test_insert_attrs(self): asserter = self._test_insert(self.classes.PersonAttrs) asserter.assert_( CompiledSQL( "INSERT INTO people_attrs (person_id, name) " "VALUES (:person_id, :name)", [{ "person_id": 5, "name": "thename" }], ))
def test_drop_ordering(self): with self.sql_execution_asserter(testing.db) as asserter: self.metadata.drop_all(checkfirst=False) asserter.assert_( AllOf( CompiledSQL("DROP TABLE t_seq_test_2", {}), EachOf( CompiledSQL("DROP TABLE t_seq_test", {}), CompiledSQL( "DROP SEQUENCE t_seq", # dropped as part of t_seq_test {}, ), ), ), CompiledSQL( "DROP SEQUENCE t_seq_2", # dropped as part of metadata level {}, ), )
def test_pk_fk_constraint_create(self): metadata = self.metadata Table( "employees", metadata, Column("id", Integer), Column("soc", String(40)), Column("name", String(30)), PrimaryKeyConstraint("id", "soc"), ) Table( "elements", metadata, Column("id", Integer), Column("stuff", String(30)), Column("emp_id", Integer), Column("emp_soc", String(40)), PrimaryKeyConstraint("id", name="elements_primkey"), ForeignKeyConstraint(["emp_id", "emp_soc"], ["employees.id", "employees.soc"]), ) self.assert_sql_execution( testing.db, lambda: metadata.create_all(testing.db, checkfirst=False), CompiledSQL("CREATE TABLE employees (" "id INTEGER NOT NULL, " "soc VARCHAR(40) NOT NULL, " "name VARCHAR(30), " "PRIMARY KEY (id, soc)" ")"), CompiledSQL("CREATE TABLE elements (" "id INTEGER NOT NULL, " "stuff VARCHAR(30), " "emp_id INTEGER, " "emp_soc VARCHAR(40), " "CONSTRAINT elements_primkey PRIMARY KEY (id), " "FOREIGN KEY(emp_id, emp_soc) " "REFERENCES employees (id, soc)" ")"), )
def test_index_create(self): metadata = self.metadata employees = Table( "employees", metadata, Column("id", Integer, primary_key=True), Column("first_name", String(30)), Column("last_name", String(30)), Column("email_address", String(30)), ) i = Index( "employee_name_index", employees.c.last_name, employees.c.first_name, ) assert i in employees.indexes i2 = Index("employee_email_index", employees.c.email_address, unique=True) assert i2 in employees.indexes self.assert_sql_execution( testing.db, lambda: metadata.create_all(testing.db, checkfirst=False), RegexSQL("^CREATE TABLE"), AllOf( CompiledSQL( "CREATE INDEX employee_name_index ON " "employees (last_name, first_name)", [], ), CompiledSQL( "CREATE UNIQUE INDEX employee_email_index ON " "employees (email_address)", [], ), ), )
def test_delete_fetch_returning(self): User = self.classes.User sess = Session() john, jack, jill, jane = sess.query(User).order_by(User.id).all() in_(john, sess) in_(jack, sess) with self.sql_execution_asserter() as asserter: sess.query(User).filter(User.age > 29).delete( synchronize_session="fetch" ) if testing.db.dialect.full_returning: asserter.assert_( CompiledSQL( "DELETE FROM users WHERE users.age_int > %(age_int_1)s " "RETURNING users.id", [{"age_int_1": 29}], dialect="postgresql", ), ) else: asserter.assert_( CompiledSQL( "SELECT users.id FROM users " "WHERE users.age_int > :age_int_1", [{"age_int_1": 29}], ), CompiledSQL( "DELETE FROM users WHERE users.age_int > :age_int_1", [{"age_int_1": 29}], ), ) in_(john, sess) not_in_(jack, sess) in_(jill, sess) not_in_(jane, sess)
def test_update_keys(self, type_): if type_ == "states": asserter = self._test_update_states(self.classes.PersonKeys) else: asserter = self._test_update(self.classes.PersonKeys) asserter.assert_( CompiledSQL( "UPDATE people_keys SET name=:personname " "WHERE people_keys.person_id = :people_keys_person_id", [{"personname": "newname", "people_keys_person_id": 5}], ) )
def test_subqueryload_local_criteria(self, user_address_fixture): User, Address = user_address_fixture s = Session(testing.db, future=True) stmt = ( select(User) .options( subqueryload( User.addresses.and_(Address.email_address != "*****@*****.**") ), ) .order_by(User.id) ) with self.sql_execution_asserter() as asserter: result = s.execute(stmt) eq_( result.scalars().unique().all(), self._user_minus_edwood(*user_address_fixture), ) asserter.assert_( CompiledSQL( "SELECT users.id, users.name FROM users ORDER BY users.id" ), CompiledSQL( "SELECT addresses.id AS addresses_id, addresses.user_id " "AS addresses_user_id, addresses.email_address " "AS addresses_email_address, anon_1.users_id " "AS anon_1_users_id FROM (SELECT users.id AS users_id " "FROM users) AS anon_1 JOIN addresses ON anon_1.users_id = " "addresses.user_id AND " "addresses.email_address != :email_address_1 " "ORDER BY addresses.id", [{"email_address_1": "*****@*****.**"}], ), )
def test_noload_remove(self): # test that a load of User.addresses is not emitted # when flushing a remove User, Address = self._user_address_fixture() sess = Session() u1 = User(name="jack", addresses=[Address(email_address="a1")]) a2 = Address(email_address='a2') u1.addresses.append(a2) sess.add(u1) sess.commit() u1_id = u1.id a2_id = a2.id sess.expire_all() u1.addresses.remove(a2) self.assert_sql_execution( testing.db, sess.flush, CompiledSQL( "SELECT addresses.id AS addresses_id, addresses.email_address " "AS addresses_email_address FROM addresses " "WHERE addresses.id = :param_1", lambda ctx: [{ 'param_1': a2_id }]), CompiledSQL( "UPDATE addresses SET user_id=:user_id WHERE addresses.id = " ":addresses_id", lambda ctx: [{ 'addresses_id': a2_id, 'user_id': None }]), CompiledSQL( "SELECT users.id AS users_id, users.name AS users_name " "FROM users WHERE users.id = :param_1", lambda ctx: [{ "param_1": u1_id }]), )
def _assert_cyclic_constraint(self, metadata): assertions = [ CompiledSQL('CREATE TABLE b (' 'id INTEGER NOT NULL, ' 'aid INTEGER, ' 'PRIMARY KEY (id)' ')'), CompiledSQL('CREATE TABLE a (' 'id INTEGER NOT NULL, ' 'bid INTEGER, ' 'PRIMARY KEY (id), ' 'FOREIGN KEY(bid) REFERENCES b (id)' ')'), ] if testing.db.dialect.supports_alter: assertions.append( CompiledSQL('ALTER TABLE b ADD CONSTRAINT bfk ' 'FOREIGN KEY(aid) REFERENCES a (id)')) self.assert_sql_execution( testing.db, lambda: metadata.create_all(checkfirst=False), *assertions) assertions = [] if testing.db.dialect.supports_alter: assertions.append(CompiledSQL('ALTER TABLE b DROP CONSTRAINT bfk')) assertions.extend([ CompiledSQL("DROP TABLE a"), CompiledSQL("DROP TABLE b"), ]) self.assert_sql_execution(testing.db, lambda: metadata.drop_all(checkfirst=False), *assertions)
def test_index_functional_create(self): metadata = self.metadata t = Table('sometable', metadata, Column('id', Integer, primary_key=True), Column('data', String(50))) Index('myindex', t.c.data.desc()) self.assert_sql_execution( testing.db, lambda: t.create(testing.db), CompiledSQL('CREATE TABLE sometable (id INTEGER NOT NULL, ' 'data VARCHAR(50), PRIMARY KEY (id))'), ExactSQL('CREATE INDEX myindex ON sometable (data DESC)'))
def test_pk_fk_constraint_create(self): metadata = self.metadata Table('employees', metadata, Column('id', Integer), Column('soc', String(40)), Column('name', String(30)), PrimaryKeyConstraint('id', 'soc') ) Table('elements', metadata, Column('id', Integer), Column('stuff', String(30)), Column('emp_id', Integer), Column('emp_soc', String(40)), PrimaryKeyConstraint('id', name='elements_primkey'), ForeignKeyConstraint(['emp_id', 'emp_soc'], ['employees.id', 'employees.soc']) ) self.assert_sql_execution( testing.db, lambda: metadata.create_all(checkfirst=False), CompiledSQL('CREATE TABLE employees (' 'id INTEGER NOT NULL, ' 'soc VARCHAR(40) NOT NULL, ' 'name VARCHAR(30), ' 'PRIMARY KEY (id, soc)' ')' ), CompiledSQL('CREATE TABLE elements (' 'id INTEGER NOT NULL, ' 'stuff VARCHAR(30), ' 'emp_id INTEGER, ' 'emp_soc VARCHAR(40), ' 'CONSTRAINT elements_primkey PRIMARY KEY (id), ' 'FOREIGN KEY(emp_id, emp_soc) ' 'REFERENCES employees (id, soc)' ')' ) )
def test_execute_constructed_cached(self, user_address_fixture): users, addresses = user_address_fixture cache = {} def go(name): stmt = select([lambda: users.c.id]).where( lambda: users.c.name == name ) with testing.db.connect().execution_options( compiled_cache=cache ) as conn: conn.execute(stmt) with self.sql_execution_asserter(testing.db) as asserter: go("name1") go("name2") go("name1") go("name3") asserter.assert_( CompiledSQL( "SELECT users.id FROM users WHERE users.name = :name_1", lambda ctx: [{"name_1": "name1"}], ), CompiledSQL( "SELECT users.id FROM users WHERE users.name = :name_1", lambda ctx: [{"name_1": "name2"}], ), CompiledSQL( "SELECT users.id FROM users WHERE users.name = :name_1", lambda ctx: [{"name_1": "name1"}], ), CompiledSQL( "SELECT users.id FROM users WHERE users.name = :name_1", lambda ctx: [{"name_1": "name3"}], ), )
def test_index_create_inline(self): # test an index create using index=True, unique=True metadata = self.metadata events = Table('events', metadata, Column('id', Integer, primary_key=True), Column('name', String(30), index=True, unique=True), Column('location', String(30), index=True), Column('sport', String(30)), Column('announcer', String(30)), Column('winner', String(30))) Index('sport_announcer', events.c.sport, events.c.announcer, unique=True) Index('idx_winners', events.c.winner) eq_( set(ix.name for ix in events.indexes), set([ 'ix_events_name', 'ix_events_location', 'sport_announcer', 'idx_winners' ])) self.assert_sql_execution( testing.db, lambda: events.create(testing.db), RegexSQL("^CREATE TABLE events"), AllOf( CompiledSQL('CREATE UNIQUE INDEX ix_events_name ON events ' '(name)'), CompiledSQL('CREATE INDEX ix_events_location ON events ' '(location)'), CompiledSQL('CREATE UNIQUE INDEX sport_announcer ON events ' '(sport, announcer)'), CompiledSQL('CREATE INDEX idx_winners ON events (winner)'), ))
def test_check_constraint_create(self): metadata = self.metadata Table( "foo", metadata, Column("id", Integer, primary_key=True), Column("x", Integer), Column("y", Integer), CheckConstraint("x>y"), ) Table( "bar", metadata, Column("id", Integer, primary_key=True), Column("x", Integer, CheckConstraint("x>7")), Column("z", Integer), ) self.assert_sql_execution( testing.db, lambda: metadata.create_all(testing.db, checkfirst=False), AllOf( CompiledSQL("CREATE TABLE foo (" "id INTEGER NOT NULL, " "x INTEGER, " "y INTEGER, " "PRIMARY KEY (id), " "CHECK (x>y)" ")"), CompiledSQL("CREATE TABLE bar (" "id INTEGER NOT NULL, " "x INTEGER CHECK (x>7), " "z INTEGER, " "PRIMARY KEY (id)" ")"), ), )
def test_fk_cant_drop_cycled_unnamed(self): metadata = MetaData() Table( "a", metadata, Column("id", Integer, primary_key=True), Column("bid", Integer), ForeignKeyConstraint(["bid"], ["b.id"]), ) Table( "b", metadata, Column("id", Integer, primary_key=True), Column("aid", Integer), ForeignKeyConstraint(["aid"], ["a.id"]), ) metadata.create_all(testing.db) if testing.db.dialect.supports_alter: assert_raises_message( exc.CircularDependencyError, "Can't sort tables for DROP; an unresolvable foreign key " "dependency exists between tables: a, b. Please ensure " "that the ForeignKey and ForeignKeyConstraint objects " "involved in the cycle have names so that they can be " "dropped using DROP CONSTRAINT.", metadata.drop_all, testing.db, ) else: with expect_warnings("Can't sort tables for DROP; an unresolvable " "foreign key dependency "): with self.sql_execution_asserter() as asserter: metadata.drop_all(testing.db, checkfirst=False) asserter.assert_( AllOf(CompiledSQL("DROP TABLE a"), CompiledSQL("DROP TABLE b")))
def no_opt(): q = session.query(Parent).options( joinedload(Parent.children.of_type(ChildSubclass1))) return self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT parent.id AS parent_id, " "anon_1.child_id AS anon_1_child_id, " "anon_1.child_parent_id AS anon_1_child_parent_id, " "anon_1.child_type AS anon_1_child_type, " "anon_1.child_subclass1_id AS anon_1_child_subclass1_id " "FROM parent " "LEFT OUTER JOIN (SELECT child.id AS child_id, " "child.parent_id AS child_parent_id, " "child.type AS child_type, " "child_subclass1.id AS child_subclass1_id " "FROM child " "LEFT OUTER JOIN child_subclass1 " "ON child.id = child_subclass1.id) AS anon_1 " "ON parent.id = anon_1.child_parent_id", {}, ), CompiledSQL( "SELECT child_subclass1.id AS child_subclass1_id, " "child.id AS child_id, " "child.parent_id AS child_parent_id, " "child.type AS child_type " "FROM child JOIN child_subclass1 " "ON child.id = child_subclass1.id " "WHERE child.id IN ([POSTCOMPILE_primary_keys]) " "ORDER BY child.id", [{ "primary_keys": [1] }], ), )
def test_unique_constraint_create(self): metadata = self.metadata Table( "foo", metadata, Column("id", Integer, primary_key=True), Column("value", String(30), unique=True), ) Table( "bar", metadata, Column("id", Integer, primary_key=True), Column("value", String(30)), Column("value2", String(30)), UniqueConstraint("value", "value2", name="uix1"), ) self.assert_sql_execution( testing.db, lambda: metadata.create_all(testing.db, checkfirst=False), AllOf( CompiledSQL("CREATE TABLE foo (" "id INTEGER NOT NULL, " "value VARCHAR(30), " "PRIMARY KEY (id), " "UNIQUE (value)" ")"), CompiledSQL("CREATE TABLE bar (" "id INTEGER NOT NULL, " "value VARCHAR(30), " "value2 VARCHAR(30), " "PRIMARY KEY (id), " "CONSTRAINT uix1 UNIQUE (value, value2)" ")"), ), )