def test_create_index_schema(self): t = Table("t", MetaData(), Column("x", Integer), schema="foo") i = Index("xyz", t.c.x) self.assert_compile(schema.CreateIndex(i), "CREATE INDEX xyz ON foo.t (x)")
class B(Base): __tablename__ = "b" id = Column(Integer, primary_key=True) a_id = Column(ForeignKey("a.id")) cs = relationship("C")
def _table_fixture(self): m = MetaData() return (m, ) + tuple( Table("t%d" % i, m, Column("x", Integer)) for i in range(1, 6))
class B(ComparableEntity, Base): __tablename__ = "b" id = Column(Integer, primary_key=True)
class D(ComparableEntity, Base): __tablename__ = "d" id = Column(Integer, primary_key=True) c_id = Column(ForeignKey("c.id")) b_id = Column(ForeignKey("b.id"))
class B(Base): __tablename__ = "b" id = Column(Integer, primary_key=True) a_id = Column(ForeignKey(A.id)) a = relationship(A)
class A(Base): __tablename__ = "a" id = Column(Integer, primary_key=True) data = Column(String) bs = relationship("B")
def test_python_fn_default(self): metadata = MetaData() table = Table( "sometable", metadata, Column("id", Integer, primary_key=True), Column("data", String), Column("foo", Integer, default=lambda: 10), ) values = [ { "id": 1, "data": "data1" }, { "id": 2, "data": "data2", "foo": 15 }, { "id": 3, "data": "data3" }, ] checkparams = { "id_m0": 1, "id_m1": 2, "id_m2": 3, "data_m0": "data1", "data_m1": "data2", "data_m2": "data3", "foo": None, # evaluated later "foo_m1": 15, "foo_m2": None, # evaluated later } stmt = table.insert().values(values) eq_( dict([(k, v.type._type_affinity) for (k, v) in stmt.compile( dialect=postgresql.dialect()).binds.items()]), { "foo": Integer, "data_m2": String, "id_m0": Integer, "id_m2": Integer, "foo_m1": Integer, "data_m1": String, "id_m1": Integer, "foo_m2": Integer, "data_m0": String, }, ) self.assert_compile( stmt, "INSERT INTO sometable (id, data, foo) VALUES " "(%(id_m0)s, %(data_m0)s, %(foo)s), " "(%(id_m1)s, %(data_m1)s, %(foo_m1)s), " "(%(id_m2)s, %(data_m2)s, %(foo_m2)s)", checkparams=checkparams, dialect=postgresql.dialect(), )
def test_sql_functions(self): metadata = MetaData() table = Table( "sometable", metadata, Column("id", Integer, primary_key=True), Column("data", String), Column("foo", Integer), ) values = [ { "id": 1, "data": "foo", "foo": func.foob() }, { "id": 2, "data": "bar", "foo": func.foob() }, { "id": 3, "data": "bar", "foo": func.bar() }, { "id": 4, "data": "bar", "foo": 15 }, { "id": 5, "data": "bar", "foo": func.foob() }, ] checkparams = { "id_m0": 1, "data_m0": "foo", "id_m1": 2, "data_m1": "bar", "id_m2": 3, "data_m2": "bar", "id_m3": 4, "data_m3": "bar", "foo_m3": 15, "id_m4": 5, "data_m4": "bar", } self.assert_compile( table.insert().values(values), "INSERT INTO sometable (id, data, foo) VALUES " "(%(id_m0)s, %(data_m0)s, foob()), " "(%(id_m1)s, %(data_m1)s, foob()), " "(%(id_m2)s, %(data_m2)s, bar()), " "(%(id_m3)s, %(data_m3)s, %(foo_m3)s), " "(%(id_m4)s, %(data_m4)s, foob())", checkparams=checkparams, dialect=postgresql.dialect(), )
def define_tables(cls, metadata): global foo, bar, blub, bar_foo, blub_bar, blub_foo # the 'data' columns are to appease SQLite which cant handle a blank # INSERT foo = Table( "foo", metadata, Column( "id", Integer, Sequence("foo_seq", optional=True), primary_key=True, ), Column("data", String(20)), ) bar = Table( "bar", metadata, Column("id", Integer, ForeignKey("foo.id"), primary_key=True), Column("bar_data", String(20)), ) blub = Table( "blub", metadata, Column("id", Integer, ForeignKey("bar.id"), primary_key=True), Column("blub_data", String(20)), ) bar_foo = Table( "bar_foo", metadata, Column("bar_id", Integer, ForeignKey("bar.id")), Column("foo_id", Integer, ForeignKey("foo.id")), ) blub_bar = Table( "bar_blub", metadata, Column("blub_id", Integer, ForeignKey("blub.id")), Column("bar_id", Integer, ForeignKey("bar.id")), ) blub_foo = Table( "blub_foo", metadata, Column("blub_id", Integer, ForeignKey("blub.id")), Column("foo_id", Integer, ForeignKey("foo.id")), )
def _baseline_1_create_tables(self): zoo = 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), ) animal = 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() global Zoo, Animal class Zoo(object): def __init__(self, **kwargs): for k, v in kwargs.items(): setattr(self, k, v) class Animal(object): def __init__(self, **kwargs): for k, v in kwargs.items(): setattr(self, k, v) mapper(Zoo, zoo) mapper(Animal, animal)
def test_with_polymorphic(self): metadata = MetaData(testing.db) order = Table( "orders", metadata, Column("id", Integer, primary_key=True), Column( "employee_id", Integer, ForeignKey("employees.id"), nullable=False, ), Column("type", Unicode(16)), ) employee = Table( "employees", metadata, Column("id", Integer, primary_key=True), Column("name", Unicode(16), unique=True, nullable=False), ) product = Table("products", metadata, Column("id", Integer, primary_key=True)) orderproduct = Table( "orderproducts", metadata, Column("id", Integer, primary_key=True), Column("order_id", Integer, ForeignKey("orders.id"), nullable=False), Column( "product_id", Integer, ForeignKey("products.id"), nullable=False, ), ) class Order(object): pass class Employee(object): pass class Product(object): pass class OrderProduct(object): pass order_join = order.select().alias("pjoin") mapper( Order, order, with_polymorphic=("*", order_join), polymorphic_on=order_join.c.type, polymorphic_identity="order", properties={ "orderproducts": relationship(OrderProduct, lazy="select", backref="order") }, ) mapper( Product, product, properties={ "orderproducts": relationship(OrderProduct, lazy="select", backref="product") }, ) mapper( Employee, employee, properties={ "orders": relationship(Order, lazy="select", backref="employee") }, ) mapper(OrderProduct, orderproduct) # this requires that the compilation of order_mapper's "surrogate # mapper" occur after the initial setup of MapperProperty objects on # the mapper. configure_mappers()
def test_conflicting_backref_one(self): """test that conflicting backrefs raises an exception""" metadata = MetaData(testing.db) order = Table( "orders", metadata, Column("id", Integer, primary_key=True), Column("type", Unicode(16)), ) product = Table("products", metadata, Column("id", Integer, primary_key=True)) orderproduct = Table( "orderproducts", metadata, Column("id", Integer, primary_key=True), Column("order_id", Integer, ForeignKey("orders.id"), nullable=False), Column( "product_id", Integer, ForeignKey("products.id"), nullable=False, ), ) class Order(object): pass class Product(object): pass class OrderProduct(object): pass order_join = order.select().alias("pjoin") mapper( Order, order, with_polymorphic=("*", order_join), polymorphic_on=order_join.c.type, polymorphic_identity="order", properties={ "orderproducts": relationship(OrderProduct, lazy="select", backref="product") }, ) mapper( Product, product, properties={ "orderproducts": relationship(OrderProduct, lazy="select", backref="product") }, ) mapper(OrderProduct, orderproduct) assert_raises_message(sa_exc.ArgumentError, "Error creating backref", configure_mappers)
def test_drop_index_schema(self): t = Table("t", MetaData(), Column("x", Integer), schema="foo") i = Index("xyz", t.c.x) self.assert_compile(schema.DropIndex(i), "DROP INDEX foo.xyz")
def test_column_expr(self): c = Column("x", Integer) is_(inspect(c), c) assert not c.is_selectable assert not hasattr(c, "selectable")
class Book(Base): __tablename__ = "book" id = Column(Integer, primary_key=True) pages = relationship("Page")
def _xy_table_fixture(self): m = MetaData() t = Table("t", m, Column("x", Integer), Column("y", Integer)) return t
class A(Base): __tablename__ = "a" id = Column(Integer, primary_key=True) data = Column(String(30)) deferred_data = deferred(Column(String(30)))
def setup_class(cls): m = MetaData() cls.left = Table( "lft", m, Column("id", Integer, primary_key=True), Column("x", Integer), Column("y", Integer), ) cls.right = Table( "rgt", m, Column("id", Integer, primary_key=True), Column("lid", Integer, ForeignKey("lft.id")), Column("x", Integer), Column("y", Integer), ) cls.right_multi_fk = Table( "rgt_multi_fk", m, Column("id", Integer, primary_key=True), Column("lid1", Integer, ForeignKey("lft.id")), Column("lid2", Integer, ForeignKey("lft.id")), ) cls.selfref = Table( "selfref", m, Column("id", Integer, primary_key=True), Column("sid", Integer, ForeignKey("selfref.id")), ) cls.composite_selfref = Table( "composite_selfref", m, Column("id", Integer, primary_key=True), Column("group_id", Integer, primary_key=True), Column("parent_id", Integer), ForeignKeyConstraint( ["parent_id", "group_id"], ["composite_selfref.id", "composite_selfref.group_id"], ), ) cls.m2mleft = Table("m2mlft", m, Column("id", Integer, primary_key=True)) cls.m2mright = Table("m2mrgt", m, Column("id", Integer, primary_key=True)) cls.m2msecondary = Table( "m2msecondary", m, Column("lid", Integer, ForeignKey("m2mlft.id"), primary_key=True), Column("rid", Integer, ForeignKey("m2mrgt.id"), primary_key=True), ) cls.m2msecondary_no_fks = Table( "m2msecondary_no_fks", m, Column("lid", Integer, primary_key=True), Column("rid", Integer, primary_key=True), ) cls.m2msecondary_ambig_fks = Table( "m2msecondary_ambig_fks", m, Column("lid1", Integer, ForeignKey("m2mlft.id"), primary_key=True), Column("rid1", Integer, ForeignKey("m2mrgt.id"), primary_key=True), Column("lid2", Integer, ForeignKey("m2mlft.id"), primary_key=True), Column("rid2", Integer, ForeignKey("m2mrgt.id"), primary_key=True), ) cls.base_w_sub_rel = Table( "base_w_sub_rel", m, Column("id", Integer, primary_key=True), Column("sub_id", Integer, ForeignKey("rel_sub.id")), ) cls.rel_sub = Table( "rel_sub", m, Column( "id", Integer, ForeignKey("base_w_sub_rel.id"), primary_key=True, ), ) cls.base = Table( "base", m, Column("id", Integer, primary_key=True), Column("flag", Boolean), ) cls.sub = Table( "sub", m, Column("id", Integer, ForeignKey("base.id"), primary_key=True), ) cls.sub_w_base_rel = Table( "sub_w_base_rel", m, Column("id", Integer, ForeignKey("base.id"), primary_key=True), Column("base_id", Integer, ForeignKey("base.id")), ) cls.sub_w_sub_rel = Table( "sub_w_sub_rel", m, Column("id", Integer, ForeignKey("base.id"), primary_key=True), Column("sub_id", Integer, ForeignKey("sub.id")), ) cls.right_w_base_rel = Table( "right_w_base_rel", m, Column("id", Integer, primary_key=True), Column("base_id", Integer, ForeignKey("base.id")), ) cls.three_tab_a = Table("three_tab_a", m, Column("id", Integer, primary_key=True)) cls.three_tab_b = Table( "three_tab_b", m, Column("id", Integer, primary_key=True), Column("aid", Integer, ForeignKey("three_tab_a.id")), ) cls.three_tab_c = Table( "three_tab_c", m, Column("id", Integer, primary_key=True), Column("aid", Integer, ForeignKey("three_tab_a.id")), Column("bid", Integer, ForeignKey("three_tab_b.id")), ) cls.composite_target = Table( "composite_target", m, Column("uid", Integer, primary_key=True), Column("oid", Integer, primary_key=True), ) cls.composite_multi_ref = Table( "composite_multi_ref", m, Column("uid1", Integer), Column("uid2", Integer), Column("oid", Integer), ForeignKeyConstraint( ("uid1", "oid"), ("composite_target.uid", "composite_target.oid"), ), ForeignKeyConstraint( ("uid2", "oid"), ("composite_target.uid", "composite_target.oid"), ), ) cls.purely_single_col = Table("purely_single_col", m, Column("path", String))
class Book(Base): __tablename__ = "book" id = Column(Integer, primary_key=True) title = Column(String(50), nullable=False) pages = relationship("Page", backref="book")
def data(cls): time.sleep(0.001) return Column(String)
class Page(Base): __tablename__ = "page" id = Column(Integer, primary_key=True) book_id = Column(ForeignKey("book.id")) title = Column(String(50))
class C(ComparableEntity, Base): __tablename__ = "c" id = Column(Integer, primary_key=True) a_id = Column(ForeignKey("a.id"))
def test_update(self): """ Tests sending functions and SQL expressions to the VALUES and SET clauses of INSERT/UPDATE instances, and that column-level defaults get overridden. """ meta = self.metadata t = Table( "t1", meta, Column( "id", Integer, Sequence("t1idseq", optional=True), primary_key=True, ), Column("value", Integer), ) t2 = Table( "t2", meta, Column( "id", Integer, Sequence("t2idseq", optional=True), primary_key=True, ), Column("value", Integer, default=7), Column("stuff", String(20), onupdate="thisisstuff"), ) meta.create_all() t.insert(values=dict(value=func.length("one"))).execute() assert t.select().execute().first()["value"] == 3 t.update(values=dict(value=func.length("asfda"))).execute() assert t.select().execute().first()["value"] == 5 r = t.insert(values=dict(value=func.length("sfsaafsda"))).execute() id_ = r.inserted_primary_key[0] assert t.select(t.c.id == id_).execute().first()["value"] == 9 t.update(values={t.c.value: func.length("asdf")}).execute() assert t.select().execute().first()["value"] == 4 t2.insert().execute() t2.insert(values=dict(value=func.length("one"))).execute() t2.insert(values=dict(value=func.length("asfda") + -19)).execute( stuff="hi" ) res = exec_sorted(select([t2.c.value, t2.c.stuff])) eq_(res, [(-14, "hi"), (3, None), (7, None)]) t2.update(values=dict(value=func.length("asdsafasd"))).execute( stuff="some stuff" ) assert select([t2.c.value, t2.c.stuff]).execute().fetchall() == [ (9, "some stuff"), (9, "some stuff"), (9, "some stuff"), ] t2.delete().execute() t2.insert(values=dict(value=func.length("one") + 8)).execute() assert t2.select().execute().first()["value"] == 11 t2.update(values=dict(value=func.length("asfda"))).execute() eq_( select([t2.c.value, t2.c.stuff]).execute().first(), (5, "thisisstuff"), ) t2.update( values={t2.c.value: func.length("asfdaasdf"), t2.c.stuff: "foo"} ).execute() eq_(select([t2.c.value, t2.c.stuff]).execute().first(), (9, "foo"))
class A(Base): __tablename__ = "a" id = Column(Integer, primary_key=True)
class Foo(Base): __tablename__ = "foo" id = Column(Integer, primary_key=True)
class C(Base): __tablename__ = "c" id = Column(Integer, primary_key=True) b_id = Column(ForeignKey("b.id"))
def test_table(self): t = Table("t", MetaData(), Column("x", Integer)) is_(inspect(t), t) assert t.is_selectable is_(t.selectable, t)
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 test_cycle_unnamed_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")), ) 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)" ")"), ), AllOf( CompiledSQL("ALTER TABLE b ADD " "FOREIGN KEY(aid) REFERENCES a (id)"), CompiledSQL("ALTER TABLE a ADD " "FOREIGN KEY(bid) REFERENCES b (id)"), ), ] with self.sql_execution_asserter() as asserter: metadata.create_all(checkfirst=False) if testing.db.dialect.supports_alter: asserter.assert_(*assertions) 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, checkfirst=False, ) else: with expect_warnings( "Can't sort tables for DROP; an unresolvable " "foreign key dependency exists between tables"): with self.sql_execution_asserter() as asserter: metadata.drop_all(checkfirst=False) asserter.assert_( AllOf(CompiledSQL("DROP TABLE b"), CompiledSQL("DROP TABLE a")))