def test_iteration(self): metadata = MetaData() table1 = Table('table1', metadata, Column('col1', sa.Integer, primary_key=True), schema='someschema') table2 = Table('table2', metadata, Column('col1', sa.Integer, primary_key=True), Column('col2', sa.Integer, sa.ForeignKey('someschema.table1.col1')), schema='someschema') # ensure this doesnt crash print [t for t in metadata.sorted_tables] buf = StringIO.StringIO() def foo(s, p=None): buf.write(s) gen = sa.create_engine(testing.db.name + "://", strategy="mock", executor=foo) gen = gen.dialect.schemagenerator(gen.dialect, gen) gen.traverse(table1) gen.traverse(table2) buf = buf.getvalue() print buf if testing.db.dialect.preparer(testing.db.dialect).omit_schema: assert buf.index("CREATE TABLE table1") > -1 assert buf.index("CREATE TABLE table2") > -1 else: assert buf.index("CREATE TABLE someschema.table1") > -1 assert buf.index("CREATE TABLE someschema.table2") > -1
def test_compileonattr_rel_backref_b(self): m = MetaData() t1 = Table('t1', m, Column('id', Integer, primary_key=True), Column('x', Integer)) t2 = Table('t2', m, Column('id', Integer, primary_key=True), Column('t1_id', Integer, ForeignKey('t1.id'))) class Base(object): def __init__(self): pass class Base_AKW(object): def __init__(self, *args, **kwargs): pass for base in object, Base, Base_AKW: class A(base): pass class B(base): pass mapper(A, t1) mapper(B, t2, properties=dict(a=relation(A, backref='bs'))) a = A() b = B() b.a = a session = create_session() session.add(a) assert b in session, 'base: %s' % base
def define_tables(self, metadata): Table('table1', metadata, Column('id', Integer, primary_key=True), Column('data', String(30))) Table('table2', metadata, Column('id', Integer, primary_key=True), Column('someid', None, ForeignKey('table1.id')))
def test_index_reflection(self): m1 = MetaData(testing.db) t1 = Table('party', m1, Column('id', sa.Integer, nullable=False), Column('name', sa.String(20), index=True) ) i1 = sa.Index('idx1', t1.c.id, unique=True) i2 = sa.Index('idx2', t1.c.name, t1.c.id, unique=False) m1.create_all() try: m2 = MetaData(testing.db) t2 = Table('party', m2, autoload=True) print len(t2.indexes), t2.indexes assert len(t2.indexes) == 3 # Make sure indexes are in the order we expect them in tmp = [(idx.name, idx) for idx in t2.indexes] tmp.sort() r1, r2, r3 = [idx[1] for idx in tmp] assert r1.name == 'idx1' assert r2.name == 'idx2' assert r1.unique == True assert r2.unique == False assert r3.unique == False assert [t2.c.id] == r1.columns assert [t2.c.name, t2.c.id] == r2.columns assert [t2.c.name] == r3.columns finally: m1.drop_all()
def test_tokens(self): m = MetaData() bind = self.mock_engine() sane_alone = Table('t', m, Column('id', Integer)) sane_schema = Table('t', m, Column('id', Integer), schema='s') insane_alone = Table('t t', m, Column('id', Integer)) insane_schema = Table('t t', m, Column('id', Integer), schema='s s') ddl = DDL('%(schema)s-%(table)s-%(fullname)s') self.assertEquals(ddl._expand(sane_alone, bind), '-t-t') self.assertEquals(ddl._expand(sane_schema, bind), 's-t-s.t') self.assertEquals(ddl._expand(insane_alone, bind), '-"t t"-"t t"') self.assertEquals(ddl._expand(insane_schema, bind), '"s s"-"t t"-"s s"."t t"') # overrides are used piece-meal and verbatim. ddl = DDL('%(schema)s-%(table)s-%(fullname)s-%(bonus)s', context={ 'schema': 'S S', 'table': 'T T', 'bonus': 'b' }) self.assertEquals(ddl._expand(sane_alone, bind), 'S S-T T-t-b') self.assertEquals(ddl._expand(sane_schema, bind), 'S S-T T-s.t-b') self.assertEquals(ddl._expand(insane_alone, bind), 'S S-T T-"t t"-b') self.assertEquals(ddl._expand(insane_schema, bind), 'S S-T T-"s s"."t t"-b')
def define_tables(self, meta): Table('user_t', meta, Column('id', Integer, primary_key=True), Column('name', String(50))) Table('stuff', meta, Column('id', Integer, primary_key=True), Column('date', sa.Date), Column('user_id', Integer, ForeignKey('user_t.id')))
def define_tables(self, metadata): users = Table('users', metadata, Column('username', String(50), primary_key=True), Column('fullname', String(100))) addresses = Table( 'addresses', metadata, Column('email', String(50), primary_key=True), Column('username', String(50), ForeignKey('users.username', onupdate="cascade"))) items = Table('items', metadata, Column('itemname', String(50), primary_key=True), Column('description', String(100))) users_to_items = Table( 'users_to_items', metadata, Column('username', String(50), ForeignKey('users.username', onupdate='cascade'), primary_key=True), Column('itemname', String(50), ForeignKey('items.itemname', onupdate='cascade'), primary_key=True), )
def setUpAll(self): global cartitems, sometable, metadata metadata = MetaData(testing.db) cartitems = Table( "cartitems", metadata, Column("cart_id", Integer, Sequence('cart_id_seq'), primary_key=True), Column("description", String(40)), Column("createdate", sa.DateTime())) sometable = Table( 'Manager', metadata, Column( 'obj_id', Integer, Sequence('obj_id_seq'), ), Column('name', String(128)), Column('id', Integer, Sequence('Manager_id_seq', optional=True), primary_key=True), ) metadata.create_all()
def setUpAll(self): global metadata, users metadata = MetaData() users = Table('users', metadata, Column('user_id', sa.Integer, sa.Sequence('user_id_seq', optional=True), primary_key=True), Column('user_name', sa.String(40)), ) addresses = Table('email_addresses', metadata, Column('address_id', sa.Integer, sa.Sequence('address_id_seq', optional=True), primary_key = True), Column('user_id', sa.Integer, sa.ForeignKey(users.c.user_id)), Column('email_address', sa.String(40)), ) orders = Table('orders', metadata, Column('order_id', sa.Integer, sa.Sequence('order_id_seq', optional=True), primary_key = True), Column('user_id', sa.Integer, sa.ForeignKey(users.c.user_id)), Column('description', sa.String(50)), Column('isopen', sa.Integer), ) orderitems = Table('items', metadata, Column('item_id', sa.INT, sa.Sequence('items_id_seq', optional=True), primary_key = True), Column('order_id', sa.INT, sa.ForeignKey("orders")), Column('item_name', sa.VARCHAR(50)), )
def test_composite_fk(self): """test reflection of composite foreign keys""" meta = MetaData(testing.db) multi = Table( 'multi', meta, Column('multi_id', sa.Integer, primary_key=True), Column('multi_rev', sa.Integer, primary_key=True), Column('multi_hoho', sa.Integer, primary_key=True), Column('name', sa.String(50), nullable=False), Column('val', sa.String(100)), test_needs_fk=True, ) multi2 = Table('multi2', meta, Column('id', sa.Integer, primary_key=True), Column('foo', sa.Integer), Column('bar', sa.Integer), Column('lala', sa.Integer), Column('data', sa.String(50)), sa.ForeignKeyConstraint(['foo', 'bar', 'lala'], ['multi.multi_id', 'multi.multi_rev', 'multi.multi_hoho']), test_needs_fk=True, ) meta.create_all() try: meta2 = MetaData() table = Table('multi', meta2, autoload=True, autoload_with=testing.db) table2 = Table('multi2', meta2, autoload=True, autoload_with=testing.db) self.assert_tables_equal(multi, table) self.assert_tables_equal(multi2, table2) j = sa.join(table, table2) self.assert_(sa.and_(table.c.multi_id==table2.c.foo, table.c.multi_rev==table2.c.bar, table.c.multi_hoho==table2.c.lala).compare(j.onclause)) finally: meta.drop_all()
def test_include_columns(self): meta = MetaData(testing.db) foo = Table('foo', meta, *[Column(n, sa.String(30)) for n in ['a', 'b', 'c', 'd', 'e', 'f']]) meta.create_all() try: meta2 = MetaData(testing.db) foo = Table('foo', meta2, autoload=True, include_columns=['b', 'f', 'e']) # test that cols come back in original order self.assertEquals([c.name for c in foo.c], ['b', 'e', 'f']) for c in ('b', 'f', 'e'): assert c in foo.c for c in ('a', 'c', 'd'): assert c not in foo.c # test against a table which is already reflected meta3 = MetaData(testing.db) foo = Table('foo', meta3, autoload=True) foo = Table('foo', meta3, include_columns=['b', 'f', 'e'], useexisting=True) self.assertEquals([c.name for c in foo.c], ['b', 'e', 'f']) for c in ('b', 'f', 'e'): assert c in foo.c for c in ('a', 'c', 'd'): assert c not in foo.c finally: meta.drop_all()
def test_nonreflected_fk_raises(self): """test that a NoReferencedColumnError is raised when reflecting a table with an FK to another table which has not included the target column in its reflection. """ meta = MetaData(testing.db) a1 = Table('a', meta, Column('x', sa.Integer, primary_key=True), Column('z', sa.Integer), test_needs_fk=True ) b1 = Table('b', meta, Column('y', sa.Integer, sa.ForeignKey('a.x')), test_needs_fk=True ) meta.create_all() try: m2 = MetaData(testing.db) a2 = Table('a', m2, include_columns=['z'], autoload=True) b2 = Table('b', m2, autoload=True) self.assertRaises(tsa.exc.NoReferencedColumnError, a2.join, b2) finally: meta.drop_all()
def test_override_keys(self): """test that columns can be overridden with a 'key', and that ForeignKey targeting during reflection still works.""" meta = MetaData(testing.db) a1 = Table('a', meta, Column('x', sa.Integer, primary_key=True), Column('z', sa.Integer), test_needs_fk=True ) b1 = Table('b', meta, Column('y', sa.Integer, sa.ForeignKey('a.x')), test_needs_fk=True ) meta.create_all() try: m2 = MetaData(testing.db) a2 = Table('a', m2, Column('x', sa.Integer, primary_key=True, key='x1'), autoload=True) b2 = Table('b', m2, autoload=True) assert a2.join(b2).onclause.compare(a2.c.x1==b2.c.y) assert b2.c.y.references(a2.c.x1) finally: meta.drop_all()
def test_tometadata_with_schema(self): meta = MetaData() table = Table( 'mytable', meta, Column('myid', Integer, primary_key=True), Column('name', String(40), nullable=True), Column('description', String(30), CheckConstraint("description='hi'")), UniqueConstraint('name'), test_needs_fk=True, ) table2 = Table( 'othertable', meta, Column('id', Integer, primary_key=True), Column('myid', Integer, ForeignKey('mytable.myid')), test_needs_fk=True, ) meta2 = MetaData() table_c = table.tometadata(meta2, schema='someschema') table2_c = table2.tometadata(meta2, schema='someschema') eq_(str(table_c.join(table2_c).onclause), str(table_c.c.myid == table2_c.c.myid)) eq_(str(table_c.join(table2_c).onclause), "someschema.mytable.myid = someschema.othertable.myid")
def test_create_drop_bound(self): for meta in (MetaData, ThreadLocalMetaData): for bind in (testing.db, testing.db.connect()): metadata = meta() table = Table('test_table', metadata, Column('foo', Integer)) metadata.bind = bind assert metadata.bind is table.bind is bind metadata.create_all() assert table.exists() metadata.drop_all() table.create() table.drop() assert not table.exists() metadata = meta() table = Table('test_table', metadata, Column('foo', Integer)) metadata.connect(bind) assert metadata.bind is table.bind is bind metadata.create_all() assert table.exists() metadata.drop_all() table.create() table.drop() assert not table.exists() if isinstance(bind, engine.Connection): bind.close()
def test_compileonattr_rel_backref_a(self): m = MetaData() t1 = Table('t1', m, Column('id', Integer, primary_key=True), Column('x', Integer)) t2 = Table('t2', m, Column('id', Integer, primary_key=True), Column('t1_id', Integer, ForeignKey('t1.id'))) class Base(object): def __init__(self, *args, **kwargs): pass for base in object, Base: class A(base): pass class B(base): pass mapper(A, t1, properties=dict(bs=relation(B, backref='a'))) mapper(B, t2) b = B() assert b.a is None a = A() b.a = a session = create_session() session.add(b) assert a in session, "base is %s" % base
def define_tables(self, metadata): Table('table_a', metadata, Column('id', Integer, primary_key=True), Column('name', String(30))) Table('table_b', metadata, Column('id', Integer, primary_key=True), Column('name', String(30)), Column('a_id', Integer, ForeignKey('table_a.id')))
def define_tables(self, metadata): Table('users', metadata, Column('id', Integer, primary_key=True), Column('name', String(40)), Column('fullname', String(100)), Column('password', String(15))) Table('addresses', metadata, Column('id', Integer, primary_key=True), Column('email_address', String(100), nullable=False), Column('user_id', Integer, ForeignKey('users.id')))
def define_tables(self, metadata): Table('users', metadata, Column('id', Integer, primary_key=True), Column('name', String(30)), Column('type', String(30))) Table('email_users', metadata, Column('id', Integer, ForeignKey('users.id'), primary_key=True), Column('email_address', String(30)))
def test_with_inheritance(self): metadata = MetaData(testing.db) table1 = Table("mytable", metadata, Column('col1', Integer, primary_key=True), Column('col2', String(30))) table2 = Table( "mytable2", metadata, Column('col1', Integer, ForeignKey('mytable.col1'), primary_key=True), Column('col3', String(30)), ) @profile_memory def go(): class A(_base.ComparableEntity): pass class B(A): pass mapper(A, table1, polymorphic_on=table1.c.col2, polymorphic_identity='a') mapper(B, table2, inherits=A, polymorphic_identity='b') sess = create_session() a1 = A() a2 = A() b1 = B(col3='b1') b2 = B(col3='b2') for x in [a1, a2, b1, b2]: sess.add(x) sess.flush() sess.clear() alist = sess.query(A).order_by(A.col1).all() self.assertEquals([A(), A(), B(col3='b1'), B(col3='b2')], alist) for a in alist: sess.delete(a) sess.flush() # dont need to clear_mappers() del B del A metadata.create_all() try: go() finally: metadata.drop_all() assert_no_mappers()
def test_session(self): metadata = MetaData(testing.db) table1 = Table("mytable", metadata, Column('col1', Integer, primary_key=True), Column('col2', String(30))) table2 = Table("mytable2", metadata, Column('col1', Integer, primary_key=True), Column('col2', String(30)), Column('col3', Integer, ForeignKey("mytable.col1"))) metadata.create_all() m1 = mapper(A, table1, properties={ "bs": relation(B, cascade="all, delete", order_by=table2.c.col1) }, order_by=table1.c.col1) m2 = mapper(B, table2) m3 = mapper(A, table1, non_primary=True) @profile_memory def go(): sess = create_session() a1 = A(col2="a1") a2 = A(col2="a2") a3 = A(col2="a3") a1.bs.append(B(col2="b1")) a1.bs.append(B(col2="b2")) a3.bs.append(B(col2="b3")) for x in [a1, a2, a3]: sess.add(x) sess.flush() sess.clear() alist = sess.query(A).all() self.assertEquals([ A(col2="a1", bs=[B(col2="b1"), B(col2="b2")]), A(col2="a2", bs=[]), A(col2="a3", bs=[B(col2="b3")]) ], alist) for a in alist: sess.delete(a) sess.flush() go() metadata.drop_all() del m1, m2, m3 assert_no_mappers()
def test_pickle_via_reflect(): # this is the most common use case, pickling the results of a # database reflection meta2 = MetaData(bind=testing.db) t1 = Table('mytable', meta2, autoload=True) t2 = Table('othertable', meta2, autoload=True) meta3 = pickle.loads(pickle.dumps(meta2)) assert meta3.bind is None assert meta3.tables['mytable'] is not t1 return (meta3.tables['mytable'], meta3.tables['othertable'])
def define_tables(self, metadata): t2 = Table('t2', metadata, Column('nextid', Integer)) Table( 't1', metadata, Column('id', Integer, primary_key=True, default=sa.select([func.max(t2.c.nextid)]).as_scalar()), Column('data', String(30)))
def define_tables(self, metadata): Table('users', metadata, Column('id', Integer, primary_key=True), Column('username', String(50), unique=True), Column('fullname', String(100))) Table( 'addresses', metadata, Column('id', Integer, primary_key=True), Column('email', String(50)), Column('username', String(50), ForeignKey('users.username', onupdate="cascade")))
def define_tables(self, metadata): Table('items', metadata, Column('item_id', Integer, primary_key=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), Column('name', String(40)))
def define_tables(self, metadata): Table('users_table', metadata, Column('id', Integer, primary_key=True), Column('name', String(64))) Table('addresses_table', metadata, Column('id', Integer, primary_key=True), Column('user_id', Integer, ForeignKey('users_table.id')), Column('email_address', String(128)), Column('purpose', String(16)), Column('bounces', Integer, default=0))
def define_tables(self, metadata): Table('infos', metadata, Column('pk', Integer, primary_key=True), Column('info', String(128))) Table('data', metadata, Column('data_pk', Integer, primary_key=True), Column('info_pk', Integer, ForeignKey('infos.pk')), Column('timeval', Integer), Column('data_val', String(128))) Table('rels', metadata, Column('rel_pk', Integer, primary_key=True), Column('info_pk', Integer, ForeignKey('infos.pk')), Column('start', Integer), Column('finish', Integer))
def define_tables(self, metadata): Table('left', metadata, Column('id', Integer, ForeignKey('middle.id'), primary_key=True), Column('data', String(50), primary_key=True)) Table('middle', metadata, Column('id', Integer, primary_key=True), Column('data', String(50))) Table('right', metadata, Column('id', Integer, ForeignKey('middle.id'), primary_key=True), Column('data', String(50), primary_key=True))
def define_tables(self, metadata): Table('departments', metadata, Column('department_id', Integer, primary_key=True), Column('name', String(50))) Table( 'employees', metadata, Column('person_id', Integer, primary_key=True), Column('name', String(50)), Column('department_id', Integer, ForeignKey('departments.department_id')))
def test_append_constraint_unique(self): meta = MetaData() users = Table('users', meta, Column('id', sa.Integer)) addresses = Table('addresses', meta, Column('id', sa.Integer), Column('user_id', sa.Integer)) fk = sa.ForeignKeyConstraint(['user_id'],[users.c.id]) addresses.append_constraint(fk) addresses.append_constraint(fk) assert len(addresses.c.user_id.foreign_keys) == 1 assert addresses.constraints == set([addresses.primary_key, fk])