def test_year(self): """Exercise YEAR.""" year_table = Table( "mysql_year", self.metadata, Column("y1", mysql.MSYear), Column("y2", mysql.MSYear), Column("y3", mysql.MSYear), Column("y5", mysql.MSYear(4)), ) for col in year_table.c: self.assert_(repr(col)) year_table.create() reflected = Table("mysql_year", MetaData(testing.db), autoload=True) for table in year_table, reflected: with testing.db.connect() as conn: conn.execute(table.insert(["1950", "50", None, 1950])) row = conn.execute(table.select()).first() eq_(list(row), [1950, 2050, None, 1950]) conn.execute(table.delete()) self.assert_(colspec(table.c.y1).startswith("y1 YEAR")) eq_regex(colspec(table.c.y5), r"y5 YEAR(?:\(4\))?")
def test_precision_float_roundtrip(self): t = Table( "t", self.metadata, Column( "scale_value", mysql.DOUBLE(precision=15, scale=12, asdecimal=True), ), Column( "unscale_value", mysql.DOUBLE(decimal_return_scale=12, asdecimal=True), ), ) with testing.db.connect() as conn: t.create(conn) conn.execute( t.insert(), scale_value=45.768392065789, unscale_value=45.768392065789, ) result = conn.scalar(select([t.c.scale_value])) eq_(result, decimal.Decimal("45.768392065789")) result = conn.scalar(select([t.c.unscale_value])) eq_(result, decimal.Decimal("45.768392065789"))
def test_index_against_text_separate(self): metadata = MetaData() idx = Index("y", text("some_function(q)")) t = Table("x", metadata, Column("q", String(50))) t.append_constraint(idx) self.assert_compile(schema.CreateIndex(idx), "CREATE INDEX y ON x (some_function(q))")
def test_label_and_alias(self): # Lower case names, should not quote metadata = MetaData() table = Table("t1", metadata, Column("col1", Integer)) x = select([table.c.col1.label("label1")]).alias("alias1") self.assert_compile( select([x.c.label1]), "SELECT " "alias1.label1 " "FROM (" "SELECT " "t1.col1 AS label1 " "FROM t1" ") AS alias1", ) # Not lower case names, should quote metadata = MetaData() table = Table("T1", metadata, Column("Col1", Integer)) x = select([table.c.Col1.label("Label1")]).alias("Alias1") self.assert_compile( select([x.c.Label1]), "SELECT " '"Alias1"."Label1" ' "FROM (" "SELECT " '"T1"."Col1" AS "Label1" ' 'FROM "T1"' ') AS "Alias1"', )
def test_reflection_with_unique_constraint(self): insp = inspect(testing.db) meta = self.metadata uc_table = Table( "mysql_uc", meta, Column("a", String(10)), UniqueConstraint("a", name="uc_a"), ) uc_table.create() # MySQL converts unique constraints into unique indexes. # separately we get both indexes = dict((i["name"], i) for i in insp.get_indexes("mysql_uc")) constraints = set(i["name"] for i in insp.get_unique_constraints("mysql_uc")) self.assert_("uc_a" in indexes) self.assert_(indexes["uc_a"]["unique"]) self.assert_("uc_a" in constraints) # reflection here favors the unique index, as that's the # more "official" MySQL construct reflected = Table("mysql_uc", MetaData(testing.db), autoload=True) indexes = dict((i.name, i) for i in reflected.indexes) constraints = set(uc.name for uc in reflected.constraints) self.assert_("uc_a" in indexes) self.assert_(indexes["uc_a"].unique) self.assert_("uc_a" not in constraints)
def test_rowcount_flag(self): metadata = self.metadata engine = engines.testing_engine(options={"enable_rowcount": True}) assert engine.dialect.supports_sane_rowcount metadata.bind = engine t = Table("t1", metadata, Column("data", String(10))) metadata.create_all() r = t.insert().execute({"data": "d1"}, {"data": "d2"}, {"data": "d3"}) r = t.update().where(t.c.data == "d2").values(data="d3").execute() eq_(r.rowcount, 1) r = t.delete().where(t.c.data == "d3").execute() eq_(r.rowcount, 2) r = t.delete().execution_options(enable_rowcount=False).execute() eq_(r.rowcount, -1) engine.dispose() engine = engines.testing_engine(options={"enable_rowcount": False}) assert not engine.dialect.supports_sane_rowcount metadata.bind = engine r = t.insert().execute({"data": "d1"}, {"data": "d2"}, {"data": "d3"}) r = t.update().where(t.c.data == "d2").values(data="d3").execute() eq_(r.rowcount, -1) r = t.delete().where(t.c.data == "d3").execute() eq_(r.rowcount, -1) r = t.delete().execution_options(enable_rowcount=True).execute() eq_(r.rowcount, 1) r.close() engine.dispose()
def test_reflect_fulltext_comment(self): mt = Table( "mytable", self.metadata, Column("id", Integer, primary_key=True), Column("textdata", String(50)), mysql_engine="InnoDB", ) Index( "textdata_ix", mt.c.textdata, mysql_prefix="FULLTEXT", mysql_with_parser="ngram", ) self.metadata.create_all(testing.db) mt = Table("mytable", MetaData(), autoload_with=testing.db) idx = list(mt.indexes)[0] eq_(idx.name, "textdata_ix") eq_(idx.dialect_options["mysql"]["prefix"], "FULLTEXT") eq_(idx.dialect_options["mysql"]["with_parser"], "ngram") self.assert_compile( CreateIndex(idx), "CREATE FULLTEXT INDEX textdata_ix ON mytable " "(textdata) WITH PARSER ngram", )
def test_update_to_select_schema(self): meta = MetaData() table = Table( "sometable", meta, Column("sym", String), Column("val", Integer), schema="schema", ) other = Table("#other", meta, Column("sym", String), Column("newval", Integer)) stmt = table.update().values(val=select([other.c.newval]).where( table.c.sym == other.c.sym).as_scalar()) self.assert_compile( stmt, "UPDATE [schema].sometable SET val=" "(SELECT [#other].newval FROM [#other] " "WHERE [schema].sometable.sym = [#other].sym)", ) stmt = (table.update().values(val=other.c.newval).where( table.c.sym == other.c.sym)) self.assert_compile( stmt, "UPDATE [schema].sometable SET val=" "[#other].newval FROM [schema].sometable, " "[#other] WHERE [schema].sometable.sym = [#other].sym", )
def test_foreignkey_missing_insert(self): Table("t1", self.metadata, Column("id", Integer, primary_key=True)) t2 = Table( "t2", self.metadata, Column("id", Integer, ForeignKey("t1.id"), primary_key=True), ) self.metadata.create_all() # want to ensure that "null value in column "id" violates not- # null constraint" is raised (IntegrityError on psycoopg2, but # ProgrammingError on pg8000), and not "ProgrammingError: # (ProgrammingError) relationship "t2_id_seq" does not exist". # the latter corresponds to autoincrement behavior, which is not # the case here due to the foreign key. for eng in [ engines.testing_engine(options={"implicit_returning": False}), engines.testing_engine(options={"implicit_returning": True}), ]: with expect_warnings( ".*has no Python-side or server-side default.*"): assert_raises( (exc.IntegrityError, exc.ProgrammingError), eng.execute, t2.insert(), )
def test_unicode_enum(self): metadata = self.metadata t1 = Table( "table", metadata, Column("id", Integer, primary_key=True), Column("value", Enum(u("réveillé"), u("drôle"), u("S’il"))), Column("value2", mysql.ENUM(u("réveillé"), u("drôle"), u("S’il"))), ) metadata.create_all() t1.insert().execute(value=u("drôle"), value2=u("drôle")) t1.insert().execute(value=u("réveillé"), value2=u("réveillé")) t1.insert().execute(value=u("S’il"), value2=u("S’il")) eq_( t1.select().order_by(t1.c.id).execute().fetchall(), [ (1, u("drôle"), u("drôle")), (2, u("réveillé"), u("réveillé")), (3, u("S’il"), u("S’il")), ], ) # test reflection of the enum labels m2 = MetaData(testing.db) t2 = Table("table", m2, autoload=True) # TODO: what's wrong with the last element ? is there # latin-1 stuff forcing its way in ? eq_(t2.c.value.type.enums[0:2], [u("réveillé"), u("drôle")]) # u'S’il') # eh ? eq_(t2.c.value2.type.enums[0:2], [u("réveillé"), u("drôle")]) # u'S’il') # eh ?
def test_conflicting_backref_subclass(self): meta = MetaData() a = Table("a", meta, Column("id", Integer, primary_key=True)) b = Table( "b", meta, Column("id", Integer, primary_key=True), Column("a_id", Integer, ForeignKey("a.id")), ) class A(object): pass class B(object): pass class C(B): pass mapper( A, a, properties={ "b": relationship(B, backref="a"), "c": relationship(C, backref="a"), }, ) mapper(B, b) mapper(C, None, inherits=B) assert_raises_message(sa_exc.ArgumentError, "Error creating backref", configure_mappers)
def _baseline_1_create_tables(self): Table( "Zoo", self.metadata, Column( "ID", Integer, Sequence("zoo_id_seq"), primary_key=True, index=True, ), Column("Name", Unicode(255)), Column("Founded", Date), Column("Opens", Time), Column("LastEscape", DateTime), Column("Admission", Float), ) Table( "Animal", self.metadata, Column("ID", Integer, Sequence("animal_id_seq"), primary_key=True), Column("ZooID", Integer, ForeignKey("Zoo.ID"), index=True), Column("Name", Unicode(100)), Column("Species", Unicode(100)), Column("Legs", Integer, default=4), Column("LastEscape", DateTime), Column("Lifespan", Float(4)), Column("MotherID", Integer, ForeignKey("Animal.ID")), Column("PreferredFoodID", Integer), Column("AlternateFoodID", Integer), ) self.metadata.create_all()
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 test_preexecute_passivedefault(self): """test 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.""" 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 ); """ ) t = Table("speedy_users", meta, autoload=True) r = t.insert().execute(user_name="user", user_password="******") assert r.inserted_primary_key == [1] result = t.select().execute().fetchall() assert result == [(1, "user", "lala")] finally: testing.db.execute("drop table speedy_users")
def test_subquery_four(self): # Not lower case names, quotes off, should not quote metadata = MetaData() t1 = Table( "T1", metadata, Column("Col1", Integer, quote=False), schema="Foo", quote=False, quote_schema=False, ) a = t1.select().alias("Anon") b = select([1], a.c.Col1 == 2, from_obj=a) self.assert_compile( b, "SELECT 1 " "FROM (" "SELECT " "Foo.T1.Col1 AS Col1 " "FROM " "Foo.T1" ') AS "Anon" ' "WHERE " '"Anon".Col1 = :Col1_1', )
def test_select(self): t = Table("t", MetaData(), Column("x", Integer)) s = t.select() is_(inspect(s), s) assert s.is_selectable is_(s.selectable, s)
def test_checksfor_sequence(self): meta1 = self.metadata seq = Sequence("fooseq") t = Table("mytable", meta1, Column("col1", Integer, seq)) seq.drop() testing.db.execute("CREATE SEQUENCE fooseq") t.create(checkfirst=True)
def define_tables(cls, metadata): global foo, bar, foo_bar foo = Table( "foo", metadata, Column( "id", Integer, Sequence("foo_id_seq", optional=True), primary_key=True, ), Column("data", String(20)), ) bar = Table( "bar", metadata, Column("bid", Integer, ForeignKey("foo.id"), primary_key=True), ) foo_bar = Table( "foo_bar", metadata, Column("foo_id", Integer, ForeignKey("foo.id")), Column("bar_id", Integer, ForeignKey("bar.bid")), )
def test_use_alter(self): m = MetaData() Table("t", m, Column("a", Integer)) Table( "t2", m, Column("a", Integer, ForeignKey("t.a", use_alter=True, name="fk_ta")), Column("b", Integer, ForeignKey("t.a", name="fk_tb")), ) e = engines.mock_engine(dialect_name="postgresql") m.create_all(e) m.drop_all(e) e.assert_sql([ "CREATE TABLE t (a INTEGER)", "CREATE TABLE t2 (a INTEGER, b INTEGER, CONSTRAINT fk_tb " "FOREIGN KEY(b) REFERENCES t (a))", "ALTER TABLE t2 " "ADD CONSTRAINT fk_ta FOREIGN KEY(a) REFERENCES t (a)", "ALTER TABLE t2 DROP CONSTRAINT fk_ta", "DROP TABLE t2", "DROP TABLE t", ])
def define_tables(cls, metadata): Table( "people", metadata, Column("people_id", Integer, primary_key=True), Column("age", Integer), Column("name", String(30)), ) Table( "bookcases", metadata, Column("bookcase_id", Integer, primary_key=True), Column( "bookcase_owner_id", Integer, ForeignKey("people.people_id") ), Column("bookcase_shelves", Integer), Column("bookcase_width", Integer), ) Table( "books", metadata, Column("book_id", Integer, primary_key=True), Column( "bookcase_id", Integer, ForeignKey("bookcases.bookcase_id") ), Column("book_owner_id", Integer, ForeignKey("people.people_id")), Column("book_weight", Integer), )
def test_illegal_initial_char(self): # Create table with quote defaults metadata = MetaData() t1 = Table( "$table", metadata, Column("$column", Integer), schema="$schema" ) # Note that the names are quoted b/c the initial # character is in ['$','0', '1' ... '9'] result = 'CREATE TABLE "$schema"."$table" ("$column" INTEGER)' self.assert_compile(schema.CreateTable(t1), result) # Create the same table with quotes set to False now metadata = MetaData() t1 = Table( "$table", metadata, Column("$column", Integer, quote=False), schema="$schema", quote=False, quote_schema=False, ) # Note that the names are now unquoted result = "CREATE TABLE $schema.$table ($column INTEGER)" self.assert_compile(schema.CreateTable(t1), result)
def test_multiple(self): m = MetaData() Table( "foo", m, Column("id", Integer, primary_key=True), Column("bar", Integer, primary_key=True), ) tb = Table( "some_table", m, Column("id", Integer, primary_key=True), Column("foo_id", Integer, ForeignKey("foo.id")), Column("foo_bar", Integer, ForeignKey("foo.bar")), ) self.assert_compile( schema.CreateTable(tb), "CREATE TABLE some_table (" "id INTEGER NOT NULL, " "foo_id INTEGER, " "foo_bar INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(foo_id) REFERENCES foo (id), " "FOREIGN KEY(foo_bar) REFERENCES foo (bar))", )
def test_enum_parse(self): with testing.expect_deprecated("Manually quoting ENUM value literals"): enum_table = Table( "mysql_enum", self.metadata, Column("e1", mysql.ENUM("'a'")), Column("e2", mysql.ENUM("''")), Column("e3", mysql.ENUM("a")), Column("e4", mysql.ENUM("")), Column("e5", mysql.ENUM("'a'", "''")), Column("e6", mysql.ENUM("''", "'a'")), Column("e7", mysql.ENUM("''", "'''a'''", "'b''b'", "''''")), ) for col in enum_table.c: self.assert_(repr(col)) enum_table.create() reflected = Table("mysql_enum", MetaData(testing.db), autoload=True) for t in enum_table, reflected: eq_(t.c.e1.type.enums, ["a"]) eq_(t.c.e2.type.enums, [""]) eq_(t.c.e3.type.enums, ["a"]) eq_(t.c.e4.type.enums, [""]) eq_(t.c.e5.type.enums, ["a", ""]) eq_(t.c.e6.type.enums, ["", "a"]) eq_(t.c.e7.type.enums, ["", "'a'", "b'b", "'"])
def test_too_long_index_name(self): dialect = testing.db.dialect.__class__() for max_ident, max_index in [(22, None), (256, 22)]: dialect.max_identifier_length = max_ident dialect.max_index_name_length = max_index for tname, cname, exp in [ ("sometable", "this_name_is_too_long", "ix_sometable_t_09aa"), ("sometable", "this_name_alsois_long", "ix_sometable_t_3cf1"), ]: t1 = Table(tname, MetaData(), Column(cname, Integer, index=True)) ix1 = list(t1.indexes)[0] self.assert_compile( schema.CreateIndex(ix1), "CREATE INDEX %s " "ON %s (%s)" % (exp, tname, cname), dialect=dialect, ) dialect.max_identifier_length = 22 dialect.max_index_name_length = None t1 = Table("t", MetaData(), Column("c", Integer)) assert_raises( exc.IdentifierError, schema.CreateIndex( Index("this_other_name_is_too_long_for_what_were_doing", t1.c.c)).compile, dialect=dialect, )
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 test_cycle_named_fks(self): metadata = MetaData(testing.db) Table( "a", metadata, Column("id", Integer, primary_key=True), Column("bid", Integer, ForeignKey("b.id")), ) Table( "b", metadata, Column("id", Integer, primary_key=True), Column( "aid", Integer, ForeignKey("a.id", use_alter=True, name="aidfk"), ), ) assertions = [ AllOf( CompiledSQL("CREATE TABLE b (" "id INTEGER NOT NULL, " "aid INTEGER, " "PRIMARY KEY (id)" ")"), CompiledSQL("CREATE TABLE a (" "id INTEGER NOT NULL, " "bid INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(bid) REFERENCES b (id)" ")"), ), CompiledSQL("ALTER TABLE b ADD CONSTRAINT aidfk " "FOREIGN KEY(aid) REFERENCES a (id)"), ] with self.sql_execution_asserter() as asserter: metadata.create_all(checkfirst=False) if testing.db.dialect.supports_alter: asserter.assert_(*assertions) with self.sql_execution_asserter() as asserter: metadata.drop_all(checkfirst=False) asserter.assert_( CompiledSQL("ALTER TABLE b DROP CONSTRAINT aidfk"), AllOf(CompiledSQL("DROP TABLE b"), CompiledSQL("DROP TABLE a")), ) else: with self.sql_execution_asserter() as asserter: metadata.drop_all(checkfirst=False) asserter.assert_( AllOf(CompiledSQL("DROP TABLE b"), CompiledSQL("DROP TABLE a")))
def _constraint_create_fixture(self): m = MetaData() t = Table("tbl", m, Column("a", Integer), Column("b", Integer)) t2 = Table("t2", m, Column("a", Integer), Column("b", Integer)) return t, t2
def test_reflection(self): Table("mysql_json", self.metadata, Column("foo", mysql.JSON)) self.metadata.create_all() reflected = Table("mysql_json", MetaData(), autoload_with=testing.db) is_(reflected.c.foo.type._type_affinity, sqltypes.JSON) assert isinstance(reflected.c.foo.type, mysql.JSON)
def _table_seq_fixture(self): m = MetaData() s1 = Sequence("s1") s2 = Sequence("s2") t1 = Table("t1", m, Column("x", Integer, s1, primary_key=True)) t2 = Table("t2", m, Column("x", Integer, s2, primary_key=True)) return m, t1, t2, s1, s2
def test_dates(self): "Exercise type specification for date types." columns = [ # column type, args, kwargs, expected ddl (mssql.MSDateTime, [], {}, "DATETIME", []), (types.DATE, [], {}, "DATE", [">=", (10, )]), (types.Date, [], {}, "DATE", [">=", (10, )]), (types.Date, [], {}, "DATETIME", ["<", (10, )], mssql.MSDateTime), (mssql.MSDate, [], {}, "DATE", [">=", (10, )]), (mssql.MSDate, [], {}, "DATETIME", ["<", (10, )], mssql.MSDateTime), (types.TIME, [], {}, "TIME", [">=", (10, )]), (types.Time, [], {}, "TIME", [">=", (10, )]), (mssql.MSTime, [], {}, "TIME", [">=", (10, )]), (mssql.MSTime, [1], {}, "TIME(1)", [">=", (10, )]), (types.Time, [], {}, "DATETIME", ["<", (10, )], mssql.MSDateTime), (mssql.MSTime, [], {}, "TIME", [">=", (10, )]), (mssql.MSSmallDateTime, [], {}, "SMALLDATETIME", []), (mssql.MSDateTimeOffset, [], {}, "DATETIMEOFFSET", [">=", (10, )]), ( mssql.MSDateTimeOffset, [1], {}, "DATETIMEOFFSET(1)", [">=", (10, )], ), (mssql.MSDateTime2, [], {}, "DATETIME2", [">=", (10, )]), (mssql.MSDateTime2, [0], {}, "DATETIME2(0)", [">=", (10, )]), (mssql.MSDateTime2, [1], {}, "DATETIME2(1)", [">=", (10, )]), ] table_args = ["test_mssql_dates", metadata] for index, spec in enumerate(columns): type_, args, kw, res, requires = spec[0:5] if (requires and testing._is_excluded("mssql", *requires) or not requires): c = Column("c%s" % index, type_(*args, **kw), nullable=None) testing.db.dialect.type_descriptor(c.type) table_args.append(c) dates_table = Table(*table_args) gen = testing.db.dialect.ddl_compiler(testing.db.dialect, schema.CreateTable(dates_table)) for col in dates_table.c: index = int(col.name[1:]) testing.eq_( gen.get_column_specification(col), "%s %s" % (col.name, columns[index][3]), ) self.assert_(repr(col)) dates_table.create(checkfirst=True) reflected_dates = Table("test_mssql_dates", MetaData(testing.db), autoload=True) for col in reflected_dates.c: self.assert_types_base(col, dates_table.c[col.key])