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)
