class Manager(Person): __tablename__ = 'managers' __mapper_args__ = {'polymorphic_identity':'manager'} id = Column('id', Integer, ForeignKey('people.id'), primary_key=True) golf_swing = Column('golf_swing', String(50))
class User(Base): __tablename__ = 'users' id = Column('id', Integer, primary_key=True) name = Column('name', String(50))
def define_tables(self, metadata): global t1 t1 = Table('t1', metadata, Column('id', Integer, primary_key=True), Column('data', String(50)) )
class Engineer(Person): __tablename__ = 'engineers' __mapper_args__ = {'polymorphic_identity':'engineer'} id = Column('id', Integer, ForeignKey('people.id'), primary_key=True) primary_language = Column('primary_language', String(50))
class User(Base, ComparableEntity): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50))
class Address(Base, ComparableEntity): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) email = Column(String(50)) user_id = Column(Integer) # note no foreign key
def setUpAll(self): global t, f, f2, ts, currenttime, metadata, default_generator db = testing.db metadata = MetaData(db) default_generator = {'x': 50} def mydefault(): default_generator['x'] += 1 return default_generator['x'] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text('13')])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection try: return conn.execute(sa.select([sa.text('12')])).scalar() finally: # ensure a "close()" on this connection does nothing, # since its a "branched" connection conn.close() use_function_defaults = testing.against('postgres', 'mssql', 'maxdb') is_oracle = testing.against('oracle') # select "count(1)" returns different results on different DBs also # correct for "current_date" compatible as column default, value # differences currenttime = func.current_date(type_=sa.Date, bind=db) if is_oracle: ts = db.scalar( sa.select([ func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label('today') ])) assert isinstance( ts, datetime.date) and not isinstance(ts, datetime.datetime) f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() # TODO: engine propigation across nested functions not working currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date) def1 = currenttime def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date) deftype = sa.Date elif use_function_defaults: f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() def1 = currenttime deftype = sa.Date if testing.against('maxdb'): def2 = sa.text("curdate") elif testing.against('mssql'): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = db.func.current_date().scalar() else: f = len('abcdef') f2 = len('abcdefghijk') def1 = def2 = "3" ts = 3 deftype = Integer t = Table( 'default_test1', metadata, # python function Column('col1', Integer, primary_key=True, default=mydefault), # python literal Column('col2', String(20), default="imthedefault", onupdate="im the update"), # preexecute expression Column('col3', Integer, default=func.length('abcdef'), onupdate=func.length('abcdefghijk')), # SQL-side default from sql expression Column('col4', deftype, server_default=def1), # SQL-side default from literal expression Column('col5', deftype, server_default=def2), # preexecute + update timestamp Column('col6', sa.Date, default=currenttime, onupdate=currenttime), Column('boolcol1', sa.Boolean, default=True), Column('boolcol2', sa.Boolean, default=False), # python function which uses ExecutionContext Column('col7', Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx), # python builtin Column('col8', sa.Date, default=datetime.date.today, onupdate=datetime.date.today), # combo Column('col9', String(20), default='py', server_default='ddl')) t.create()
class Address(Base, ComparableEntity): __tablename__ = 'addresses' id = Column('id', Integer, primary_key=True) email = Column('email', String(50)) user_id = Column('user_id', Integer, ForeignKey('users.id'))
[dict(zip(columns, column_values)) for column_values in rows]) table.info[('fixture', 'loader')] = load_fixture table.info[('fixture', 'columns')] = columns table.info[('fixture', 'rows')] = rows return table users = fixture_table( Table('users', fixture_metadata, Column('id', Integer, primary_key=True, test_needs_autoincrement=True), Column('name', String(30), nullable=False), test_needs_acid=True, test_needs_fk=True), ('id', 'name'), (7, 'jack'), (8, 'ed'), (9, 'fred'), (10, 'chuck')) addresses = fixture_table( Table('addresses', fixture_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), test_needs_acid=True, test_needs_fk=True), ('id', 'user_id', 'email_address'),
def test_to_metadata(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, ) def test_to_metadata(): meta2 = MetaData() table_c = table.tometadata(meta2) table2_c = table2.tometadata(meta2) return (table_c, table2_c) def test_pickle(): meta.bind = testing.db meta2 = pickle.loads(pickle.dumps(meta)) assert meta2.bind is None meta3 = pickle.loads(pickle.dumps(meta2)) return (meta2.tables['mytable'], meta2.tables['othertable']) 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']) meta.create_all(testing.db) try: for test, has_constraints in ((test_to_metadata, True), (test_pickle, True), (test_pickle_via_reflect, False)): table_c, table2_c = test() self.assert_tables_equal(table, table_c) self.assert_tables_equal(table2, table2_c) assert table is not table_c assert table.primary_key is not table_c.primary_key assert list( table2_c.c.myid.foreign_keys)[0].column is table_c.c.myid assert list( table2_c.c.myid.foreign_keys)[0].column is not table.c.myid # constraints dont get reflected for any dialect right now if has_constraints: for c in table_c.c.description.constraints: if isinstance(c, CheckConstraint): break else: assert False assert c.sqltext == "description='hi'" for c in table_c.constraints: if isinstance(c, UniqueConstraint): break else: assert False assert c.columns.contains_column(table_c.c.name) assert not c.columns.contains_column(table.c.name) finally: meta.drop_all(testing.db)
def test_with_manytomany(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)), ) table3 = Table( 't1tot2', metadata, Column('t1', Integer, ForeignKey('mytable.col1')), Column('t2', Integer, ForeignKey('mytable2.col1')), ) @profile_memory def go(): class A(_base.ComparableEntity): pass class B(_base.ComparableEntity): pass mapper(A, table1, properties={ 'bs': relation(B, secondary=table3, backref='as', order_by=table3.c.t1) }) mapper(B, table2) sess = create_session() a1 = A(col2='a1') a2 = A(col2='a2') b1 = B(col2='b1') b2 = B(col2='b2') a1.bs.append(b1) a2.bs.append(b2) for x in [a1, a2]: sess.add(x) sess.flush() sess.clear() alist = sess.query(A).order_by(A.col1).all() self.assertEquals([A(bs=[B(col2='b1')]), A(bs=[B(col2='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 define_tables(self, metadata): Table('common', metadata, Column('id', Integer, primary_key=True), Column('data', Integer), Column('extra', String(45)))
def define_tables(self, metadata): Table('users', metadata, Column('id', Integer, primary_key=True), Column('name', String(20)), test_needs_acid=True)
class Parent(Base): __tablename__ = 'parent' id = Column('id', Integer, primary_key=True) tp = Column('type', String(50)) __mapper_args__ = dict(polymorphic_on = tp)
def test_proxy(self): stmts = [] cursor_stmts = [] class MyProxy(ConnectionProxy): def execute(self, conn, execute, clauseelement, *multiparams, **params): stmts.append((str(clauseelement), params, multiparams)) return execute(clauseelement, *multiparams, **params) def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): cursor_stmts.append((statement, parameters, None)) return execute(cursor, statement, parameters, context) def assert_stmts(expected, received): for stmt, params, posn in expected: if not received: assert False while received: teststmt, testparams, testmultiparams = received.pop(0) teststmt = re.compile(r'[\n\t ]+', re.M).sub(' ', teststmt).strip() if teststmt.startswith(stmt) and (testparams == params or testparams == posn): break for engine in ( engines.testing_engine(options=dict(proxy=MyProxy())), engines.testing_engine( options=dict(proxy=MyProxy(), strategy='threadlocal'))): m = MetaData(engine) t1 = Table( 't1', m, Column('c1', Integer, primary_key=True), Column('c2', String(50), default=func.lower('Foo'), primary_key=True)) m.create_all() try: t1.insert().execute(c1=5, c2='some data') t1.insert().execute(c1=6) assert engine.execute("select * from t1").fetchall() == [ (5, 'some data'), (6, 'foo') ] finally: m.drop_all() engine.dispose() compiled = [("CREATE TABLE t1", {}, None), ("INSERT INTO t1 (c1, c2)", { 'c2': 'some data', 'c1': 5 }, None), ("INSERT INTO t1 (c1, c2)", { 'c1': 6 }, None), ("select * from t1", {}, None), ("DROP TABLE t1", {}, None)] if engine.dialect.preexecute_pk_sequences: cursor = [("CREATE TABLE t1", {}, None), ("INSERT INTO t1 (c1, c2)", { 'c2': 'some data', 'c1': 5 }, [5, 'some data']), ("SELECT lower", { 'lower_2': 'Foo' }, ['Foo']), ("INSERT INTO t1 (c1, c2)", { 'c2': 'foo', 'c1': 6 }, [6, 'foo']), ("select * from t1", {}, None), ("DROP TABLE t1", {}, None)] else: cursor = [ ("CREATE TABLE t1", {}, ()), ("INSERT INTO t1 (c1, c2)", { 'c2': 'some data', 'c1': 5 }, [5, 'some data']), ("INSERT INTO t1 (c1, c2)", { 'c1': 6, "lower_2": "Foo" }, [6, "Foo" ]), # bind param name 'lower_2' might be incorrect ("select * from t1", {}, ()), ("DROP TABLE t1", {}, ()) ] assert_stmts(compiled, stmts) assert_stmts(cursor, cursor_stmts)
class User(Base, ComparableEntity): __tablename__ = 'users' id = Column('id', Integer, primary_key=True) name = Column('name', String(50)) addresses = relation("Address", backref="user")
class User(Base, ComparableEntity): __tablename__ = 'users' __autoload__ = True nom = Column('name', String(50), key='nom') addresses = relation("Address", backref="user")
class Company(Base, ComparableEntity): __tablename__ = 'companies' id = Column('id', Integer, primary_key=True) name = Column('name', String(50)) employees = relation("Person")
class User(Base, ComparableEntity): __tablename__ = 'users' id = Column('id', Integer, primary_key=True) name = Column('name', String(50)) addresses = relation("Address", order_by=(Address.email, Address.id))
class User(Base, ComparableEntity): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) addresses = relation("Address", order_by="desc(Address.email)", primaryjoin="User.id==Address.user_id", foreign_keys="[Address.user_id]")
class User(Base, ComparableEntity): __tablename__ = 'users' id = Column('id', Integer, primary_key=True) _name = Column('name', String(50)) name = sa.orm.synonym('_name', comparator_factory=CustomCompare)
def define_tables(self, metadata): Table("a_table", metadata, Column("id", Integer(), primary_key=True), Column("fui", String(128)), Column("b", Integer(), ForeignKey("a_table.id")))
def define_tables(self, metadata): Table( 'nodes', metadata, Column('name', String(50), primary_key=True), Column('parent', String(50), ForeignKey('nodes.name', onupdate='cascade')))