def define_tables(cls, metadata):
Table(
"left",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("data", String(30)),
)
Table(
"right",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("data", String(30)),
)
Table(
"secondary",
metadata,
Column("left_id", Integer, ForeignKey("left.id"),
primary_key=True),
Column("right_id",
Integer,
ForeignKey("right.id"),
primary_key=True),
)
def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name, "
"mytable.description",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING char_length(mytable.name) AS "
"length_1",
)
def define_tables(cls, metadata):
Table(
"users",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("name", String(50)),
test_needs_fk=True,
)
Table(
"user_items",
metadata,
Column("user_id", ForeignKey("users.id"), primary_key=True),
Column("item_id", ForeignKey("items.id"), primary_key=True),
test_needs_fk=True,
)
Table(
"items",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("name", String(50)),
test_needs_fk=True,
)
class Person(Base):
__tablename__ = "person"
id = Column(Integer, primary_key=True)
first_name = Column(String(10))
last_name = Column(String(10))
@hybrid.hybrid_property
def name(self):
return self.first_name + " " + self.last_name
@name.setter
def name(self, value):
self.first_name, self.last_name = value.split(" ", 1)
@name.expression
def name(cls):
return func.concat(cls.first_name, " ", cls.last_name)
@name.update_expression
def name(cls, value):
f, l = value.split(" ", 1)
return [(cls.first_name, f), (cls.last_name, l)]
@hybrid.hybrid_property
def uname(self):
return self.name
@hybrid.hybrid_property
def fname(self):
return self.first_name
@hybrid.hybrid_property
def fname2(self):
return self.fname
def test_index_create_camelcase(self):
"""test that mixed-case index identifiers are legal"""
metadata = self.metadata
employees = Table(
"companyEmployees",
metadata,
Column("id", Integer, primary_key=True),
Column("firstName", String(30)),
Column("lastName", String(30)),
Column("emailAddress", String(30)),
)
Index("employeeNameIndex", employees.c.lastName, employees.c.firstName)
Index("employeeEmailIndex", employees.c.emailAddress, unique=True)
self.assert_sql_execution(
testing.db,
lambda: metadata.create_all(checkfirst=False),
RegexSQL("^CREATE TABLE"),
AllOf(
CompiledSQL(
'CREATE INDEX "employeeNameIndex" ON '
'"companyEmployees" ("lastName", "firstName")',
[],
),
CompiledSQL(
'CREATE UNIQUE INDEX "employeeEmailIndex" ON '
'"companyEmployees" ("emailAddress")',
[],
),
),
)
class Person(Base):
__tablename__ = "person"
id = Column(Integer,
primary_key=True,
test_needs_autoincrement=True)
type = Column(String(50))
name = Column(String(50))
def define_tables(cls, metadata):
Table(
"users",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("name", String(50)),
test_needs_fk=True,
)
Table(
"addresses",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("email", String(50)),
Column("user_id", Integer, ForeignKey("users.id")),
test_needs_fk=True,
)
Table(
"imhandles",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("user_id", Integer),
Column("network", String(50)),
Column("handle", String(50)),
test_needs_fk=True,
)
def define_tables(cls, metadata):
Table(
"items",
metadata,
Column(
"item_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("name", String(40)),
)
Table(
"item_keywords",
metadata,
Column("item_id", Integer, ForeignKey("items.item_id")),
Column("keyword_id", Integer, ForeignKey("keywords.keyword_id")),
Column("data", String(40)),
)
Table(
"keywords",
metadata,
Column(
"keyword_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("name", String(40)),
)
def define_tables(cls, metadata):
Table(
"users",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("name", String(30), nullable=False),
Column("some_update", String(30), onupdate="im the update"),
)
Table(
"addresses",
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),
)
Table(
"foobar",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("user_id", None, ForeignKey("users.id")),
Column("data", String(30)),
Column("some_update", String(30), onupdate="im the other update"),
)
def setup_class(cls):
global t1, t2, metadata
metadata = MetaData()
t1 = Table(
"t1",
metadata,
Column("c1", Integer, primary_key=True),
Column("c2", String(30)),
)
t2 = Table(
"t2",
metadata,
Column("c1", Integer, primary_key=True),
Column("c2", String(30)),
)
cls.dialect = default.DefaultDialect()
# do a "compile" ahead of time to load
# deferred imports, use the dialect to pre-load
# dialect-level types
t1.insert().compile(dialect=cls.dialect)
# go through all the TypeEngine
# objects in use and pre-load their _type_affinity
# entries.
for t in (t1, t2):
for c in t.c:
c.type._type_affinity
from sqlalchemy_1_3.sql import sqltypes
for t in list(sqltypes._type_map.values()):
t._type_affinity
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_create_composite_index_with_length(self):
m = MetaData()
tbl = Table("testtbl", m, Column("a", String(255)),
Column("b", String(255)))
idx1 = Index("test_idx1",
tbl.c.a,
tbl.c.b,
mysql_length={
"a": 10,
"b": 20
})
idx2 = Index("test_idx2", tbl.c.a, tbl.c.b, mysql_length={"a": 15})
idx3 = Index("test_idx3", tbl.c.a, tbl.c.b, mysql_length=30)
self.assert_compile(
schema.CreateIndex(idx1),
"CREATE INDEX test_idx1 ON testtbl (a(10), b(20))",
)
self.assert_compile(
schema.CreateIndex(idx2),
"CREATE INDEX test_idx2 ON testtbl (a(15), b)",
)
self.assert_compile(
schema.CreateIndex(idx3),
"CREATE INDEX test_idx3 ON testtbl (a(30), b(30))",
)
def test_reflection_on_include_columns(self):
"""Test reflection of include_columns to be sure they respect case."""
case_table = Table(
"mysql_case",
MetaData(testing.db),
Column("c1", String(10)),
Column("C2", String(10)),
Column("C3", String(10)),
)
try:
case_table.create()
reflected = Table(
"mysql_case",
MetaData(testing.db),
autoload=True,
include_columns=["c1", "C2"],
)
for t in case_table, reflected:
assert "c1" in t.c.keys()
assert "C2" in t.c.keys()
reflected2 = Table(
"mysql_case",
MetaData(testing.db),
autoload=True,
include_columns=["c1", "c2"],
)
assert "c1" in reflected2.c.keys()
for c in ["c2", "C2", "C3"]:
assert c not in reflected2.c.keys()
finally:
case_table.drop()
def define_tables(cls, metadata):
Table(
"single",
metadata,
Column("id", Integer, primary_key=True),
Column("type", String(10)),
test_needs_fk=True,
)
Table(
"joined_base",
metadata,
Column("id", Integer, primary_key=True),
Column("type", String(10)),
test_needs_fk=True,
)
Table(
"joined_inh",
metadata,
Column("id",
Integer,
ForeignKey("joined_base.id"),
primary_key=True),
test_needs_fk=True,
)
FixtureTest.define_tables(metadata)
def test_typing_construction(self):
t = text("select * from table :foo :bar :bat")
self._assert_type_map(t, {
"foo": NullType(),
"bar": NullType(),
"bat": NullType()
})
t = t.bindparams(bindparam("foo", type_=String))
self._assert_type_map(t, {
"foo": String(),
"bar": NullType(),
"bat": NullType()
})
t = t.bindparams(bindparam("bar", type_=Integer))
self._assert_type_map(t, {
"foo": String(),
"bar": Integer(),
"bat": NullType()
})
t = t.bindparams(bat=45.564)
self._assert_type_map(t, {
"foo": String(),
"bar": Integer(),
"bat": Float()
})
def test_update_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
u = update(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name",
)
u = update(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name, "
"mytable.description",
)
u = update(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"char_length(mytable.name) AS length_1",
)
def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name VALUES "
"(:name)",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name, "
"inserted.description VALUES (:name)",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"LEN(inserted.name) AS length_1 VALUES "
"(:name)",
)
def define_tables(cls, metadata):
Table(
"departments",
metadata,
Column(
"department_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("name", String(50)),
)
Table(
"employees",
metadata,
Column(
"person_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
Column("name", String(50)),
Column(
"department_id",
Integer,
ForeignKey("departments.department_id"),
),
)
def define_tables(cls, metadata):
global principals
global users
global groups
global user_group_map
principals = Table(
"principals",
metadata,
Column(
"principal_id",
Integer,
Sequence("principal_id_seq", optional=False),
primary_key=True,
),
Column("name", String(50), nullable=False),
)
users = Table(
"prin_users",
metadata,
Column(
"principal_id",
Integer,
ForeignKey("principals.principal_id"),
primary_key=True,
),
Column("password", String(50), nullable=False),
Column("email", String(50), nullable=False),
Column("login_id", String(50), nullable=False),
)
groups = Table(
"prin_groups",
metadata,
Column(
"principal_id",
Integer,
ForeignKey("principals.principal_id"),
primary_key=True,
),
)
user_group_map = Table(
"prin_user_group_map",
metadata,
Column(
"user_id",
Integer,
ForeignKey("prin_users.principal_id"),
primary_key=True,
),
Column(
"group_id",
Integer,
ForeignKey("prin_groups.principal_id"),
primary_key=True,
),
)
def define_tables(cls, metadata):
Table(
"owners",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("data", String(30)),
)
Table(
"categories",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("name", String(20)),
)
Table(
"tests",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("owner_id",
Integer,
ForeignKey("owners.id"),
nullable=False),
Column(
"category_id",
Integer,
ForeignKey("categories.id"),
nullable=False,
),
)
Table(
"options",
metadata,
Column("test_id",
Integer,
ForeignKey("tests.id"),
primary_key=True),
Column("owner_id",
Integer,
ForeignKey("owners.id"),
primary_key=True),
Column(
"someoption",
sa.Boolean,
server_default=sa.false(),
nullable=False,
),
)
class Employee(Base):
__tablename__ = "employee"
id = Column(Integer, primary_key=True)
name = Column(String(100), nullable=False)
type = Column(String(50), nullable=False)
__mapper_args__ = {"polymorphic_on": type}
def setup(self):
self.table = Table(
"foos",
MetaData(),
Column("id", Integer, primary_key=True),
Column("bar", String(10)),
Column("baz", String(10)),
)
def _setup(self, test_collection_class):
"""Build a relationship situation using the given
test_collection_class factory"""
global metadata, slides_table, bullets_table, Slide, Bullet
slides_table = Table(
"test_Slides",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("name", String(128)),
)
bullets_table = Table(
"test_Bullets",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("slide_id", Integer, ForeignKey("test_Slides.id")),
Column("position", Integer),
Column("text", String(128)),
)
class Slide(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return '<Slide "%s">' % self.name
class Bullet(object):
def __init__(self, text):
self.text = text
def __repr__(self):
return '<Bullet "%s" pos %s>' % (self.text, self.position)
mapper(
Slide,
slides_table,
properties={
"bullets":
relationship(
Bullet,
lazy="joined",
collection_class=test_collection_class,
backref="slide",
order_by=[bullets_table.c.position],
)
},
)
mapper(Bullet, bullets_table)
metadata.create_all()
def define_tables(cls, metadata):
Table(
"foos",
metadata,
Column("id", Integer, primary_key=True, autoincrement=True),
Column("bar", String(10)),
Column("baz", String(10)),
Column("updated_once", Boolean, default=False),
)
def test_alias_pathing(self):
metadata = MetaData(self.engine)
a = Table(
"a",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column("bid", Integer, ForeignKey("b.id")),
Column("type", String(30)),
)
asub = Table(
"asub",
metadata,
Column("id", Integer, ForeignKey("a.id"), primary_key=True),
Column("data", String(30)),
)
b = Table(
"b",
metadata,
Column("id",
Integer,
primary_key=True,
test_needs_autoincrement=True),
)
mapper(A, a, polymorphic_identity="a", polymorphic_on=a.c.type)
mapper(ASub, asub, inherits=A, polymorphic_identity="asub")
mapper(B, b, properties={"as_": relationship(A)})
metadata.create_all()
sess = Session()
a1 = ASub(data="a1")
a2 = ASub(data="a2")
a3 = ASub(data="a3")
b1 = B(as_=[a1, a2, a3])
sess.add(b1)
sess.commit()
del sess
# sqlite has a slow enough growth here
# that we have to run it more times to see the
# "dip" again
@profile_memory(maxtimes=120)
def go():
sess = Session()
sess.query(B).options(subqueryload(B.as_.of_type(ASub))).all()
sess.close()
try:
go()
finally:
metadata.drop_all()
clear_mappers()
def define_tables(cls, metadata):
def gen_default(context):
pass
Table(
"some_table",
metadata,
Column("x", String(50), default=gen_default),
Column("y", String(50)),
)
def define_tables(cls, metadata):
foo = Table(
"foo",
metadata,
Column("a", String(30), primary_key=1),
Column("b", String(30), nullable=0),
)
cls.tables.bar = foo.select(foo.c.b == "bar").alias("bar")
cls.tables.baz = foo.select(foo.c.b == "baz").alias("baz")
def setup_class(cls):
global metadata, cattable, matchtable
metadata = MetaData(testing.db)
cattable = Table(
"cattable",
metadata,
Column("id", Integer, primary_key=True),
Column("description", String(50)),
)
matchtable = Table(
"matchtable",
metadata,
Column("id", Integer, primary_key=True),
Column("title", String(200)),
Column("category_id", Integer, ForeignKey("cattable.id")),
)
metadata.create_all()
cattable.insert().execute([
{
"id": 1,
"description": "Python"
},
{
"id": 2,
"description": "Ruby"
},
])
matchtable.insert().execute([
{
"id": 1,
"title": "Agile Web Development with Rails",
"category_id": 2,
},
{
"id": 2,
"title": "Dive Into Python",
"category_id": 1
},
{
"id": 3,
"title": "Programming Matz's Ruby",
"category_id": 2,
},
{
"id": 4,
"title": "The Definitive Guide to Django",
"category_id": 1,
},
{
"id": 5,
"title": "Python in a Nutshell",
"category_id": 1
},
])
def _dont_test_reflect_all_types_schema(self):
types_table = Table(
"all_types",
MetaData(testing.db),
Column("owner", String(30), primary_key=True),
Column("type_name", String(30), primary_key=True),
autoload=True,
oracle_resolve_synonyms=True,
)
for row in types_table.select().execute().fetchall():
[row[k] for k in row.keys()]
def define_tables(cls, metadata):
Table(
"foo",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("type", String(32)),
Column("data", String(30)),
Column("bar_data", String(30)),
)
