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):
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 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 define_tables(cls, metadata):
Table(
'users',
metadata,
Column('id',
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column('name', String(30), nullable=False),
)
Table(
'addresses',
metadata,
Column('id',
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column('user_id', None, ForeignKey('users.id')),
Column('name', String(30), nullable=False),
Column('email_address', String(50), nullable=False),
)
Table(
"dingalings",
metadata,
Column('id',
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column('address_id', None, ForeignKey('addresses.id')),
Column('data', String(30)),
)
def define_tables(cls, metadata):
Table(
'companies', metadata,
Column('company_id',
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column('company_name', String(40)))
Table(
'addresses', metadata,
Column('address_id',
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column('company_id', Integer, ForeignKey("companies.company_id")),
Column('address', String(40)))
Table(
'phone_numbers', metadata,
Column('phone_id',
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column('address_id', Integer, ForeignKey('addresses.address_id')),
Column('type', String(20)), Column('number', String(10)))
Table(
'invoices', metadata,
Column('invoice_id',
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column('company_id', Integer, ForeignKey("companies.company_id")),
Column('date', sa.DateTime))
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 test_default_exec(self):
metadata = MetaData(testing.db)
t1 = Table(
't1',
metadata,
Column(u'special_col',
Integer,
Sequence('special_col'),
primary_key=True),
Column('data',
String(50)) # to appease SQLite without DEFAULT VALUES
)
metadata.create_all()
try:
engine = metadata.bind
# reset the identifier preparer, so that we can force it to cache
# a unicode identifier
engine.dialect.identifier_preparer = engine.dialect.preparer(
engine.dialect)
select([column(u'special_col')]).select_from(t1).execute().close()
assert isinstance(
engine.dialect.identifier_preparer.format_sequence(
Sequence('special_col')), unicode)
# now execute, run the sequence. it should run in u"Special_col.nextid" or similar as
# a unicode object; cx_oracle asserts that this is None or a String (postgresql lets it pass thru).
# ensure that executioncontext._exec_default() is encoding.
t1.insert().execute(data='foo')
finally:
metadata.drop_all()
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 define_tables(cls, metadata):
people = Table(
'people', metadata,
Column('person_id',
Integer,
primary_key=True,
test_needs_autoincrement=True), Column('name', String(50)),
Column('type', String(30)))
engineers = Table(
'engineers', metadata,
Column('person_id',
Integer,
ForeignKey('people.person_id'),
primary_key=True), Column('primary_language', String(50)),
Column('reports_to_id', Integer, ForeignKey('managers.person_id')))
managers = Table(
'managers',
metadata,
Column('person_id',
Integer,
ForeignKey('people.person_id'),
primary_key=True),
)
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=relationship(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 test_table_overrides_metadata_create(self):
metadata = self.metadata
s1 = Sequence("s1", metadata=metadata)
s2 = Sequence("s2", metadata=metadata)
s3 = Sequence("s3")
t = Table('t', metadata,
Column('c', Integer, s3, primary_key=True))
assert s3.metadata is metadata
t.create(testing.db)
s3.drop(testing.db)
# 't' is created, and 's3' won't be
# re-created since it's linked to 't'.
# 's1' and 's2' are, however.
metadata.create_all(testing.db)
assert self._has_sequence('s1')
assert self._has_sequence('s2')
assert not self._has_sequence('s3')
s2.drop(testing.db)
assert self._has_sequence('s1')
assert not self._has_sequence('s2')
metadata.drop_all(testing.db)
assert not self._has_sequence('s1')
assert not self._has_sequence('s2')
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 _run_test(self, *arg, **kw):
metadata = self.metadata
implicit_returning = kw.pop("implicit_returning", True)
kw["primary_key"] = True
if kw.get("autoincrement", True):
kw["test_needs_autoincrement"] = True
t = Table(
"x",
metadata,
Column("y", self.MyInteger, *arg, **kw),
Column("data", Integer),
implicit_returning=implicit_returning,
)
t.create()
r = t.insert().values(data=5).execute()
# we don't pre-fetch 'server_default'.
if "server_default" in kw and (not testing.db.dialect.implicit_returning or not implicit_returning):
eq_(r.inserted_primary_key, [None])
else:
eq_(r.inserted_primary_key, ["INT_1"])
r.close()
eq_(t.select().execute().first(), ("INT_1", 5))
def test_table_overrides_metadata_create(self):
metadata = self.metadata
s1 = Sequence("s1", metadata=metadata)
s2 = Sequence("s2", metadata=metadata)
s3 = Sequence("s3")
t = Table('t', metadata,
Column('c', Integer, s3, primary_key=True))
assert s3.metadata is metadata
t.create(testing.db, checkfirst=True)
s3.drop(testing.db)
# 't' is created, and 's3' won't be
# re-created since it's linked to 't'.
# 's1' and 's2' are, however.
metadata.create_all(testing.db)
assert self._has_sequence('s1')
assert self._has_sequence('s2')
assert not self._has_sequence('s3')
s2.drop(testing.db)
assert self._has_sequence('s1')
assert not self._has_sequence('s2')
metadata.drop_all(testing.db)
assert not self._has_sequence('s1')
assert not self._has_sequence('s2')
def test_int_default_none_on_insert_reflected(self):
metadata = self.metadata
t = Table('x', metadata,
Column('y', Integer,
server_default='5', primary_key=True),
Column('data', String(10)),
implicit_returning=False
)
metadata.create_all()
m2 = MetaData(metadata.bind)
t2 = Table('x', m2, autoload=True, implicit_returning=False)
r = t2.insert().execute(data='data')
eq_(r.inserted_primary_key, [None])
if testing.against('sqlite'):
eq_(
t2.select().execute().fetchall(),
[(1, 'data')]
)
else:
eq_(
t2.select().execute().fetchall(),
[(5, 'data')]
)
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=relationship(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):
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 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(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):
if testing.against('oracle'):
fk_args = dict(deferrable=True, initially='deferred')
elif testing.against('mysql'):
fk_args = {}
else:
fk_args = dict(onupdate='cascade')
Table(
'users',
metadata,
Column('id',
Integer,
primary_key=True,
test_needs_autoincrement=True),
)
Table(
'addresses',
metadata,
Column('id',
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column('user_id', Integer, ForeignKey('users.id', **fk_args)),
)
def _run_test(self, *arg, **kw):
metadata = self.metadata
implicit_returning = kw.pop('implicit_returning', True)
kw['primary_key'] = True
if kw.get('autoincrement', True):
kw['test_needs_autoincrement'] = True
t = Table('x', metadata,
Column('y', self.MyInteger, *arg, **kw),
Column('data', Integer),
implicit_returning=implicit_returning
)
t.create()
r = t.insert().values(data=5).execute()
# we don't pre-fetch 'server_default'.
if 'server_default' in kw and (not testing.db.dialect.implicit_returning or not implicit_returning):
eq_(r.inserted_primary_key, [None])
else:
eq_(r.inserted_primary_key, ['INT_1'])
r.close()
eq_(
t.select().execute().first(),
('INT_1', 5)
)
def test_collection_setstate(self):
"""test a particular cycle that requires CollectionAdapter
to not rely upon InstanceState to deserialize."""
m = MetaData()
c1 = Table(
'c1', m,
Column('parent_id', String, ForeignKey('p.id'), primary_key=True))
c2 = Table(
'c2', m,
Column('parent_id', String, ForeignKey('p.id'), primary_key=True))
p = Table('p', m, Column('id', String, primary_key=True))
mapper(Parent,
p,
properties={
'children1': relationship(Child1),
'children2': relationship(Child2)
})
mapper(Child1, c1)
mapper(Child2, c2)
obj = Parent()
screen1 = Screen(obj)
screen1.errors = [obj.children1, obj.children2]
screen2 = Screen(Child2(), screen1)
pickle.loads(pickle.dumps(screen2))
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_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 test_autoincrement_single_col(self):
single = Table("single", self.metadata, Column("id", Integer, primary_key=True))
single.create()
r = single.insert().execute()
id_ = r.inserted_primary_key[0]
eq_(id_, 1)
eq_(1, sa.select([func.count(sa.text("*"))], from_obj=single).scalar())
def test_int_default_on_insert_with_returning(self):
metadata = self.metadata
t = Table("x", metadata, Column("y", Integer, server_default="5", primary_key=True), Column("data", String(10)))
metadata.create_all()
r = t.insert().execute(data="data")
eq_(r.inserted_primary_key, [5])
eq_(t.select().execute().fetchall(), [(5, "data")])
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('users', metadata,
Column('id', Integer, primary_key=True, test_needs_autoincrement=True),
Column('name', String(30)),
Column('type', String(30)))
Table('email_users', metadata,
Column('id', Integer, ForeignKey('users.id'), primary_key=True),
Column('email_address', String(30)))
def 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(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 define_tables(cls, metadata):
t2 = Table('t2', metadata,
Column('nextid', Integer))
Table('t1', metadata,
Column('id', Integer, primary_key=True,
default=sa.select([func.max(t2.c.nextid)]).as_scalar()),
Column('data', String(30)))
def test_table_plus_column_exceeds_length(self):
"""test that the truncation occurs if tablename / colname are only
greater than the max when concatenated."""
compile_dialect = default.DefaultDialect(label_length=30)
m = MetaData()
a_table = Table(
'thirty_characters_table_xxxxxx',
m,
Column('id', Integer, primary_key=True)
)
other_table = Table(
'other_thirty_characters_table_',
m,
Column('id', Integer, primary_key=True),
Column('thirty_characters_table_id',
Integer,
ForeignKey('thirty_characters_table_xxxxxx.id'),
primary_key=True
)
)
anon = a_table.alias()
self.assert_compile(
select([other_table,anon]).
select_from(
other_table.outerjoin(anon)
).apply_labels(),
"SELECT other_thirty_characters_table_.id AS "
"other_thirty_characters__1, "
"other_thirty_characters_table_.thirty_characters_table_id "
"AS other_thirty_characters__2, thirty_characters_table__1.id "
"AS thirty_characters_table__3 "
"FROM other_thirty_characters_table_ "
"LEFT OUTER JOIN thirty_characters_table_xxxxxx "
"AS thirty_characters_table__1 ON "
"thirty_characters_table__1.id = "
"other_thirty_characters_table_.thirty_characters_table_id",
dialect=compile_dialect)
self.assert_compile(
select([other_table, anon]).
select_from(
other_table.outerjoin(anon)
).apply_labels(),
"SELECT other_thirty_characters_table_.id AS "
"other_thirty_characters__1, "
"other_thirty_characters_table_.thirty_characters_table_id "
"AS other_thirty_characters__2, "
"thirty_characters_table__1.id AS thirty_characters_table__3 "
"FROM other_thirty_characters_table_ "
"LEFT OUTER JOIN thirty_characters_table_xxxxxx "
"AS thirty_characters_table__1 ON "
"thirty_characters_table__1.id = "
"other_thirty_characters_table_.thirty_characters_table_id",
dialect=compile_dialect
)
def test_func_embedded_whereclause(self):
"""test can use next_value() in whereclause"""
metadata = self.metadata
t1 = Table("t", metadata, Column("x", Integer))
t1.create(testing.db)
testing.db.execute(t1.insert(), [{"x": 1}, {"x": 300}, {"x": 301}])
s = Sequence("my_sequence")
eq_(testing.db.execute(t1.select().where(t1.c.x > s.next_value())).fetchall(), [(300,), (301,)])
def test_func_embedded_valuesbase(self):
"""test can use next_value() in values() of _ValuesBase"""
metadata = self.metadata
t1 = Table("t", metadata, Column("x", Integer))
t1.create(testing.db)
s = Sequence("my_sequence")
testing.db.execute(t1.insert().values(x=s.next_value()))
self._assert_seq_result(testing.db.scalar(t1.select()))
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 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_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 test_autoincrement_single_col(self):
single = Table('single', self.metadata,
Column('id', Integer, primary_key=True))
single.create()
r = single.insert().execute()
id_ = r.inserted_primary_key[0]
eq_(id_, 1)
eq_(1, sa.select([func.count(sa.text('*'))], from_obj=single).scalar())
def define_tables(cls, metadata):
rocks_table = Table("rocks", metadata,
Column("id", Integer, primary_key=True),
Column("description", String(10)),
)
bugs_table = Table("bugs", metadata,
Column("id", Integer, primary_key=True),
Column("rockid", Integer, ForeignKey('rocks.id')),
)
def define_tables(cls, metadata):
Table('t1', metadata, Column('id', Integer, primary_key=True),
Column('foo', Integer))
Table(
't2',
metadata,
Column('id', Integer, ForeignKey('t1.id'), primary_key=True),
Column('t1id', Integer, ForeignKey('t1.id')),
)
def define_tables(cls, metadata):
Table('foo', metadata,
Column('id', Integer, primary_key=True, test_needs_autoincrement=True),
Column('x', Integer),
Column('y', Integer)
)
Table('subfoo', metadata,
Column('id', Integer, ForeignKey('foo.id'), primary_key=True),
)
def test_col_replace_w_constraint(self):
m = MetaData()
a = Table('a', m, Column('id', Integer, primary_key=True))
aid = Column('a_id', ForeignKey('a.id'))
b = Table('b', m, aid)
b.append_column(aid)
assert b.c.a_id.references(a.c.id)
eq_(len(b.constraints), 2)
def test_int_default_on_insert_with_returning(self):
metadata = self.metadata
t = Table('x', metadata,
Column('y', Integer, server_default='5', primary_key=True),
Column('data', String(10)))
metadata.create_all()
r = t.insert().execute(data='data')
eq_(r.inserted_primary_key, [5])
eq_(t.select().execute().fetchall(), [(5, 'data')])
def test_insert_no_pk(self):
t = Table(
"some_other_large_named_table", self.metadata,
Column("this_is_the_primarykey_column",
Integer,
Sequence("this_is_some_large_seq"),
primary_key=True),
Column("this_is_the_data_column", String(30)))
t.create(testing.db, checkfirst=True)
testing.db.execute(t.insert(), **{"this_is_the_data_column": "data1"})
def test_checkfirst_table(self):
m = MetaData()
s = Sequence("my_sequence")
t = Table('t', m, Column('c', Integer, s, primary_key=True))
t.create(testing.db, checkfirst=False)
assert self._has_sequence('my_sequence')
t.create(testing.db, checkfirst=True)
t.drop(testing.db, checkfirst=False)
assert not self._has_sequence('my_sequence')
t.drop(testing.db, checkfirst=True)
def test_insert_no_pk(self):
t = Table("some_other_large_named_table", self.metadata,
Column("this_is_the_primarykey_column", Integer,
Sequence("this_is_some_large_seq"),
primary_key=True),
Column("this_is_the_data_column", String(30))
)
t.create(testing.db, checkfirst=True)
testing.db.execute(t.insert(),
**{"this_is_the_data_column":"data1"})
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_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(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_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_key_fallback_result(self):
e = testing.db
m = self.metadata
t = Table('t', m, Column('x', Integer), Column('y', Integer))
m.create_all(e)
e.execute(t.insert(), {"x":1, "y":1})
@profile_memory
def go():
r = e.execute(t.alias().select())
for row in r:
row[t.c.x]
go()
def test_inserted_pk_implicit_returning(self):
"""test inserted_primary_key contains the result when
pk_col=next_value(), when implicit returning is used."""
metadata = self.metadata
e = engines.testing_engine(options={"implicit_returning": True})
s = Sequence("my_sequence")
metadata.bind = e
t1 = Table("t", metadata, Column("x", Integer, primary_key=True))
t1.create()
r = e.execute(t1.insert().values(x=s.next_value()))
self._assert_seq_result(r.inserted_primary_key[0])
def test_inserted_pk_no_returning(self):
"""test inserted_primary_key contains [None] when
pk_col=next_value(), implicit returning is not used."""
metadata = self.metadata
e = engines.testing_engine(options={"implicit_returning": False})
s = Sequence("my_sequence")
metadata.bind = e
t1 = Table("t", metadata, Column("x", Integer, primary_key=True))
t1.create()
r = e.execute(t1.insert().values(x=s.next_value()))
eq_(r.inserted_primary_key, [None])
def test_checkfirst_table(self):
m = MetaData()
s = Sequence("my_sequence")
t = Table('t', m, Column('c', Integer, s, primary_key=True))
t.create(testing.db, checkfirst=False)
assert self._has_sequence('my_sequence')
t.create(testing.db, checkfirst=True)
t.drop(testing.db, checkfirst=False)
assert not self._has_sequence('my_sequence')
t.drop(testing.db, checkfirst=True)
def test_string_default_on_insert_with_returning(self):
"""With implicit_returning, we get a string PK default back no problem."""
metadata = self.metadata
t = Table(
"x",
metadata,
Column("y", String(10), server_default="key_one", primary_key=True),
Column("data", String(10)),
)
metadata.create_all()
r = t.insert().execute(data="data")
eq_(r.inserted_primary_key, ["key_one"])
eq_(t.select().execute().fetchall(), [("key_one", "data")])
def test_string_default_on_insert_with_returning(self):
"""With implicit_returning, we get a string PK default back no problem."""
metadata = self.metadata
t = Table('x', metadata,
Column('y', String(10), server_default='key_one', primary_key=True),
Column('data', String(10))
)
metadata.create_all()
r = t.insert().execute(data='data')
eq_(r.inserted_primary_key, ['key_one'])
eq_(
t.select().execute().fetchall(),
[('key_one', 'data')]
)
def test_int_default_on_insert_with_returning(self):
metadata = self.metadata
t = Table('x', metadata,
Column('y', Integer,
server_default='5', primary_key=True),
Column('data', String(10))
)
metadata.create_all()
r = t.insert().execute(data='data')
eq_(r.inserted_primary_key, [5])
eq_(
t.select().execute().fetchall(),
[(5, 'data')]
)
def test_custom_subclass_proxy(self):
"""test proxy generation of a Column subclass, can be compiled."""
from sqlalchemy.schema import Column
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import select
class MyColumn(Column):
def _constructor(self, name, type, **kw):
kw['name'] = name
return MyColumn(type, **kw)
def __init__(self, type, **kw):
Column.__init__(self, type, **kw)
def my_goofy_thing(self):
return "hi"
@compiles(MyColumn)
def goofy(element, compiler, **kw):
s = compiler.visit_column(element, **kw)
return s + "-"
id = MyColumn(Integer, primary_key=True)
id.name = 'id'
name = MyColumn(String)
name.name = 'name'
t1 = Table('foo', MetaData(),
id,
name
)
# goofy thing
eq_(t1.c.name.my_goofy_thing(), "hi")
# create proxy
s = select([t1.select().alias()])
# proxy has goofy thing
eq_(s.c.name.my_goofy_thing(), "hi")
# compile works
self.assert_compile(
select([t1.select().alias()]),
"SELECT anon_1.id-, anon_1.name- FROM "
"(SELECT foo.id- AS id, foo.name- AS name "
"FROM foo) AS anon_1",
)
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
)
