def test_bulk_save_return_defaults(self): (User, ) = self.classes("User") s = Session() objects = [User(name="u1"), User(name="u2"), User(name="u3")] assert "id" not in objects[0].__dict__ with self.sql_execution_asserter() as asserter: s.bulk_save_objects(objects, return_defaults=True) asserter.assert_( CompiledSQL("INSERT INTO users (name) VALUES (:name)", [{ "name": "u1" }]), CompiledSQL("INSERT INTO users (name) VALUES (:name)", [{ "name": "u2" }]), CompiledSQL("INSERT INTO users (name) VALUES (:name)", [{ "name": "u3" }]), ) eq_(objects[0].__dict__["id"], 1)
def test_noload_append(self): # test that a load of User.addresses is not emitted # when flushing an append User, Address = self._user_address_fixture() sess = Session() u1 = User(name="jack", addresses=[Address(email_address="a1")]) sess.add(u1) sess.commit() u1_id = u1.id sess.expire_all() u1.addresses.append(Address(email_address="a2")) self.assert_sql_execution( testing.db, sess.flush, 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 }], ), CompiledSQL( "INSERT INTO addresses (user_id, email_address) " "VALUES (:user_id, :email_address)", lambda ctx: [{ "email_address": "a2", "user_id": u1_id }], ), )
def test_threelevel_selectin_to_inline_mapped(self): self._fixture_from_geometry( { "a": { "subclasses": { "b": {"polymorphic_load": "selectin"}, "c": { "subclasses": { "d": { "polymorphic_load": "inline", "single": True, }, "e": { "polymorphic_load": "inline", "single": True, }, }, "polymorphic_load": "selectin", }, } } } ) a, b, c, d, e = self.classes("a", "b", "c", "d", "e") sess = Session() sess.add_all([d(d_data="d1"), e(e_data="e1")]) sess.commit() q = sess.query(a) result = self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT a.type AS a_type, a.id AS a_id, " "a.a_data AS a_a_data FROM a", {}, ), Or( CompiledSQL( "SELECT a.type AS a_type, c.id AS c_id, a.id AS a_id, " "c.c_data AS c_c_data, c.e_data AS c_e_data, " "c.d_data AS c_d_data " "FROM a JOIN c ON a.id = c.id " "WHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id", [{"primary_keys": [1, 2]}], ), CompiledSQL( "SELECT a.type AS a_type, c.id AS c_id, a.id AS a_id, " "c.c_data AS c_c_data, " "c.d_data AS c_d_data, c.e_data AS c_e_data " "FROM a JOIN c ON a.id = c.id " "WHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id", [{"primary_keys": [1, 2]}], ), ), ) with self.assert_statement_count(testing.db, 0): eq_(result, [d(d_data="d1"), e(e_data="e1")])
def test_selectinload(self): A, B = self.classes("A", "B") sess = Session() with self.sql_execution_asserter() as asserter: # note this is many-to-one. use_get is unconditionally turned # off for relationship to aliased class for now. a1 = sess.query(A).options(selectinload(A.b)).first() eq_(a1.b, B(id=1)) asserter.assert_( CompiledSQL( "SELECT a.id AS a_id, a.b_id AS a_b_id " "FROM a LIMIT :param_1", [{ "param_1": 1 }], ), CompiledSQL( "SELECT a_1.id AS a_1_id, b.id AS b_id FROM a AS a_1 " "JOIN (b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) " "ON a_1.b_id = b.id WHERE a_1.id " "IN ([EXPANDING_primary_keys])", [{ "primary_keys": [1] }], ), )
def test_index_create_camelcase(self): """test that mixed-case index identifiers are legal""" metadata = self.metadata employees = Table( "companyEmployees", metadata, Column("id", Integer, primary_key=True), Column("firstName", String(30)), Column("lastName", String(30)), Column("emailAddress", String(30)), ) Index("employeeNameIndex", employees.c.lastName, employees.c.firstName) Index("employeeEmailIndex", employees.c.emailAddress, unique=True) self.assert_sql_execution( testing.db, lambda: metadata.create_all(checkfirst=False), RegexSQL("^CREATE TABLE"), AllOf( CompiledSQL( 'CREATE INDEX "employeeNameIndex" ON ' '"companyEmployees" ("lastName", "firstName")', [], ), CompiledSQL( 'CREATE UNIQUE INDEX "employeeEmailIndex" ON ' '"companyEmployees" ("emailAddress")', [], ), ), )
def _assert_cyclic_constraint_no_alter(self, metadata, auto=False, sqlite_warning=False): table_assertions = [] if auto: table_assertions.append( DialectSQL("CREATE TABLE b (" "id INTEGER NOT NULL, " "aid INTEGER, " "PRIMARY KEY (id), " "CONSTRAINT bfk FOREIGN KEY(aid) REFERENCES a (id)" ")")) table_assertions.append( DialectSQL("CREATE TABLE a (" "id INTEGER NOT NULL, " "bid INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(bid) REFERENCES b (id)" ")")) else: table_assertions.append( DialectSQL("CREATE TABLE b (" "id INTEGER NOT NULL, " "aid INTEGER, " "PRIMARY KEY (id), " "CONSTRAINT bfk FOREIGN KEY(aid) REFERENCES a (id)" ")")) table_assertions.append( DialectSQL("CREATE TABLE a (" "id INTEGER NOT NULL, " "bid INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(bid) REFERENCES b (id)" ")")) assertions = [AllOf(*table_assertions)] with self.sql_execution_asserter() as asserter: metadata.create_all(checkfirst=False) asserter.assert_(*assertions) assertions = [ AllOf(CompiledSQL("DROP TABLE a"), CompiledSQL("DROP TABLE b")) ] if sqlite_warning: with expect_warnings("Can't sort tables for DROP; "): with self.sql_execution_asserter() as asserter: metadata.drop_all(checkfirst=False), else: with self.sql_execution_asserter() as asserter: metadata.drop_all(checkfirst=False), asserter.assert_(*assertions)
def test_cycle_named_fks(self): metadata = MetaData(testing.db) Table( "a", metadata, Column("id", Integer, primary_key=True), Column("bid", Integer, ForeignKey("b.id")), ) Table( "b", metadata, Column("id", Integer, primary_key=True), Column( "aid", Integer, ForeignKey("a.id", use_alter=True, name="aidfk"), ), ) assertions = [ AllOf( 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)" ")"), ), CompiledSQL("ALTER TABLE b ADD CONSTRAINT aidfk " "FOREIGN KEY(aid) REFERENCES a (id)"), ] with self.sql_execution_asserter() as asserter: metadata.create_all(checkfirst=False) if testing.db.dialect.supports_alter: asserter.assert_(*assertions) with self.sql_execution_asserter() as asserter: metadata.drop_all(checkfirst=False) asserter.assert_( CompiledSQL("ALTER TABLE b DROP CONSTRAINT aidfk"), AllOf(CompiledSQL("DROP TABLE b"), CompiledSQL("DROP TABLE a")), ) else: with self.sql_execution_asserter() as asserter: metadata.drop_all(checkfirst=False) asserter.assert_( AllOf(CompiledSQL("DROP TABLE b"), CompiledSQL("DROP TABLE a")))
def _assert_cyclic_constraint_supports_alter(self, metadata, auto=False): table_assertions = [] if auto: table_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)" ")"), ] else: table_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)" ")"), ] assertions = [AllOf(*table_assertions)] fk_assertions = [] fk_assertions.append( CompiledSQL("ALTER TABLE b ADD CONSTRAINT bfk " "FOREIGN KEY(aid) REFERENCES a (id)")) if auto: fk_assertions.append( CompiledSQL("ALTER TABLE a ADD " "FOREIGN KEY(bid) REFERENCES b (id)")) assertions.append(AllOf(*fk_assertions)) with self.sql_execution_asserter() as asserter: metadata.create_all(checkfirst=False) asserter.assert_(*assertions) assertions = [ CompiledSQL("ALTER TABLE b DROP CONSTRAINT bfk"), CompiledSQL("DROP TABLE a"), CompiledSQL("DROP TABLE b"), ] with self.sql_execution_asserter() as asserter: metadata.drop_all(checkfirst=False), asserter.assert_(*assertions)
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", {}, ), 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", {}, ), 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", {}, ), )
def test_bulk_save_updated_include_unchanged(self): (User, ) = self.classes("User") s = Session(expire_on_commit=False) objects = [User(name="u1"), User(name="u2"), User(name="u3")] s.add_all(objects) s.commit() objects[0].name = "u1new" objects[2].name = "u3new" s = Session() with self.sql_execution_asserter() as asserter: s.bulk_save_objects(objects, update_changed_only=False) asserter.assert_( CompiledSQL( "UPDATE users SET name=:name WHERE " "users.id = :users_id", [ { "users_id": 1, "name": "u1new" }, { "users_id": 2, "name": "u2" }, { "users_id": 3, "name": "u3new" }, ], ))
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))"), CompiledSQL("CREATE INDEX myindex ON sometable (data DESC)"), )
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_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 test_person_selectin_subclasses(self): s = 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 ([EXPANDING_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 ([EXPANDING_primary_keys]) " "ORDER BY people.person_id", {"primary_keys": [3, 4]}, ), ), ) eq_(result, self.all_employees)
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( # 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 ([EXPANDING_primary_keys])", {"primary_keys": [2]}, ), ), CompiledSQL( "SELECT b.a_id AS b_a_id, b.id AS b_id FROM b " "WHERE b.a_id IN ([EXPANDING_primary_keys])", {"primary_keys": [1, 2]}, ), ), ) self.assert_sql_execution(testing.db, lambda: self._run_query(result))
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_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_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_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_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_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(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_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_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_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 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(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 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 ([EXPANDING_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(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)" ")"), ), )
def test_bulk_update(self): (User, ) = self.classes("User") s = Session(expire_on_commit=False) objects = [User(name="u1"), User(name="u2"), User(name="u3")] s.add_all(objects) s.commit() s = Session() with self.sql_execution_asserter() as asserter: s.bulk_update_mappings( User, [ { "id": 1, "name": "u1new" }, { "id": 2, "name": "u2" }, { "id": 3, "name": "u3new" }, ], ) asserter.assert_( CompiledSQL( "UPDATE users SET name=:name WHERE users.id = :users_id", [ { "users_id": 1, "name": "u1new" }, { "users_id": 2, "name": "u2" }, { "users_id": 3, "name": "u3new" }, ], ))
def test_useget_cancels_eager_propagated_present(self): """test that a one to many lazyload cancels the unnecessary eager many-to-one join on the other side, even when a propagated option is present.""" User = self.classes.User Address = self.classes.Address mapper(User, self.tables.users) mapper( Address, self.tables.addresses, properties={ "user": relationship( User, lazy="joined", backref=backref("addresses", lazy="baked_select"), ) }, ) from sqlalchemy_1_3.orm.interfaces import MapperOption class MyBogusOption(MapperOption): propagate_to_loaders = True sess = Session() u1 = (sess.query(User).options( MyBogusOption()).filter(User.id == 8).one()) def go(): eq_(u1.addresses[0].user, u1) self.assert_sql_execution( testing.db, go, CompiledSQL( "SELECT addresses.id AS addresses_id, addresses.user_id AS " "addresses_user_id, addresses.email_address AS " "addresses_email_address FROM addresses WHERE :param_1 = " "addresses.user_id", {"param_1": 8}, ), )
def test_lazyload(self): A, B = self.classes("A", "B") sess = Session() a1 = sess.query(A).first() with self.sql_execution_asserter() as asserter: # note this is many-to-one. use_get is unconditionally turned # off for relationship to aliased class for now. eq_(a1.b, B(id=1)) asserter.assert_( CompiledSQL( "SELECT b.id AS b_id FROM b JOIN d ON d.b_id = b.id " "JOIN c ON c.id = d.c_id WHERE :param_1 = b.id", [{ "param_1": 1 }], ))