def test_multiple(self): m = MetaData() Table( "foo", m, Column("id", Integer, primary_key=True), Column("bar", Integer, primary_key=True), ) tb = Table( "some_table", m, Column("id", Integer, primary_key=True), Column("foo_id", Integer, ForeignKey("foo.id")), Column("foo_bar", Integer, ForeignKey("foo.bar")), ) self.assert_compile( schema.CreateTable(tb), "CREATE TABLE some_table (" "id INTEGER NOT NULL, " "foo_id INTEGER, " "foo_bar INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(foo_id) REFERENCES foo (id), " "FOREIGN KEY(foo_bar) REFERENCES foo (bar))", )
def test_use_alter(self): m = MetaData() Table("t", m, Column("a", Integer)) Table( "t2", m, Column("a", Integer, ForeignKey("t.a", use_alter=True, name="fk_ta")), Column("b", Integer, ForeignKey("t.a", name="fk_tb")), ) e = engines.mock_engine(dialect_name="postgresql") m.create_all(e) m.drop_all(e) e.assert_sql([ "CREATE TABLE t (a INTEGER)", "CREATE TABLE t2 (a INTEGER, b INTEGER, CONSTRAINT fk_tb " "FOREIGN KEY(b) REFERENCES t (a))", "ALTER TABLE t2 " "ADD CONSTRAINT fk_ta FOREIGN KEY(a) REFERENCES t (a)", "ALTER TABLE t2 DROP CONSTRAINT fk_ta", "DROP TABLE t2", "DROP TABLE t", ])
def define_tables(cls, metadata): Table( "users", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("name", String(50)), test_needs_fk=True, ) Table( "user_items", metadata, Column("user_id", ForeignKey("users.id"), primary_key=True), Column("item_id", ForeignKey("items.id"), primary_key=True), test_needs_fk=True, ) Table( "items", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("name", String(50)), test_needs_fk=True, )
def define_tables(cls, metadata): global foo, bar, foo_bar foo = Table( "foo", metadata, Column( "id", Integer, Sequence("foo_id_seq", optional=True), primary_key=True, ), Column("data", String(20)), ) bar = Table( "bar", metadata, Column("bid", Integer, ForeignKey("foo.id"), primary_key=True), ) foo_bar = Table( "foo_bar", metadata, Column("foo_id", Integer, ForeignKey("foo.id")), Column("bar_id", Integer, ForeignKey("bar.bid")), )
def define_tables(cls, metadata): Table( "datas", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("a", Integer, nullable=False), ) Table( "foo", metadata, Column("data_id", Integer, ForeignKey("datas.id"), primary_key=True), Column("bar", Integer), ) Table( "stats", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("data_id", Integer, ForeignKey("datas.id")), Column("somedata", Integer, nullable=False), )
def define_tables(cls, metadata): Table( "base", metadata, Column("uid", String(30), primary_key=True), Column("x", String(30)), ) Table( "derived", metadata, Column("uid", String(30), ForeignKey("base.uid"), primary_key=True), Column("y", String(30)), ) Table( "derivedII", metadata, Column("uid", String(30), ForeignKey("base.uid"), primary_key=True), Column("z", String(30)), ) Table( "comments", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("uid", String(30), ForeignKey("base.uid")), Column("comment", String(30)), )
def define_tables(cls, metadata): Table( "users", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("name", String(30), nullable=False), Column("some_update", String(30), onupdate="im the update"), ) Table( "addresses", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("user_id", None, ForeignKey("users.id")), Column("email_address", String(50), nullable=False), ) Table( "foobar", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("user_id", None, ForeignKey("users.id")), Column("data", String(30)), Column("some_update", String(30), onupdate="im the other update"), )
def _baseline_1_create_tables(self): Table( "Zoo", self.metadata, Column( "ID", Integer, Sequence("zoo_id_seq"), primary_key=True, index=True, ), Column("Name", Unicode(255)), Column("Founded", Date), Column("Opens", Time), Column("LastEscape", DateTime), Column("Admission", Float), ) Table( "Animal", self.metadata, Column("ID", Integer, Sequence("animal_id_seq"), primary_key=True), Column("ZooID", Integer, ForeignKey("Zoo.ID"), index=True), Column("Name", Unicode(100)), Column("Species", Unicode(100)), Column("Legs", Integer, default=4), Column("LastEscape", DateTime), Column("Lifespan", Float(4)), Column("MotherID", Integer, ForeignKey("Animal.ID")), Column("PreferredFoodID", Integer), Column("AlternateFoodID", Integer), ) self.metadata.create_all()
def define_tables(cls, metadata): Table( "items", metadata, Column( "item_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("name", String(40)), ) Table( "item_keywords", metadata, Column("item_id", Integer, ForeignKey("items.item_id")), Column("keyword_id", Integer, ForeignKey("keywords.keyword_id")), Column("data", String(40)), ) Table( "keywords", metadata, Column( "keyword_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("name", String(40)), )
def define_tables(cls, metadata): Table( "people", metadata, Column("people_id", Integer, primary_key=True), Column("age", Integer), Column("name", String(30)), ) Table( "bookcases", metadata, Column("bookcase_id", Integer, primary_key=True), Column( "bookcase_owner_id", Integer, ForeignKey("people.people_id") ), Column("bookcase_shelves", Integer), Column("bookcase_width", Integer), ) Table( "books", metadata, Column("book_id", Integer, primary_key=True), Column( "bookcase_id", Integer, ForeignKey("bookcases.bookcase_id") ), Column("book_owner_id", Integer, ForeignKey("people.people_id")), Column("book_weight", Integer), )
def define_tables(cls, metadata): Table( "left", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("data", String(30)), ) Table( "right", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("data", String(30)), ) Table( "secondary", metadata, Column("left_id", Integer, ForeignKey("left.id"), primary_key=True), Column("right_id", Integer, ForeignKey("right.id"), primary_key=True), )
def define_tables(cls, metadata): Table("a", metadata, Column("id", Integer, primary_key=True)) Table( "b", metadata, Column("id", Integer, primary_key=True), Column("aid", ForeignKey("a.id"), nullable=True), ) Table( "c", metadata, Column("id", Integer, primary_key=True), Column("aid", ForeignKey("a.id"), nullable=False), ) Table( "d", metadata, Column("id", Integer, primary_key=True), Column("aid", ForeignKey("a.id", ondelete="cascade"), nullable=False), ) Table( "e", metadata, Column("id", Integer, primary_key=True), Column("aid", ForeignKey("a.id", ondelete="set null"), nullable=True), )
def test_collection_setstate(self): """test a particular cycle that requires CollectionAdapter to not rely upon InstanceState to deserialize.""" m = MetaData() c1 = Table( "c1", m, Column("parent_id", String, ForeignKey("p.id"), primary_key=True), ) c2 = Table( "c2", m, Column("parent_id", String, ForeignKey("p.id"), primary_key=True), ) p = Table("p", m, Column("id", String, primary_key=True)) mapper( Parent, p, properties={ "children1": relationship(Child1), "children2": relationship(Child2), }, ) mapper(Child1, c1) mapper(Child2, c2) obj = Parent() screen1 = Screen(obj) screen1.errors = [obj.children1, obj.children2] screen2 = Screen(Child2(), screen1) pickle.loads(pickle.dumps(screen2))
def define_tables(cls, metadata): Table( "a", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("x", Integer), Column("y", Integer), ) Table( "b", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("a_id", ForeignKey("a.id")), Column("x", Integer), Column("y", Integer), ) Table( "c", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("b_id", ForeignKey("b.id")), Column("x", Integer), Column("y", Integer), )
def define_tables(cls, metadata): Table( "owners", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("data", String(30)), ) Table( "categories", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("name", String(20)), ) Table( "tests", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("owner_id", Integer, ForeignKey("owners.id"), nullable=False), Column( "category_id", Integer, ForeignKey("categories.id"), nullable=False, ), ) Table( "options", metadata, Column("test_id", Integer, ForeignKey("tests.id"), primary_key=True), Column("owner_id", Integer, ForeignKey("owners.id"), primary_key=True), Column( "someoption", sa.Boolean, server_default=sa.false(), nullable=False, ), )
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 define_tables(cls, metadata): global principals global users global groups global user_group_map principals = Table( "principals", metadata, Column( "principal_id", Integer, Sequence("principal_id_seq", optional=False), primary_key=True, ), Column("name", String(50), nullable=False), ) users = Table( "prin_users", metadata, Column( "principal_id", Integer, ForeignKey("principals.principal_id"), primary_key=True, ), Column("password", String(50), nullable=False), Column("email", String(50), nullable=False), Column("login_id", String(50), nullable=False), ) groups = Table( "prin_groups", metadata, Column( "principal_id", Integer, ForeignKey("principals.principal_id"), primary_key=True, ), ) user_group_map = Table( "prin_user_group_map", metadata, Column( "user_id", Integer, ForeignKey("prin_users.principal_id"), primary_key=True, ), Column( "group_id", Integer, ForeignKey("prin_groups.principal_id"), primary_key=True, ), )
def test_alias_pathing(self): metadata = MetaData(self.engine) a = Table( "a", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("bid", Integer, ForeignKey("b.id")), Column("type", String(30)), ) asub = Table( "asub", metadata, Column("id", Integer, ForeignKey("a.id"), primary_key=True), Column("data", String(30)), ) b = Table( "b", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), ) mapper(A, a, polymorphic_identity="a", polymorphic_on=a.c.type) mapper(ASub, asub, inherits=A, polymorphic_identity="asub") mapper(B, b, properties={"as_": relationship(A)}) metadata.create_all() sess = Session() a1 = ASub(data="a1") a2 = ASub(data="a2") a3 = ASub(data="a3") b1 = B(as_=[a1, a2, a3]) sess.add(b1) sess.commit() del sess # sqlite has a slow enough growth here # that we have to run it more times to see the # "dip" again @profile_memory(maxtimes=120) def go(): sess = Session() sess.query(B).options(subqueryload(B.as_.of_type(ASub))).all() sess.close() try: go() finally: metadata.drop_all() clear_mappers()
def define_tables(cls, metadata): Table( "design_types", metadata, Column( "design_type_id", Integer, primary_key=True, test_needs_autoincrement=True, ), ) Table( "design", metadata, Column( "design_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column( "design_type_id", Integer, ForeignKey("design_types.design_type_id"), ), ) Table( "parts", metadata, Column( "part_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("design_id", Integer, ForeignKey("design.design_id")), Column( "design_type_id", Integer, ForeignKey("design_types.design_type_id"), ), ) Table( "inherited_part", metadata, Column( "ip_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("part_id", Integer, ForeignKey("parts.part_id")), Column("design_id", Integer, ForeignKey("design.design_id")), )
class Job(ComparableEntity, Base): __tablename__ = "job" id = Column(Integer, primary_key=True, test_needs_autoincrement=True) type = Column(String(10)) widget_id = Column(ForeignKey("widget.id")) widget = relationship("Widget") container_id = Column(Integer, ForeignKey("data_container.id")) __mapper_args__ = {"polymorphic_on": type}
def define_tables(cls, metadata): Table( "companies", metadata, Column( "company_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("company_name", String(40)), ) Table( "addresses", metadata, Column( "address_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("company_id", Integer, ForeignKey("companies.company_id")), Column("address", String(40)), ) Table( "phone_numbers", metadata, Column( "phone_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("address_id", Integer, ForeignKey("addresses.address_id")), Column("type", String(20)), Column("number", String(10)), ) Table( "invoices", metadata, Column( "invoice_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("company_id", Integer, ForeignKey("companies.company_id")), Column("date", sa.DateTime), )
class Grunt(Employee): __tablename__ = "grunt" id = Column(Integer, ForeignKey("employee.id"), primary_key=True) savings = Column(Numeric) employer_id = Column(Integer, ForeignKey("boss.id")) employer = relationship("Boss", backref="employees", primaryjoin=Boss.id == employer_id) __mapper_args__ = {"polymorphic_identity": "grunt"}
def define_tables(cls, metadata): Table( "t1", metadata, Column("id", Integer, primary_key=True), Column("foo", Integer), ) Table( "t2", metadata, Column("id", Integer, ForeignKey("t1.id"), primary_key=True), Column("t1id", Integer, ForeignKey("t1.id")), )
def test_basic_reflection(self): meta = self.metadata users = Table( "engine_users", meta, Column("user_id", types.INT, primary_key=True), Column("user_name", types.VARCHAR(20), nullable=False), Column("test1", types.CHAR(5), nullable=False), Column("test2", types.Float(5), nullable=False), Column("test2.5", types.Float(), nullable=False), Column("test3", types.Text()), Column("test4", types.Numeric, nullable=False), Column("test4.5", types.Numeric(10, 2), nullable=False), Column("test5", types.DateTime), Column( "parent_user_id", types.Integer, ForeignKey("engine_users.user_id"), ), Column("test6", types.DateTime, nullable=False), Column("test7", types.Text()), Column("test8", types.LargeBinary()), Column("test_passivedefault2", types.Integer, server_default="5"), Column("test9", types.BINARY(100)), Column("test_numeric", types.Numeric()), ) addresses = Table( "engine_email_addresses", meta, Column("address_id", types.Integer, primary_key=True), Column("remote_user_id", types.Integer, ForeignKey(users.c.user_id)), Column("email_address", types.String(20)), ) meta.create_all() meta2 = MetaData() reflected_users = Table("engine_users", meta2, autoload=True, autoload_with=testing.db) reflected_addresses = Table( "engine_email_addresses", meta2, autoload=True, autoload_with=testing.db, ) self.assert_tables_equal(users, reflected_users) self.assert_tables_equal(addresses, reflected_addresses)
def define_tables(cls, metadata): Table( "people", metadata, Column( "person_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("name", String(50)), Column("type", String(30)), ) Table( "engineers", metadata, Column( "person_id", Integer, ForeignKey("people.person_id"), primary_key=True, ), Column("status", String(30)), Column("primary_language", String(50)), ) Table( "managers", metadata, Column( "person_id", Integer, ForeignKey("people.person_id"), primary_key=True, ), Column("status", String(30)), Column("manager_name", String(50)), ) Table( "boss", metadata, Column( "boss_id", Integer, ForeignKey("managers.person_id"), primary_key=True, ), Column("golf_swing", String(30)), )
def test_case_sensitive_reflection_dual_case_references(self): # this tests that within the fix we do for MySQL bug # 88718, we don't do case-insensitive logic if the backend # is case sensitive m = self.metadata Table( "t1", m, Column("some_id", Integer, primary_key=True), mysql_engine="InnoDB", ) Table( "T1", m, Column("Some_Id", Integer, primary_key=True), mysql_engine="InnoDB", ) Table( "t2", m, Column("id", Integer, primary_key=True), Column("t1id", ForeignKey("t1.some_id", name="t1id_fk")), Column("cap_t1id", ForeignKey("T1.Some_Id", name="cap_t1id_fk")), mysql_engine="InnoDB", ) m.create_all(testing.db) eq_( dict((rec["name"], rec) for rec in inspect(testing.db).get_foreign_keys("t2")), { "cap_t1id_fk": { "name": "cap_t1id_fk", "constrained_columns": ["cap_t1id"], "referred_schema": None, "referred_table": "T1", "referred_columns": ["Some_Id"], "options": {}, }, "t1id_fk": { "name": "t1id_fk", "constrained_columns": ["t1id"], "referred_schema": None, "referred_table": "t1", "referred_columns": ["some_id"], "options": {}, }, }, )
def define_tables(cls, metadata): Table( "mytable", metadata, Column("myid", Integer), Column("name", String(30)), Column("description", String(50)), ) Table( "myothertable", metadata, Column("otherid", Integer), Column("othername", String(30)), ) Table( "users", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("name", String(30), nullable=False), ) Table( "addresses", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("user_id", None, ForeignKey("users.id")), Column("name", String(30), nullable=False), Column("email_address", String(50), nullable=False), ) Table( "dingalings", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("address_id", None, ForeignKey("addresses.id")), Column("data", String(30)), ) Table( "update_w_default", metadata, Column("id", Integer, primary_key=True), Column("x", Integer), Column("ycol", Integer, key="y"), Column("data", String(30), onupdate=lambda: "hi"), )
def test_misc_one(self): metadata = MetaData(testing.db) node_table = Table( "node", metadata, Column("node_id", Integer, primary_key=True), Column("name_index", Integer, nullable=True), ) node_name_table = Table( "node_name", metadata, Column("node_name_id", Integer, primary_key=True), Column("node_id", Integer, ForeignKey("node.node_id")), Column("host_id", Integer, ForeignKey("host.host_id")), Column("name", String(64), nullable=False), ) host_table = Table( "host", metadata, Column("host_id", Integer, primary_key=True), Column("hostname", String(64), nullable=False, unique=True), ) metadata.create_all() try: node_table.insert().execute(node_id=1, node_index=5) class Node(object): pass class NodeName(object): pass class Host(object): pass mapper(Node, node_table) mapper(Host, host_table) mapper( NodeName, node_name_table, properties={ "node": relationship(Node, backref=backref("names")), "host": relationship(Host), }, ) sess = create_session() assert sess.query(Node).get(1).names == [] finally: metadata.drop_all()
def define_tables(cls, metadata): Table( "base", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), ) Table( "sub", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), ) Table( "base_mtom", metadata, Column("base_id", Integer, ForeignKey("base.id"), primary_key=True), Column( "related_id", Integer, ForeignKey("related.id"), primary_key=True, ), ) Table( "sub_mtom", metadata, Column("base_id", Integer, ForeignKey("sub.id"), primary_key=True), Column( "related_id", Integer, ForeignKey("related.id"), primary_key=True, ), ) Table( "related", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), )
def define_tables(cls, metadata): Table( "single", metadata, Column("id", Integer, primary_key=True), Column("type", String(10)), test_needs_fk=True, ) Table( "joined_base", metadata, Column("id", Integer, primary_key=True), Column("type", String(10)), test_needs_fk=True, ) Table( "joined_inh", metadata, Column("id", Integer, ForeignKey("joined_base.id"), primary_key=True), test_needs_fk=True, ) FixtureTest.define_tables(metadata)