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')))
