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(cls, metadata): global person_table, employee_table, Person, Employee person_table = Table( "persons", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("name", String(80)), ) employee_table = Table( "employees", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("salary", Integer), Column("person_id", Integer, ForeignKey("persons.id")), ) class Person(object): def __init__(self, name): self.name = name class Employee(Person): pass
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') eq_(ddl._expand(sane_alone, bind), '-t-t') eq_(ddl._expand(sane_schema, bind), 's-t-s.t') eq_(ddl._expand(insane_alone, bind), '-"t t"-"t t"') eq_(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' }) eq_(ddl._expand(sane_alone, bind), 'S S-T T-t-b') eq_(ddl._expand(sane_schema, bind), 'S S-T T-s.t-b') eq_(ddl._expand(insane_alone, bind), 'S S-T T-"t t"-b') eq_(ddl._expand(insane_schema, bind), 'S S-T T-"s s"."t t"-b')
def setup_class(cls): 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 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) assert_raises(tsa.exc.NoReferencedColumnError, a2.join, b2) finally: meta.drop_all()
def define_tables(cls, metadata): if testing.against('oracle'): fk_args = dict(deferrable=True, initially='deferred') else: fk_args = dict(onupdate='cascade') users = Table('users', metadata, Column('username', String(50), primary_key=True), Column('fullname', String(100)), test_needs_fk=True) addresses = Table('addresses', metadata, Column('email', String(50), primary_key=True), Column('username', String(50), ForeignKey('users.username', **fk_args)), test_needs_fk=True) items = Table('items', metadata, Column('itemname', String(50), primary_key=True), Column('description', String(100)), test_needs_fk=True) users_to_items = Table('users_to_items', metadata, Column('username', String(50), ForeignKey('users.username', **fk_args), primary_key=True), Column('itemname', String(50), ForeignKey('items.itemname', **fk_args), primary_key=True), test_needs_fk=True)
def test_tometadata_strip_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=None) table2_c = table2.tometadata(meta2, schema=None) 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), 'mytable.myid = othertable.myid')
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_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 eq_([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) eq_([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_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 define_tables(cls, metadata): Table( 'parent', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('name', String(50), nullable=False), Column('child_id', Integer, ForeignKey('child.id', use_alter=True, name='c1'), nullable=True)) Table( 'child', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('name', String(50), nullable=False), Column('child_id', Integer, ForeignKey('child.id')), Column('parent_id', Integer, ForeignKey('parent.id'), nullable=True))
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(cls, metadata): if testing.against('oracle'): fk_args = dict(deferrable=True, initially='deferred') else: fk_args = dict(onupdate='cascade') Table('users', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('username', String(50), unique=True), Column('fullname', String(100)), test_needs_fk=True) Table('addresses', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('email', String(50)), Column('username', String(50), ForeignKey('users.username', **fk_args)), test_needs_fk=True)
def define_tables(cls, metadata): Table('Table1', metadata, Column('id', Integer, primary_key=True)) Table('Table2', metadata, Column('t1id', Integer, ForeignKey("Table1.id"), primary_key=True), Column('num', Integer, primary_key=True))
def define_tables(cls, metadata): Table('Table1', metadata, Column('ID', Integer, primary_key=True)) Table('Table2', metadata, Column('T1ID', Integer, ForeignKey("Table1.ID"), primary_key=True), Column('NUM', Integer, primary_key=True))
def define_tables(cls, metadata): Table('parent', metadata, Column('id', Integer, primary_key=True) ) Table('child', metadata, Column('id', Integer, ForeignKey('parent.id'), primary_key=True) )
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( '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')))
def define_tables(cls, metadata): if testing.against('oracle'): fk_args = dict(deferrable=True, initially='deferred') else: fk_args = dict(onupdate='cascade') Table('person', metadata, Column('name', String(50), primary_key=True), Column('type', String(50), nullable=False), test_needs_fk=True) Table('engineer', metadata, Column('name', String(50), ForeignKey('person.name', **fk_args), primary_key=True), Column('primary_language', String(50)), Column('boss_name', String(50), ForeignKey('manager.name', **fk_args)), test_needs_fk=True) Table('manager', metadata, Column('name', String(50), ForeignKey('person.name', **fk_args), primary_key=True), Column('paperwork', String(50)), test_needs_fk=True)
def define_tables(cls, metadata): users = Table('users', metadata, Column('username', String(50), primary_key=True), Column('fullname', String(100)), test_needs_fk=True) addresses = Table('addresses', metadata, Column('email', String(50), primary_key=True), Column( 'username', String(50), ForeignKey('users.username', onupdate="cascade")), test_needs_fk=True) items = Table('items', metadata, Column('itemname', String(50), primary_key=True), Column('description', String(100)), test_needs_fk=True) 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), test_needs_fk=True)
def test_direct_quoting(self): m = MetaData(testing.db) t = Table("weird_casing", m, autoload=True) self.assert_compile( t.select(), 'SELECT weird_casing.col1, weird_casing."Col2", weird_casing."col3" FROM weird_casing' )
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 define_tables(cls, metadata): Table( 'addresses', metadata, Column('address_id', Integer, primary_key=True), Column('street', String(30)), ) Table( 'homes', metadata, Column('home_id', Integer, primary_key=True, key="id"), Column('description', String(30)), Column('address_id', Integer, ForeignKey('addresses.address_id'), nullable=False), ) Table( 'businesses', metadata, Column('business_id', Integer, primary_key=True, key="id"), Column('description', String(30), key="description"), Column('address_id', Integer, ForeignKey('addresses.address_id'), nullable=False), )
def define_tables(cls, metadata): Table( 'descriptions', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('d1', String(20)), Column('d2', String(20)), ) Table( 'values', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('description_id', Integer, ForeignKey('descriptions.id'), nullable=False), Column('v1', String(20)), Column('v2', String(20)), )
def test_passive_override(self): """ Primarily for postgres, tests that when we get a primary key column back from reflecting a table which has a default value on it, we pre-execute that DefaultClause upon insert, even though DefaultClause says "let the database execute this", because in postgres we must have all the primary key values in memory before insert; otherwise we can't locate the just inserted row. """ # TODO: move this to dialect/postgres try: meta = MetaData(testing.db) testing.db.execute(""" CREATE TABLE speedy_users ( speedy_user_id SERIAL PRIMARY KEY, user_name VARCHAR NOT NULL, user_password VARCHAR NOT NULL ); """, None) t = Table("speedy_users", meta, autoload=True) t.insert().execute(user_name='user', user_password='******') l = t.select().execute().fetchall() eq_(l, [(1, 'user', 'lala')]) finally: testing.db.execute("drop table speedy_users", None)
def setup(self): meta = MetaData(testing.db) global table, GoofyType class GoofyType(TypeDecorator): impl = String def process_bind_param(self, value, dialect): if value is None: return None return "FOO" + value def process_result_value(self, value, dialect): if value is None: return None return value + "BAR" table = Table( 'tables', meta, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('persons', Integer), Column('full', Boolean), Column('goofy', GoofyType(50))) table.create(checkfirst=True)
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 define_tables(cls, metadata): Table('t1', metadata, Column('c1', Integer, primary_key=True, test_needs_autoincrement=True), Column('c2', Integer, ForeignKey('t2.c1')), test_needs_autoincrement=True) Table('t2', metadata, Column('c1', Integer, primary_key=True, test_needs_autoincrement=True), Column('c2', Integer, ForeignKey('t1.c1', use_alter=True, name='t1c1_fq')), test_needs_autoincrement=True) Table('t1_data', metadata, Column('c1', Integer, primary_key=True, test_needs_autoincrement=True), Column('t1id', Integer, ForeignKey('t1.c1')), Column('data', String(20)), test_needs_autoincrement=True)
def define_tables(cls, metadata): Table( 'a_table', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('some_dest_id', Integer, ForeignKey('dest_table.id')), Column('aname', String(50))) Table( 'b_table', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('some_dest_id', Integer, ForeignKey('dest_table.id')), Column('bname', String(50))) Table( 'c_table', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('some_dest_id', Integer, ForeignKey('dest_table.id')), Column('cname', String(50))) Table( 'dest_table', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('name', String(50)))
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 test_unknown_types(self): meta = MetaData(testing.db) t = Table("test", meta, Column('foo', sa.DateTime)) import sys dialect_module = sys.modules[testing.db.dialect.__module__] # we're relying on the presence of "ischema_names" in the # dialect module, else we can't test this. we need to be able # to get the dialect to not be aware of some type so we temporarily # monkeypatch. not sure what a better way for this could be, # except for an established dialect hook or dialect-specific tests if not hasattr(dialect_module, 'ischema_names'): return ischema_names = dialect_module.ischema_names t.create() dialect_module.ischema_names = {} try: m2 = MetaData(testing.db) assert_raises(tsa.exc.SAWarning, Table, "test", m2, autoload=True) @testing.emits_warning('Did not recognize type') def warns(): m3 = MetaData(testing.db) t3 = Table("test", m3, autoload=True) assert t3.c.foo.type.__class__ == sa.types.NullType finally: dialect_module.ischema_names = ischema_names t.drop()
def define_tables(cls, metadata): global t1, t2, t3, t4 t1 = Table( 't1', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('data', String(30))) t2 = Table( 't2', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('t1id', Integer, ForeignKey('t1.id')), Column('type', String(30)), Column('data', String(30))) t3 = Table( 't3', metadata, Column('id', Integer, ForeignKey('t2.id'), primary_key=True), Column('moredata', String(30))) t4 = Table( 't4', metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('t3id', Integer, ForeignKey('t3.id')), Column('data', 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.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_( [ 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_mapper_reset(self): metadata = MetaData(testing.db) table1 = Table( "mytable", metadata, Column('col1', Integer, primary_key=True, test_needs_autoincrement=True), Column('col2', String(30))) table2 = Table( "mytable2", metadata, Column('col1', Integer, primary_key=True, test_needs_autoincrement=True), Column('col2', String(30)), Column('col3', Integer, ForeignKey("mytable.col1"))) @profile_memory def go(): m1 = mapper( A, table1, properties={"bs": relationship(B, order_by=table2.c.col1)}) m2 = mapper(B, table2) m3 = mapper(A, table1, non_primary=True) 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.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_([ 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() sess.close() clear_mappers() metadata.create_all() try: go() finally: metadata.drop_all() assert_no_mappers()
def setup_class(cls): global counters, metadata metadata = MetaData() counters = Table('forupdate_counters', metadata, Column('counter_id', INT, primary_key=True), Column('counter_value', INT), test_needs_acid=True) counters.create(testing.db)
def define_tables(cls, metadata): Table('p', metadata, Column('id', String(10), primary_key=True), Column('version_id', String(32), nullable=False), Column('data', String(50))) Table('c', metadata, Column('id', String(10), ForeignKey('p.id'), primary_key=True), Column('version_id', String(32), nullable=False), Column('data', String(50)))
def setup_class(cls): global users, metadata metadata = MetaData() users = Table('query_users', metadata, Column('user_id', INT, primary_key = True), Column('user_name', VARCHAR(20)), test_needs_acid=True, ) users.create(testing.db)
def setup(self): meta = MetaData(testing.db) global table table = Table('tables', meta, Column('id', Integer, primary_key=True, key='foo_id', test_needs_autoincrement=True), Column('data', String(20)), ) table.create(checkfirst=True)
def setup(self): meta = MetaData(testing.db) global table, seq seq = Sequence('tid_seq') table = Table('tables', meta, Column('id', Integer, seq, primary_key=True), Column('data', String(50)) ) table.create(checkfirst=True)
def test_autoincrement_single_col(self): single = Table('single', self.metadata, Column('id', Integer, primary_key=True)) single.create() r = single.insert().execute() id_ = r.last_inserted_ids()[0] assert id_ is not None eq_(1, sa.select([func.count(sa.text('*'))], from_obj=single).scalar())
def test_metadata_connect(self): metadata = MetaData() t1 = Table("table1", metadata, Column("col1", Integer, primary_key=True), Column("col2", String(20))) metadata.bind = testing.db metadata.create_all() try: assert t1.count().scalar() == 0 finally: metadata.drop_all()
def setup_class(cls): global users, metadata, tlengine tlengine = create_engine(testing.db.url, strategy='threadlocal') metadata = MetaData() users = Table('query_users', metadata, Column('user_id', INT, Sequence('query_users_id_seq', optional=True), primary_key=True), Column('user_name', VARCHAR(20)), test_needs_acid=True, ) users.create(tlengine)
def setup(self): global meta, table, engine engine = engines.reconnecting_engine() meta = MetaData(engine) table = Table('sometable', meta, Column('id', Integer, primary_key=True), Column('name', String(50))) meta.create_all() table.insert().execute( [{'id':i, 'name':'row %d' % i} for i in range(1, 100)] )
def test_empty_insert(self): metadata = MetaData(testing.db) t1 = Table('t1', metadata, Column('is_true', Boolean, server_default=('1'))) metadata.create_all() try: result = t1.insert().execute() eq_(1, select([func.count(text('*'))], from_obj=t1).scalar()) eq_(True, t1.select().scalar()) finally: metadata.drop_all()
def test_tometadata_kwargs(self): meta = MetaData() table = Table('mytable', meta, Column('myid', Integer, primary_key=True), mysql_engine='InnoDB', ) meta2 = MetaData() table_c = table.tometadata(meta2) eq_(table.kwargs,table_c.kwargs)
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])
def test_override_existing_fk(self): """test that you can override columns and specify new foreign keys to other reflected tables, on columns which *do* already have that foreign key, and that the FK is not duped. """ meta = MetaData(testing.db) users = Table('users', meta, Column('id', sa.Integer, primary_key=True), Column('name', sa.String(30)), test_needs_fk=True) addresses = Table('addresses', meta, Column('id', sa.Integer, primary_key=True), Column('user_id', sa.Integer, sa.ForeignKey('users.id')), test_needs_fk=True) meta.create_all() try: meta2 = MetaData(testing.db) a2 = Table('addresses', meta2, Column('user_id', sa.Integer, sa.ForeignKey('users.id')), autoload=True) u2 = Table('users', meta2, autoload=True) s = sa.select([a2]) assert s.c.user_id assert len(a2.foreign_keys) == 1 assert len(a2.c.user_id.foreign_keys) == 1 assert len(a2.constraints) == 2 assert [c.parent for c in a2.foreign_keys] == [a2.c.user_id] assert [c.parent for c in a2.c.user_id.foreign_keys] == [a2.c.user_id] assert list(a2.c.user_id.foreign_keys)[0].parent is a2.c.user_id assert u2.join(a2).onclause == u2.c.id==a2.c.user_id meta2 = MetaData(testing.db) u2 = Table('users', meta2, Column('id', sa.Integer, primary_key=True), autoload=True) a2 = Table('addresses', meta2, Column('id', sa.Integer, primary_key=True), Column('user_id', sa.Integer, sa.ForeignKey('users.id')), autoload=True) s = sa.select([a2]) assert s.c.user_id assert len(a2.foreign_keys) == 1 assert len(a2.c.user_id.foreign_keys) == 1 assert len(a2.constraints) == 2 assert [c.parent for c in a2.foreign_keys] == [a2.c.user_id] assert [c.parent for c in a2.c.user_id.foreign_keys] == [a2.c.user_id] assert list(a2.c.user_id.foreign_keys)[0].parent is a2.c.user_id assert u2.join(a2).onclause == u2.c.id==a2.c.user_id finally: meta.drop_all()
def test_fk_no_such_target_col_error(self): meta = MetaData() a = Table('a', meta, Column('a', Integer)) b = Table('b', meta, Column('b', Integer)) a.append_constraint( ForeignKeyConstraint(['a'], ['b.x']) ) def go(): list(a.c.a.foreign_keys)[0].column assert_raises_message( exc.NoReferencedColumnError, "Could not create ForeignKey 'b.x' on " "table 'a': table 'b' has no column named 'x'", go )
def test_non_autoincrement(self): # sqlite INT primary keys can be non-unique! (only for ints) nonai = Table("nonaitest", self.metadata, Column('id', Integer, autoincrement=False, primary_key=True), Column('data', String(20))) nonai.create() try: # postgres + mysql strict will fail on first row, # mysql in legacy mode fails on second row nonai.insert().execute(data='row 1') nonai.insert().execute(data='row 2') assert False except sa.exc.SQLError, e: assert True
def test_tometadata_already_there(self): meta1 = MetaData() table1 = Table('mytable', meta1, Column('myid', Integer, primary_key=True), ) meta2 = MetaData() table2 = Table('mytable', meta2, Column('yourid', Integer, primary_key=True), ) meta3 = MetaData() table_c = table1.tometadata(meta2) table_d = table2.tometadata(meta2) # d'oh! assert table_c is table_d
def test_rollback_deadlock(self): """test that returning connections to the pool clears any object locks.""" conn1 = testing.db.connect() conn2 = testing.db.connect() users = Table('deadlock_users', metadata, Column('user_id', INT, primary_key = True), Column('user_name', VARCHAR(20)), test_needs_acid=True, ) users.create(conn1) conn1.execute("select * from deadlock_users") conn1.close() # without auto-rollback in the connection pool's return() logic, this # deadlocks in PostgreSQL, because conn1 is returned to the pool but # still has a lock on "deadlock_users". # comment out the rollback in pool/ConnectionFairy._close() to see ! users.drop(conn2) conn2.close()
def test_fk_copy(self): c1 = Column('foo', Integer) c2 = Column('bar', Integer) m = MetaData() t1 = Table('t', m, c1, c2) kw = dict(onupdate="X", ondelete="Y", use_alter=True, name='f1', deferrable="Z", initially="Q", link_to_name=True) fk1 = ForeignKey(c1, **kw) fk2 = ForeignKeyConstraint((c1,), (c2,), **kw) t1.append_constraint(fk2) fk1c = fk1.copy() fk2c = fk2.copy() for k in kw: eq_(getattr(fk1c, k), kw[k]) eq_(getattr(fk2c, k), kw[k])
def test_unknown_types(self): meta = MetaData(testing.db) t = Table("test", meta, Column('foo', sa.DateTime)) ischema_names = testing.db.dialect.ischema_names t.create() testing.db.dialect.ischema_names = {} try: m2 = MetaData(testing.db) assert_raises(sa.exc.SAWarning, Table, "test", m2, autoload=True) @testing.emits_warning('Did not recognize type') def warns(): m3 = MetaData(testing.db) t3 = Table("test", m3, autoload=True) assert t3.c.foo.type.__class__ == sa.types.NullType finally: testing.db.dialect.ischema_names = ischema_names t.drop()
def test_tometadata_indexes(self): meta = MetaData() table = Table('mytable', meta, Column('id', Integer, primary_key=True), Column('data1', Integer, index=True), Column('data2', Integer), ) Index('multi',table.c.data1,table.c.data2), meta2 = MetaData() table_c = table.tometadata(meta2) def _get_key(i): return [i.name,i.unique] + \ sorted(i.kwargs.items()) + \ i.columns.keys() eq_( sorted([_get_key(i) for i in table.indexes]), sorted([_get_key(i) for i in table_c.indexes]) )
def test_create_drop_explicit(self): metadata = MetaData() table = Table("test_table", metadata, Column("foo", Integer)) for bind in (testing.db, testing.db.connect()): for args in [([], {"bind": bind}), ([bind], {})]: metadata.create_all(*args[0], **args[1]) assert table.exists(*args[0], **args[1]) metadata.drop_all(*args[0], **args[1]) table.create(*args[0], **args[1]) table.drop(*args[0], **args[1]) assert not table.exists(*args[0], **args[1])
def test_override_pkfk(self): """test that you can override columns which contain foreign keys to other reflected tables, where the foreign key column is also a primary key column""" meta = MetaData(testing.db) users = Table('users', meta, Column('id', sa.Integer, primary_key=True), Column('name', sa.String(30))) addresses = Table('addresses', meta, Column('id', sa.Integer, primary_key=True), Column('street', sa.String(30))) meta.create_all() try: meta2 = MetaData(testing.db) a2 = Table('addresses', meta2, Column('id', sa.Integer, sa.ForeignKey('users.id'), primary_key=True), autoload=True) u2 = Table('users', meta2, autoload=True) assert list(a2.primary_key) == [a2.c.id] assert list(u2.primary_key) == [u2.c.id] assert u2.join(a2).onclause == u2.c.id==a2.c.id meta3 = MetaData(testing.db) u3 = Table('users', meta3, autoload=True) a3 = Table('addresses', meta3, Column('id', sa.Integer, sa.ForeignKey('users.id'), primary_key=True), autoload=True) assert list(a3.primary_key) == [a3.c.id] assert list(u3.primary_key) == [u3.c.id] assert u3.join(a3).onclause == u3.c.id==a3.c.id finally: meta.drop_all()
def test_prefixes(self): table1 = Table("temporary_table_1", self.metadata, Column("col1", Integer), prefixes=["TEMPORARY"]) table1.create() assert [str(x) for x in self.engine.mock if "CREATE TEMPORARY TABLE" in str(x)] del self.engine.mock[:] table2 = Table("temporary_table_2", self.metadata, Column("col1", Integer), prefixes=["VIRTUAL"]) table2.create() assert [str(x) for x in self.engine.mock if "CREATE VIRTUAL TABLE" in str(x)]
def test_no_rowcount_on_selects_inserts(self): """assert that rowcount is only called on deletes and updates. This because cursor.rowcount can be expensive on some dialects such as Firebird. """ engine = engines.testing_engine() metadata.bind = engine t = Table('t1', metadata, Column('data', String(10)) ) metadata.create_all() class BreakRowcountMixin(object): @property def rowcount(self): assert False execution_ctx_cls = engine.dialect.execution_ctx_cls engine.dialect.execution_ctx_cls = type("FakeCtx", (BreakRowcountMixin, execution_ctx_cls), {}) try: r = t.insert().execute({'data': 'd1'}, {'data': 'd2'}, {'data': 'd3'}) eq_(t.select().execute().fetchall(), [('d1', ), ('d2', ), ('d3', )]) assert_raises(AssertionError, t.update().execute, {'data' : 'd4'}) assert_raises(AssertionError, t.delete().execute) finally: engine.dialect.execution_ctx_cls = execution_ctx_cls
def setup(self): meta = MetaData(testing.db) global table, GoofyType class GoofyType(TypeDecorator): impl = String def process_bind_param(self, value, dialect): if value is None: return None return "FOO" + value def process_result_value(self, value, dialect): if value is None: return None return value + "BAR" table = Table('tables', meta, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('persons', Integer), Column('full', Boolean), Column('goofy', GoofyType(50)) ) table.create(checkfirst=True)