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