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_reflect_nvarchar(self): metadata = self.metadata Table( "tnv", metadata, Column("nv_data", sqltypes.NVARCHAR(255)), Column("c_data", sqltypes.NCHAR(20)), ) metadata.create_all() m2 = MetaData(testing.db) t2 = Table("tnv", m2, autoload=True) assert isinstance(t2.c.nv_data.type, sqltypes.NVARCHAR) assert isinstance(t2.c.c_data.type, sqltypes.NCHAR) if testing.against("oracle+cx_oracle"): assert isinstance( t2.c.nv_data.type.dialect_impl(testing.db.dialect), cx_oracle._OracleUnicodeStringNCHAR, ) assert isinstance( t2.c.c_data.type.dialect_impl(testing.db.dialect), cx_oracle._OracleUnicodeStringNCHAR, ) data = u("m’a réveillé.") with testing.db.connect() as conn: conn.execute(t2.insert(), dict(nv_data=data, c_data=data)) nv_data, c_data = conn.execute(t2.select()).first() eq_(nv_data, data) eq_(c_data, data + (" " * 7)) # char is space padded assert isinstance(nv_data, util.text_type) assert isinstance(c_data, util.text_type)
def test_string_default_none_on_insert(self): """Test that without implicit returning, we return None for a string server default. That is, we don't want to attempt to pre-execute "server_default" generically - the user should use a Python side-default for a case like this. Testing that all backends do the same thing here. """ metadata = self.metadata t = Table( 'x', metadata, Column( 'y', String(10), server_default='key_one', primary_key=True), Column('data', String(10)), implicit_returning=False ) metadata.create_all() r = t.insert().execute(data='data') eq_(r.inserted_primary_key, [None]) eq_( t.select().execute().fetchall(), [('key_one', 'data')] )
def test_limit_offset_for_update(self): metadata = self.metadata # oracle can't actually do the ROWNUM thing with FOR UPDATE # very well. t = Table( "t1", metadata, Column("id", Integer, primary_key=True), Column("data", Integer), ) metadata.create_all() t.insert().execute( {"id": 1, "data": 1}, {"id": 2, "data": 7}, {"id": 3, "data": 12}, {"id": 4, "data": 15}, {"id": 5, "data": 32}, ) # here, we can't use ORDER BY. eq_( t.select().with_for_update().limit(2).execute().fetchall(), [(1, 1), (2, 7)], ) # here, its impossible. But we'd prefer it to raise ORA-02014 # instead of issuing a syntax error. assert_raises_message( exc.DatabaseError, "ORA-02014", t.select().with_for_update().limit(2).offset(3).execute, )
def test_select_doesnt_pollute_result(self): class MyType(TypeDecorator): impl = Integer def process_result_value(self, value, dialect): raise Exception("I have not been selected") t1 = Table( 't1', self.metadata, Column('x', MyType()) ) t2 = Table( 't2', self.metadata, Column('x', Integer) ) self.metadata.create_all(testing.db) with testing.db.connect() as conn: conn.execute(t1.insert().values(x=5)) stmt = t2.insert().values( x=select([t1.c.x]).as_scalar()).returning(t2.c.x) result = conn.execute(stmt) eq_(result.scalar(), 5)
def test_default_exec(self): metadata = MetaData(testing.db) t1 = Table( "t1", metadata, Column("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("special_col")]).select_from(t1).execute().close() assert isinstance(engine.dialect.identifier_preparer.format_sequence(Sequence("special_col")), str) # 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_misordered_lastrow(self): metadata = self.metadata related = Table( 'related', metadata, Column('id', Integer, primary_key=True), mysql_engine='MyISAM' ) t6 = Table( "t6", metadata, Column( 'manual_id', Integer, ForeignKey('related.id'), primary_key=True), Column( 'auto_id', Integer, primary_key=True, test_needs_autoincrement=True), mysql_engine='MyISAM' ) metadata.create_all() r = related.insert().values(id=12).execute() id_ = r.inserted_primary_key[0] eq_(id_, 12) r = t6.insert().values(manual_id=id_).execute() eq_(r.inserted_primary_key, [12, 1])
def test_column_label_overlap_fallback(self): content = Table( 'content', self.metadata, Column('type', String(30)), ) bar = Table( 'bar', self.metadata, Column('content_type', String(30)) ) self.metadata.create_all(testing.db) testing.db.execute(content.insert().values(type="t1")) row = testing.db.execute(content.select(use_labels=True)).first() in_(content.c.type, row) not_in_(bar.c.content_type, row) in_(sql.column('content_type'), row) row = testing.db.execute( select([content.c.type.label("content_type")])).first() in_(content.c.type, row) not_in_(bar.c.content_type, row) in_(sql.column('content_type'), row) row = testing.db.execute(select([func.now().label("content_type")])). \ first() not_in_(content.c.type, row) not_in_(bar.c.content_type, row) in_(sql.column('content_type'), row)
def test_table_overrides_metadata_create(self): metadata = self.metadata 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_long_type(self): metadata = self.metadata t = Table("t", metadata, Column("data", oracle.LONG)) metadata.create_all(testing.db) testing.db.execute(t.insert(), data="xyz") eq_(testing.db.scalar(select([t.c.data])), "xyz")
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 _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_fixed_char(self): m = MetaData(testing.db) t = Table('t1', m, Column('id', Integer, primary_key=True), Column('data', CHAR(30), nullable=False)) t.create() try: t.insert().execute( dict(id=1, data="value 1"), dict(id=2, data="value 2"), dict(id=3, data="value 3") ) eq_( t.select().where(t.c.data == 'value 2').execute().fetchall(), [(2, 'value 2 ')] ) m2 = MetaData(testing.db) t2 = Table('t1', m2, autoload=True) assert type(t2.c.data.type) is CHAR eq_( t2.select().where(t2.c.data == 'value 2').execute().fetchall(), [(2, 'value 2 ')] ) finally: t.drop()
def test_numeric_infinity_float(self): m = self.metadata t1 = Table('t1', m, Column("intcol", Integer), Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=False))) t1.create() t1.insert().execute([ dict( intcol=1, numericcol=float("inf") ), dict( intcol=2, numericcol=float("-inf") ), ]) eq_( select([t1.c.numericcol]). order_by(t1.c.intcol).execute().fetchall(), [(float('inf'), ), (float('-inf'), )] ) eq_( testing.db.execute( "select numericcol from t1 order by intcol").fetchall(), [(float('inf'), ), (float('-inf'), )] )
def _dont_test_numeric_nan_decimal(self): m = self.metadata t1 = Table('t1', m, Column("intcol", Integer), Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=True))) t1.create() t1.insert().execute([ dict( intcol=1, numericcol=decimal.Decimal("NaN") ), dict( intcol=2, numericcol=decimal.Decimal("-NaN") ), ]) eq_( select([t1.c.numericcol]). order_by(t1.c.intcol).execute().fetchall(), [(decimal.Decimal("NaN"), ), (decimal.Decimal("NaN"), )] ) eq_( testing.db.execute( "select numericcol from t1 order by intcol").fetchall(), [(decimal.Decimal("NaN"), ), (decimal.Decimal("NaN"), )] )
def _test_get_unique_constraints(self, schema=None): uniques = sorted( [ {"name": "unique_a", "column_names": ["a"]}, {"name": "unique_a_b_c", "column_names": ["a", "b", "c"]}, {"name": "unique_c_a_b", "column_names": ["c", "a", "b"]}, {"name": "unique_asc_key", "column_names": ["asc", "key"]}, ], key=operator.itemgetter("name"), ) orig_meta = self.metadata table = Table( "testtbl", orig_meta, Column("a", sa.String(20)), Column("b", sa.String(30)), Column("c", sa.Integer), # reserved identifiers Column("asc", sa.String(30)), Column("key", sa.String(30)), schema=schema, ) for uc in uniques: table.append_constraint(sa.UniqueConstraint(*uc["column_names"], name=uc["name"])) orig_meta.create_all() inspector = inspect(orig_meta.bind) reflected = sorted(inspector.get_unique_constraints("testtbl", schema=schema), key=operator.itemgetter("name")) for orig, refl in zip(uniques, reflected): # Different dialects handle duplicate index and constraints # differently, so ignore this flag refl.pop("duplicates_index", None) eq_(orig, refl)
def test_empty_insert(self): t1 = Table('t1', self.metadata, Column('is_true', Boolean, server_default=('1'))) self.metadata.create_all() t1.insert().execute() eq_(1, select([func.count(text('*'))], from_obj=t1).scalar()) eq_(True, t1.select().scalar())
def test_nullable_reflection(self): t = Table("t", self.metadata, Column("a", Integer, nullable=True), Column("b", Integer, nullable=False)) t.create() eq_( dict((col["name"], col["nullable"]) for col in inspect(self.metadata.bind).get_columns("t")), {"a": True, "b": False}, )
def test_int_default_none_on_insert_reflected(self): metadata = self.metadata 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_numeric_nan_float(self): m = self.metadata t1 = Table( "t1", m, Column("intcol", Integer), Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=False)), ) t1.create() t1.insert().execute( [ dict(intcol=1, numericcol=float("nan")), dict(intcol=2, numericcol=float("-nan")), ] ) eq_( [ tuple(str(col) for col in row) for row in select([t1.c.numericcol]) .order_by(t1.c.intcol) .execute() ], [("nan",), ("nan",)], ) eq_( [ tuple(str(col) for col in row) for row in testing.db.execute( "select numericcol from t1 order by intcol" ) ], [("nan",), ("nan",)], )
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 = self.metadata 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() 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.compare(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.compare(u3.c.id == a3.c.id)
def test_column_accessor_shadow(self): shadowed = Table( 'test_shadowed', self.metadata, Column('shadow_id', INT, primary_key=True), Column('shadow_name', VARCHAR(20)), Column('parent', VARCHAR(20)), Column('row', VARCHAR(40)), Column('_parent', VARCHAR(20)), Column('_row', VARCHAR(20)), ) self.metadata.create_all() shadowed.insert().execute( shadow_id=1, shadow_name='The Shadow', parent='The Light', row='Without light there is no shadow', _parent='Hidden parent', _row='Hidden row') r = shadowed.select(shadowed.c.shadow_id == 1).execute().first() eq_(r.shadow_id, 1) eq_(r['shadow_id'], 1) eq_(r[shadowed.c.shadow_id], 1) eq_(r.shadow_name, 'The Shadow') eq_(r['shadow_name'], 'The Shadow') eq_(r[shadowed.c.shadow_name], 'The Shadow') eq_(r.parent, 'The Light') eq_(r['parent'], 'The Light') eq_(r[shadowed.c.parent], 'The Light') eq_(r.row, 'Without light there is no shadow') eq_(r['row'], 'Without light there is no shadow') eq_(r[shadowed.c.row], 'Without light there is no shadow') eq_(r['_parent'], 'Hidden parent') eq_(r['_row'], 'Hidden row')
def _test_get_unique_constraints(self, schema=None): uniques = sorted( [ {'name': 'unique_a', 'column_names': ['a']}, {'name': 'unique_a_b_c', 'column_names': ['a', 'b', 'c']}, {'name': 'unique_c_a_b', 'column_names': ['c', 'a', 'b']}, {'name': 'unique_asc_key', 'column_names': ['asc', 'key']}, ], key=operator.itemgetter('name') ) orig_meta = self.metadata table = Table( 'testtbl', orig_meta, Column('a', sa.String(20)), Column('b', sa.String(30)), Column('c', sa.Integer), # reserved identifiers Column('asc', sa.String(30)), Column('key', sa.String(30)), schema=schema ) for uc in uniques: table.append_constraint( sa.UniqueConstraint(*uc['column_names'], name=uc['name']) ) orig_meta.create_all() inspector = inspect(orig_meta.bind) reflected = sorted( inspector.get_unique_constraints('testtbl', schema=schema), key=operator.itemgetter('name') ) for orig, refl in zip(uniques, reflected): eq_(orig, refl)
def test_reflect_remote_synonyms(self): meta = MetaData(testing.db) parent = Table( "ptable", meta, autoload=True, schema=testing.config.test_schema, oracle_resolve_synonyms=True, ) child = Table( "ctable", meta, autoload=True, schema=testing.config.test_schema, oracle_resolve_synonyms=True, ) self.assert_compile( parent.join(child), "%(test_schema)s.ptable JOIN " "%(test_schema)s.ctable " "ON %(test_schema)s.ptable.id = " "%(test_schema)s.ctable.parent_id" % {"test_schema": testing.config.test_schema}, ) select([parent, child]).select_from( parent.join(child) ).execute().fetchall()
def test_insert_from_select_fn_defaults(self): data = self._fixture() counter = itertools.count(1) def foo(ctx): return next(counter) table = Table('sometable', self.metadata, Column('x', Integer), Column('foo', Integer, default=foo), Column('y', Integer)) table.create() sel = select([data.c.x, data.c.y]) ins = table.insert().\ from_select(["x", "y"], sel) testing.db.execute(ins) # counter is only called once! eq_( testing.db.execute(table.select().order_by(table.c.x)).fetchall(), [(2, 1, 5), (7, 1, 12)] )
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 _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 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_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 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_long_type(self, metadata, connection): t = Table("t", metadata, Column("data", oracle.LONG)) metadata.create_all(connection) connection.execute(t.insert(), dict(data="xyz")) eq_(connection.scalar(select(t.c.data)), "xyz")
def define_tables(cls, metadata): Table('users', metadata, Column('id', Integer, primary_key=True), Column('name', String(64)))
def define_tables(cls, metadata): dt = Table( "dt", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("col1", String(20)), Column("col2", String(20), server_default=sa.schema.FetchedValue()), Column("col3", String(20), sa.schema.FetchedValue(for_update=True)), Column( "col4", String(20), sa.schema.FetchedValue(), sa.schema.FetchedValue(for_update=True), ), ) for ins in ( sa.DDL( "CREATE TRIGGER dt_ins AFTER INSERT ON dt " "FOR EACH ROW BEGIN " "UPDATE dt SET col2='ins', col4='ins' " "WHERE dt.id = NEW.id; END").execute_if(dialect="sqlite"), sa.DDL("CREATE TRIGGER dt_ins ON dt AFTER INSERT AS " "UPDATE dt SET col2='ins', col4='ins' " "WHERE dt.id IN (SELECT id FROM inserted);").execute_if( dialect="mssql"), sa.DDL( "CREATE TRIGGER dt_ins BEFORE INSERT " "ON dt " "FOR EACH ROW " "BEGIN " ":NEW.col2 := 'ins'; :NEW.col4 := 'ins'; END;").execute_if( dialect="oracle"), sa.DDL( "CREATE TRIGGER dt_ins BEFORE INSERT ON dt " "FOR EACH ROW BEGIN " "SET NEW.col2='ins'; SET NEW.col4='ins'; END").execute_if( callable_=lambda ddl, target, bind, **kw: bind.engine. name not in ("oracle", "mssql", "sqlite")), ): event.listen(dt, "after_create", ins) event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_ins")) for up in ( sa.DDL( "CREATE TRIGGER dt_up AFTER UPDATE ON dt " "FOR EACH ROW BEGIN " "UPDATE dt SET col3='up', col4='up' " "WHERE dt.id = OLD.id; END").execute_if(dialect="sqlite"), sa.DDL("CREATE TRIGGER dt_up ON dt AFTER UPDATE AS " "UPDATE dt SET col3='up', col4='up' " "WHERE dt.id IN (SELECT id FROM deleted);").execute_if( dialect="mssql"), sa.DDL( "CREATE TRIGGER dt_up BEFORE UPDATE ON dt " "FOR EACH ROW BEGIN " ":NEW.col3 := 'up'; :NEW.col4 := 'up'; END;").execute_if( dialect="oracle"), sa.DDL("CREATE TRIGGER dt_up BEFORE UPDATE ON dt " "FOR EACH ROW BEGIN " "SET NEW.col3='up'; SET NEW.col4='up'; END").execute_if( callable_=lambda ddl, target, bind, **kw: bind. engine.name not in ("oracle", "mssql", "sqlite")), ): event.listen(dt, "after_create", up) event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_up"))
def define_tables(cls, metadata): Table( "users", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("name", String(30), nullable=False), test_needs_acid=True, test_needs_fk=True, ) 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), test_needs_acid=True, test_needs_fk=True, ) Table( "email_bounces", metadata, Column("id", Integer, ForeignKey("addresses.id")), Column("bounces", Integer), ) Table( "orders", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("user_id", None, ForeignKey("users.id")), Column("address_id", None, ForeignKey("addresses.id")), Column("description", String(30)), Column("isopen", Integer), test_needs_acid=True, test_needs_fk=True, ) Table( "dingalings", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("address_id", None, ForeignKey("addresses.id")), Column("data", String(30)), test_needs_acid=True, test_needs_fk=True, ) Table( "items", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("description", String(30), nullable=False), test_needs_acid=True, test_needs_fk=True, ) Table( "order_items", metadata, Column("item_id", None, ForeignKey("items.id")), Column("order_id", None, ForeignKey("orders.id")), test_needs_acid=True, test_needs_fk=True, ) Table( "keywords", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("name", String(30), nullable=False), test_needs_acid=True, test_needs_fk=True, ) Table( "item_keywords", metadata, Column("item_id", None, ForeignKey("items.id")), Column("keyword_id", None, ForeignKey("keywords.id")), test_needs_acid=True, test_needs_fk=True, ) Table( "nodes", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("parent_id", Integer, ForeignKey("nodes.id")), Column("data", String(30)), test_needs_acid=True, test_needs_fk=True, ) Table( "composite_pk_table", metadata, Column("i", Integer, primary_key=True), Column("j", Integer, primary_key=True), Column("k", Integer, nullable=False), )
def define_tables(cls, metadata): global ta, tb, tc ta = ["a", metadata] ta.append( Column( "id", Integer, primary_key=True, test_needs_autoincrement=True, ) ), ta.append(Column("a_data", String(30))) if "a" == parent and direction == MANYTOONE: ta.append( Column( "child_id", Integer, ForeignKey( "%s.id" % child, use_alter=True, name="foo" ), ) ) elif "a" == child and direction == ONETOMANY: ta.append( Column( "parent_id", Integer, ForeignKey( "%s.id" % parent, use_alter=True, name="foo" ), ) ) ta = Table(*ta) tb = ["b", metadata] tb.append( Column("id", Integer, ForeignKey("a.id"), primary_key=True) ) tb.append(Column("b_data", String(30))) if "b" == parent and direction == MANYTOONE: tb.append( Column( "child_id", Integer, ForeignKey( "%s.id" % child, use_alter=True, name="foo" ), ) ) elif "b" == child and direction == ONETOMANY: tb.append( Column( "parent_id", Integer, ForeignKey( "%s.id" % parent, use_alter=True, name="foo" ), ) ) tb = Table(*tb) tc = ["c", metadata] tc.append( Column("id", Integer, ForeignKey("b.id"), primary_key=True) ) tc.append(Column("c_data", String(30))) if "c" == parent and direction == MANYTOONE: tc.append( Column( "child_id", Integer, ForeignKey( "%s.id" % child, use_alter=True, name="foo" ), ) ) elif "c" == child and direction == ONETOMANY: tc.append( Column( "parent_id", Integer, ForeignKey( "%s.id" % parent, use_alter=True, name="foo" ), ) ) tc = Table(*tc)
def test_with_manytomany(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)), ) table3 = Table('t1tot2', metadata, Column('t1', Integer, ForeignKey('mytable.col1')), Column('t2', Integer, ForeignKey('mytable2.col1')), ) @profile_memory() def go(): class A(fixtures.ComparableEntity): pass class B(fixtures.ComparableEntity): pass mapper(A, table1, properties={ 'bs':relationship(B, secondary=table3, backref='as', order_by=table3.c.t1) }) mapper(B, table2) sess = create_session() a1 = A(col2='a1') a2 = A(col2='a2') b1 = B(col2='b1') b2 = B(col2='b2') a1.bs.append(b1) a2.bs.append(b2) for x in [a1,a2]: sess.add(x) sess.flush() sess.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_( [ A(bs=[B(col2='b1')]), A(bs=[B(col2='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_orm_many_engines(self): metadata = MetaData(self.engine) 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")), ) metadata.create_all() m1 = mapper( A, table1, properties={ "bs": relationship(B, cascade="all, delete", order_by=table2.c.col1) }, _compiled_cache_size=50, ) m2 = mapper(B, table2, _compiled_cache_size=50) @profile_memory() def go(): engine = engines.testing_engine( options={ "logging_name": "FOO", "pool_logging_name": "BAR", "use_reaper": False, }) with Session(engine) as sess: 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.commit() 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.commit() engine.dispose() go() metadata.drop_all() del m1, m2 assert_no_mappers()
class DDLEventTest(fixtures.TestBase): def setup(self): self.bind = engines.mock_engine() self.metadata = MetaData() self.table = Table("t", self.metadata, Column("id", Integer)) def test_table_create_before(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, "before_create", canary.before_create) table.create(bind) table.drop(bind) eq_( canary.mock_calls, [ mock.call.before_create( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ) ], ) def test_table_create_after(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, "after_create", canary.after_create) table.create(bind) table.drop(bind) eq_( canary.mock_calls, [ mock.call.after_create( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ) ], ) def test_table_create_both(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, "before_create", canary.before_create) event.listen(table, "after_create", canary.after_create) table.create(bind) table.drop(bind) eq_( canary.mock_calls, [ mock.call.before_create( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ), mock.call.after_create( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ), ], ) def test_table_drop_before(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, "before_drop", canary.before_drop) table.create(bind) table.drop(bind) eq_( canary.mock_calls, [ mock.call.before_drop( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ) ], ) def test_table_drop_after(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, "after_drop", canary.after_drop) table.create(bind) canary.state = "skipped" table.drop(bind) eq_( canary.mock_calls, [ mock.call.after_drop( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ) ], ) def test_table_drop_both(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, "before_drop", canary.before_drop) event.listen(table, "after_drop", canary.after_drop) table.create(bind) table.drop(bind) eq_( canary.mock_calls, [ mock.call.before_drop( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ), mock.call.after_drop( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ), ], ) def test_table_all(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, "before_create", canary.before_create) event.listen(table, "after_create", canary.after_create) event.listen(table, "before_drop", canary.before_drop) event.listen(table, "after_drop", canary.after_drop) table.create(bind) table.drop(bind) eq_( canary.mock_calls, [ mock.call.before_create( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ), mock.call.after_create( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ), mock.call.before_drop( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ), mock.call.after_drop( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ), ], ) def test_metadata_create_before(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, "before_create", canary.before_create) metadata.create_all(bind) metadata.drop_all(bind) eq_( canary.mock_calls, [ mock.call.before_create( # checkfirst is False because of the MockConnection # used in the current testing strategy. metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY, ) ], ) def test_metadata_create_after(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, "after_create", canary.after_create) metadata.create_all(bind) metadata.drop_all(bind) eq_( canary.mock_calls, [ mock.call.after_create( metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY, ) ], ) def test_metadata_create_both(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, "before_create", canary.before_create) event.listen(metadata, "after_create", canary.after_create) metadata.create_all(bind) metadata.drop_all(bind) eq_( canary.mock_calls, [ mock.call.before_create( metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY, ), mock.call.after_create( metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY, ), ], ) def test_metadata_drop_before(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, "before_drop", canary.before_drop) metadata.create_all(bind) metadata.drop_all(bind) eq_( canary.mock_calls, [ mock.call.before_drop( metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY, ) ], ) def test_metadata_drop_after(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, "after_drop", canary.after_drop) metadata.create_all(bind) metadata.drop_all(bind) eq_( canary.mock_calls, [ mock.call.after_drop( metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY, ) ], ) def test_metadata_drop_both(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, "before_drop", canary.before_drop) event.listen(metadata, "after_drop", canary.after_drop) metadata.create_all(bind) metadata.drop_all(bind) eq_( canary.mock_calls, [ mock.call.before_drop( metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY, ), mock.call.after_drop( metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY, ), ], ) def test_metadata_table_isolation(self): metadata, table = self.metadata, self.table table_canary = mock.Mock() metadata_canary = mock.Mock() event.listen(table, "before_create", table_canary.before_create) event.listen(metadata, "before_create", metadata_canary.before_create) self.table.create(self.bind) eq_( table_canary.mock_calls, [ mock.call.before_create( table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY, ) ], ) eq_(metadata_canary.mock_calls, [])
def setup(self): self.bind = engines.mock_engine() self.metadata = MetaData() self.table = Table("t", self.metadata, Column("id", Integer))
def test_numeric_bind_in_crud(self): t = Table("asfd", self.metadata, Column("100K", Integer)) t.create() testing.db.execute(t.insert(), {"100K": 10}) eq_(testing.db.scalar(t.select()), 10)
def define_tables(cls, metadata): Table( "prj", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("created", sa.DateTime), Column("title", sa.String(100)), ) Table( "task", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column( "status_id", Integer, ForeignKey("task_status.id"), nullable=False, ), Column("title", sa.String(100)), Column( "task_type_id", Integer, ForeignKey("task_type.id"), nullable=False, ), Column("prj_id", Integer, ForeignKey("prj.id"), nullable=False), ) Table( "task_status", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), ) Table( "task_type", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), ) Table( "msg", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("posted", sa.DateTime, index=True), Column("type_id", Integer, ForeignKey("msg_type.id")), Column("task_id", Integer, ForeignKey("task.id")), ) Table( "msg_type", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("name", sa.String(20)), Column("display_name", sa.String(20)), )
def test_literal_binds_w_quotes(self): m = MetaData() t = Table("t", m, Column("x", Integer, server_default=literal("5 ' 8"))) self.assert_compile(CreateTable(t), """CREATE TABLE t (x INTEGER DEFAULT '5 '' 8')""")
def test_setinputsizes(self, metadata, datatype, value, sis_value_text, set_nchar_flag): if isinstance(sis_value_text, str): sis_value = getattr(testing.db.dialect.dbapi, sis_value_text) else: sis_value = sis_value_text class TestTypeDec(TypeDecorator): impl = NullType() cache_ok = True def load_dialect_impl(self, dialect): if dialect.name == "oracle": return dialect.type_descriptor(datatype) else: return self.impl m = metadata # Oracle can have only one column of type LONG so we make three # tables rather than one table w/ three columns t1 = Table("t1", m, Column("foo", datatype)) t2 = Table("t2", m, Column("foo", NullType().with_variant(datatype, "oracle"))) t3 = Table("t3", m, Column("foo", TestTypeDec())) class CursorWrapper: # cx_oracle cursor can't be modified so we have to # invent a whole wrapping scheme def __init__(self, connection_fairy): self.cursor = connection_fairy.connection.cursor() self.mock = mock.Mock() connection_fairy.info["mock"] = self.mock def setinputsizes(self, *arg, **kw): self.mock.setinputsizes(*arg, **kw) self.cursor.setinputsizes(*arg, **kw) def __getattr__(self, key): return getattr(self.cursor, key) if set_nchar_flag: engine = testing_engine(options={"use_nchar_for_unicode": True}) else: engine = testing.db with engine.connect() as conn: conn.begin() m.create_all(conn, checkfirst=False) connection_fairy = conn.connection for tab in [t1, t2, t3]: with mock.patch.object( connection_fairy, "cursor", lambda: CursorWrapper(connection_fairy), ): conn.execute(tab.insert(), {"foo": value}) if sis_value: eq_( conn.info["mock"].mock_calls, [mock.call.setinputsizes(foo=sis_value)], ) else: eq_( conn.info["mock"].mock_calls, [mock.call.setinputsizes()], )
def test_with_inheritance(self): metadata = MetaData() 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, ForeignKey("mytable.col1"), primary_key=True, test_needs_autoincrement=True, ), Column("col3", String(30)), ) @profile_memory() def go(): class A(fixtures.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(self.engine) 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() # don't need to clear_mappers() del B del A metadata.create_all(self.engine) try: go() finally: metadata.drop_all(self.engine) assert_no_mappers()
def test_mapper_reset(self): metadata = MetaData() 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(): mapper( A, table1, properties={"bs": relationship(B, order_by=table2.c.col1)}, ) mapper(B, table2) sess = create_session(self.engine) 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(self.engine) try: go() finally: metadata.drop_all(self.engine) assert_no_mappers()
def test_numeric_bind_in_crud(self, metadata, connection): t = Table("asfd", metadata, Column("100K", Integer)) t.create(connection) connection.execute(t.insert(), {"100K": 10}) eq_(connection.scalar(t.select()), 10)
def test_session(self): metadata = MetaData() 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")), ) metadata.create_all(self.engine) m1 = mapper( A, table1, properties={ "bs": relationship(B, cascade="all, delete", order_by=table2.c.col1) }, ) m2 = mapper(B, table2) @profile_memory() def go(): with Session(self.engine) as sess: 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.commit() 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.commit() go() metadata.drop_all(self.engine) del m1, m2 assert_no_mappers()
def test_explicit(self): engineers = Table( "engineers", Base.metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("name", String(50)), Column("primary_language", String(50)), ) managers = Table( "managers", Base.metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("name", String(50)), Column("golf_swing", String(50)), ) boss = Table( "boss", Base.metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("name", String(50)), Column("golf_swing", String(50)), ) punion = polymorphic_union( { "engineer": engineers, "manager": managers, "boss": boss }, "type", "punion", ) class Employee(Base, fixtures.ComparableEntity): __table__ = punion __mapper_args__ = {"polymorphic_on": punion.c.type} class Engineer(Employee): __table__ = engineers __mapper_args__ = { "polymorphic_identity": "engineer", "concrete": True, } class Manager(Employee): __table__ = managers __mapper_args__ = { "polymorphic_identity": "manager", "concrete": True, } class Boss(Manager): __table__ = boss __mapper_args__ = { "polymorphic_identity": "boss", "concrete": True, } self._roundtrip(Employee, Manager, Engineer, Boss)
def test_orm_many_engines(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"))) metadata.create_all() m1 = mapper(A, table1, properties={ "bs":relationship(B, cascade="all, delete", order_by=table2.c.col1)}, order_by=table1.c.col1, _compiled_cache_size=10 ) m2 = mapper(B, table2, _compiled_cache_size=10 ) m3 = mapper(A, table1, non_primary=True) @profile_memory() def go(): engine = engines.testing_engine( options={'logging_name':'FOO', 'pool_logging_name':'BAR', 'use_reaper':False} ) sess = create_session(bind=engine) 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).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() engine.dispose() go() metadata.drop_all() del m1, m2, m3 assert_no_mappers()
def define_tables(cls, metadata): global products_table, specification_table, documents_table global Product, Detail, Assembly, SpecLine, Document, RasterDocument products_table = Table( "products", metadata, Column( "product_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("product_type", String(128)), Column("name", String(128)), Column("mark", String(128)), ) specification_table = Table( "specification", metadata, Column( "spec_line_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column( "leader_id", Integer, ForeignKey("products.product_id"), nullable=True, ), Column( "follower_id", Integer, ForeignKey("products.product_id"), nullable=True, ), Column("quantity", Float, default=1.0), ) documents_table = Table( "documents", metadata, Column( "document_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("document_type", String(128)), Column("product_id", Integer, ForeignKey("products.product_id")), Column("create_date", DateTime, default=lambda: datetime.now()), Column( "last_updated", DateTime, default=lambda: datetime.now(), onupdate=lambda: datetime.now(), ), Column("name", String(128)), Column("data", LargeBinary), Column("size", Integer, default=0), ) class Product(object): def __init__(self, name, mark=""): self.name = name self.mark = mark def __repr__(self): return "<%s %s>" % (self.__class__.__name__, self.name) class Detail(Product): def __init__(self, name): self.name = name class Assembly(Product): def __repr__(self): return ( Product.__repr__(self) + " " + " ".join( [ x + "=" + repr(getattr(self, x, None)) for x in ["specification", "documents"] ] ) ) class SpecLine(object): def __init__(self, leader=None, follower=None, quantity=1): self.leader = leader self.follower = follower self.quantity = quantity def __repr__(self): return "<%s %.01f %s>" % ( self.__class__.__name__, self.quantity or 0.0, repr(self.follower), ) class Document(object): def __init__(self, name, data=None): self.name = name self.data = data def __repr__(self): return "<%s %s>" % (self.__class__.__name__, self.name) class RasterDocument(Document): pass
def define_tables(cls, metadata): dt = Table( "dt", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("col1", String(20)), Column( "col2", String(20), server_default=sa.schema.FetchedValue() ), Column( "col3", String(20), sa.schema.FetchedValue(for_update=True) ), Column( "col4", String(20), sa.schema.FetchedValue(), sa.schema.FetchedValue(for_update=True), ), ) dialect_name = testing.db.dialect.name for ins in ( sa.DDL( "CREATE TRIGGER dt_ins AFTER INSERT ON dt " "FOR EACH ROW BEGIN " "UPDATE dt SET col2='ins', col4='ins' " "WHERE dt.id = NEW.id; END" ).execute_if(dialect="sqlite"), sa.DDL( "CREATE TRIGGER dt_ins ON dt AFTER INSERT AS " "UPDATE dt SET col2='ins', col4='ins' " "WHERE dt.id IN (SELECT id FROM inserted);" ).execute_if(dialect="mssql"), sa.DDL( "CREATE TRIGGER dt_ins BEFORE INSERT " "ON dt " "FOR EACH ROW " "BEGIN " ":NEW.col2 := 'ins'; :NEW.col4 := 'ins'; END;" ).execute_if(dialect="oracle"), sa.DDL( "CREATE TRIGGER dt_ins BEFORE INSERT " "ON dt " "FOR EACH ROW " "EXECUTE PROCEDURE my_func_ins();" ).execute_if(dialect="postgresql"), sa.DDL( "CREATE TRIGGER dt_ins BEFORE INSERT ON dt " "FOR EACH ROW BEGIN " "SET NEW.col2='ins'; SET NEW.col4='ins'; END" ).execute_if( callable_=lambda ddl, target, bind, **kw: bind.engine.name not in ("oracle", "mssql", "sqlite", "postgresql") ), ): my_func_ins = sa.DDL( "CREATE OR REPLACE FUNCTION my_func_ins() " "RETURNS TRIGGER AS $$ " "BEGIN " "NEW.col2 := 'ins'; NEW.col4 := 'ins'; " "RETURN NEW; " "END; $$ LANGUAGE PLPGSQL" ).execute_if(dialect="postgresql") event.listen(dt, "after_create", my_func_ins) event.listen(dt, "after_create", ins) if dialect_name == "postgresql": event.listen( dt, "before_drop", sa.DDL("DROP TRIGGER dt_ins ON dt") ) else: event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_ins")) for up in ( sa.DDL( "CREATE TRIGGER dt_up AFTER UPDATE ON dt " "FOR EACH ROW BEGIN " "UPDATE dt SET col3='up', col4='up' " "WHERE dt.id = OLD.id; END" ).execute_if(dialect="sqlite"), sa.DDL( "CREATE TRIGGER dt_up ON dt AFTER UPDATE AS " "UPDATE dt SET col3='up', col4='up' " "WHERE dt.id IN (SELECT id FROM deleted);" ).execute_if(dialect="mssql"), sa.DDL( "CREATE TRIGGER dt_up BEFORE UPDATE ON dt " "FOR EACH ROW BEGIN " ":NEW.col3 := 'up'; :NEW.col4 := 'up'; END;" ).execute_if(dialect="oracle"), sa.DDL( "CREATE TRIGGER dt_up BEFORE UPDATE ON dt " "FOR EACH ROW " "EXECUTE PROCEDURE my_func_up();" ).execute_if(dialect="postgresql"), sa.DDL( "CREATE TRIGGER dt_up BEFORE UPDATE ON dt " "FOR EACH ROW BEGIN " "SET NEW.col3='up'; SET NEW.col4='up'; END" ).execute_if( callable_=lambda ddl, target, bind, **kw: bind.engine.name not in ("oracle", "mssql", "sqlite", "postgresql") ), ): my_func_up = sa.DDL( "CREATE OR REPLACE FUNCTION my_func_up() " "RETURNS TRIGGER AS $$ " "BEGIN " "NEW.col3 := 'up'; NEW.col4 := 'up'; " "RETURN NEW; " "END; $$ LANGUAGE PLPGSQL" ).execute_if(dialect="postgresql") event.listen(dt, "after_create", my_func_up) event.listen(dt, "after_create", up) if dialect_name == "postgresql": event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_up ON dt")) else: event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_up"))
def define_tables(cls, metadata): global managers_table, engineers_table, hackers_table global companies, employees_table companies = Table( "companies", metadata, Column("id", Integer, primary_key=True, test_needs_autoincrement=True), Column("name", String(50)), ) employees_table = Table( "employees", metadata, Column( "employee_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("name", String(50)), Column("company_id", Integer, ForeignKey("companies.id")), ) managers_table = Table( "managers", metadata, Column( "employee_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("name", String(50)), Column("manager_data", String(50)), Column("company_id", Integer, ForeignKey("companies.id")), ) engineers_table = Table( "engineers", metadata, Column( "employee_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("name", String(50)), Column("engineer_info", String(50)), Column("company_id", Integer, ForeignKey("companies.id")), ) hackers_table = Table( "hackers", metadata, Column( "employee_id", Integer, primary_key=True, test_needs_autoincrement=True, ), Column("name", String(50)), Column("engineer_info", String(50)), Column("company_id", Integer, ForeignKey("companies.id")), Column("nickname", String(50)), )
def setup(self): self.bind = engines.mock_engine() self.metadata = MetaData() self.table = Table('t', self.metadata, Column('id', Integer))
class DDLEventTest(fixtures.TestBase): def setup(self): self.bind = engines.mock_engine() self.metadata = MetaData() self.table = Table('t', self.metadata, Column('id', Integer)) def test_table_create_before(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, 'before_create', canary.before_create) table.create(bind) table.drop(bind) eq_(canary.mock_calls, [ mock.call.before_create(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY) ]) def test_table_create_after(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, 'after_create', canary.after_create) table.create(bind) table.drop(bind) eq_(canary.mock_calls, [ mock.call.after_create(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY) ]) def test_table_create_both(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, 'before_create', canary.before_create) event.listen(table, 'after_create', canary.after_create) table.create(bind) table.drop(bind) eq_(canary.mock_calls, [ mock.call.before_create(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY), mock.call.after_create(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY) ]) def test_table_drop_before(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, 'before_drop', canary.before_drop) table.create(bind) table.drop(bind) eq_(canary.mock_calls, [ mock.call.before_drop(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY), ]) def test_table_drop_after(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, 'after_drop', canary.after_drop) table.create(bind) canary.state = 'skipped' table.drop(bind) eq_(canary.mock_calls, [ mock.call.after_drop(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY), ]) def test_table_drop_both(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, 'before_drop', canary.before_drop) event.listen(table, 'after_drop', canary.after_drop) table.create(bind) table.drop(bind) eq_(canary.mock_calls, [ mock.call.before_drop(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY), mock.call.after_drop(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY), ]) def test_table_all(self): table, bind = self.table, self.bind canary = mock.Mock() event.listen(table, 'before_create', canary.before_create) event.listen(table, 'after_create', canary.after_create) event.listen(table, 'before_drop', canary.before_drop) event.listen(table, 'after_drop', canary.after_drop) table.create(bind) table.drop(bind) eq_(canary.mock_calls, [ mock.call.before_create(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY), mock.call.after_create(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY), mock.call.before_drop(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY), mock.call.after_drop(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY), ]) def test_metadata_create_before(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, 'before_create', canary.before_create) metadata.create_all(bind) metadata.drop_all(bind) eq_( canary.mock_calls, [ mock.call.before_create( # checkfirst is False because of the MockConnection # used in the current testing strategy. metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY), ]) def test_metadata_create_after(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, 'after_create', canary.after_create) metadata.create_all(bind) metadata.drop_all(bind) eq_(canary.mock_calls, [ mock.call.after_create(metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY), ]) def test_metadata_create_both(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, 'before_create', canary.before_create) event.listen(metadata, 'after_create', canary.after_create) metadata.create_all(bind) metadata.drop_all(bind) eq_(canary.mock_calls, [ mock.call.before_create(metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY), mock.call.after_create(metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY), ]) def test_metadata_drop_before(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, 'before_drop', canary.before_drop) metadata.create_all(bind) metadata.drop_all(bind) eq_(canary.mock_calls, [ mock.call.before_drop(metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY), ]) def test_metadata_drop_after(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, 'after_drop', canary.after_drop) metadata.create_all(bind) metadata.drop_all(bind) eq_(canary.mock_calls, [ mock.call.after_drop(metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY), ]) def test_metadata_drop_both(self): metadata, bind = self.metadata, self.bind canary = mock.Mock() event.listen(metadata, 'before_drop', canary.before_drop) event.listen(metadata, 'after_drop', canary.after_drop) metadata.create_all(bind) metadata.drop_all(bind) eq_(canary.mock_calls, [ mock.call.before_drop(metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY), mock.call.after_drop(metadata, self.bind, checkfirst=False, tables=list(metadata.tables.values()), _ddl_runner=mock.ANY), ]) def test_metadata_table_isolation(self): metadata, table = self.metadata, self.table table_canary = mock.Mock() metadata_canary = mock.Mock() event.listen(table, 'before_create', table_canary.before_create) event.listen(metadata, 'before_create', metadata_canary.before_create) self.table.create(self.bind) eq_(table_canary.mock_calls, [ mock.call.before_create(table, self.bind, checkfirst=False, _ddl_runner=mock.ANY, _is_metadata_operation=mock.ANY), ]) eq_(metadata_canary.mock_calls, []) def test_append_listener(self): metadata, table, bind = self.metadata, self.table, self.bind fn = lambda *a: None table.append_ddl_listener('before-create', fn) assert_raises(exc.InvalidRequestError, table.append_ddl_listener, 'blah', fn) metadata.append_ddl_listener('before-create', fn) assert_raises(exc.InvalidRequestError, metadata.append_ddl_listener, 'blah', fn)
class ReconnectRecipeTest(fixtures.TestBase): """Test for the reconnect recipe given at doc/build/faq/connections.rst. Make sure the above document is updated if changes are made here. """ # this recipe works on PostgreSQL also but only if the connection # is cut off from the server side, otherwise the connection.cursor() # method rightly fails because we explicitly closed the connection. # since we don't have a fixture # that can do this we currently rely on the MySQL drivers that allow # us to call cursor() even when the connection were closed. In order # to get a real "cut the server off" kind of fixture we'd need to do # something in provisioning that seeks out the TCP connection at the # OS level and kills it. __only_on__ = ("mysql+mysqldb", "mysql+pymysql") future = False def make_engine(self, engine): num_retries = 3 retry_interval = 0.5 def _run_with_retries(fn, context, cursor, statement, *arg, **kw): for retry in range(num_retries + 1): try: fn(cursor, statement, context=context, *arg) except engine.dialect.dbapi.Error as raw_dbapi_err: connection = context.root_connection if engine.dialect.is_disconnect( raw_dbapi_err, connection, cursor ): if retry > num_retries: raise engine.logger.error( "disconnection error, retrying operation", exc_info=True, ) connection.invalidate() if self.future: connection.rollback() else: trans = connection.get_transaction() if trans: trans.rollback() time.sleep(retry_interval) context.cursor = ( cursor ) = connection.connection.cursor() else: raise else: return True e = engine.execution_options(isolation_level="AUTOCOMMIT") @event.listens_for(e, "do_execute_no_params") def do_execute_no_params(cursor, statement, context): return _run_with_retries( context.dialect.do_execute_no_params, context, cursor, statement, ) @event.listens_for(e, "do_execute") def do_execute(cursor, statement, parameters, context): return _run_with_retries( context.dialect.do_execute, context, cursor, statement, parameters, ) return e __backend__ = True def setup_test(self): self.engine = engines.reconnecting_engine( options=dict(future=self.future) ) self.meta = MetaData() self.table = Table( "sometable", self.meta, Column("id", Integer, primary_key=True), Column("name", String(50)), ) self.meta.create_all(self.engine) def teardown_test(self): self.meta.drop_all(self.engine) self.engine.dispose() def test_restart_on_execute_no_txn(self): engine = self.make_engine(self.engine) with engine.connect() as conn: eq_(conn.execute(select(1)).scalar(), 1) self.engine.test_shutdown() self.engine.test_restart() eq_(conn.execute(select(1)).scalar(), 1) def test_restart_on_execute_txn(self): engine = self.make_engine(self.engine) with engine.begin() as conn: eq_(conn.execute(select(1)).scalar(), 1) self.engine.test_shutdown() self.engine.test_restart() eq_(conn.execute(select(1)).scalar(), 1) def test_autocommits_txn(self): engine = self.make_engine(self.engine) with engine.begin() as conn: conn.execute( self.table.insert(), [ {"id": 1, "name": "some name 1"}, {"id": 2, "name": "some name 2"}, {"id": 3, "name": "some name 3"}, ], ) self.engine.test_shutdown() self.engine.test_restart() eq_( conn.execute( select(self.table).order_by(self.table.c.id) ).fetchall(), [(1, "some name 1"), (2, "some name 2"), (3, "some name 3")], ) def test_fail_on_executemany_txn(self): engine = self.make_engine(self.engine) with engine.begin() as conn: conn.execute( self.table.insert(), [ {"id": 1, "name": "some name 1"}, {"id": 2, "name": "some name 2"}, {"id": 3, "name": "some name 3"}, ], ) self.engine.test_shutdown() self.engine.test_restart() assert_raises( exc.DBAPIError, conn.execute, self.table.insert(), [ {"id": 4, "name": "some name 4"}, {"id": 5, "name": "some name 5"}, {"id": 6, "name": "some name 6"}, ], ) if self.future: conn.rollback() else: trans = conn.get_transaction() trans.rollback()
def test_with_manytomany(self): metadata = MetaData() 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)), ) table3 = Table( "t1tot2", metadata, Column("t1", Integer, ForeignKey("mytable.col1")), Column("t2", Integer, ForeignKey("mytable2.col1")), ) @profile_memory() def go(): class A(fixtures.ComparableEntity): pass class B(fixtures.ComparableEntity): pass mapper( A, table1, properties={ "bs": relationship(B, secondary=table3, backref="as", order_by=table3.c.t1) }, ) mapper(B, table2) sess = create_session(self.engine) a1 = A(col2="a1") a2 = A(col2="a2") b1 = B(col2="b1") b2 = B(col2="b2") a1.bs.append(b1) a2.bs.append(b2) for x in [a1, a2]: sess.add(x) sess.flush() sess.expunge_all() alist = sess.query(A).order_by(A.col1).all() eq_([A(bs=[B(col2="b1")]), A(bs=[B(col2="b2")])], alist) for a in alist: sess.delete(a) sess.flush() # mappers necessarily find themselves in the compiled cache, # so to allow them to be GC'ed clear out the cache self.engine.clear_compiled_cache() del B del A metadata.create_all(self.engine) try: go() finally: metadata.drop_all(self.engine) assert_no_mappers()
def test_string_w_quotes(self): m = MetaData() t = Table("t", m, Column("x", Integer, server_default="5'6")) self.assert_compile(CreateTable(t), "CREATE TABLE t (x INTEGER DEFAULT '5''6')")
def test_numerics_broken_inspection(self, metadata, connection): """Numeric scenarios where Oracle type info is 'broken', returning us precision, scale of the form (0, 0) or (0, -127). We convert to Decimal and let int()/float() processors take over. """ # this test requires cx_oracle 5 foo = Table( "foo", metadata, Column("idata", Integer), Column("ndata", Numeric(20, 2)), Column("ndata2", Numeric(20, 2)), Column("nidata", Numeric(5, 0)), Column("fdata", Float()), ) foo.create(connection) connection.execute( foo.insert(), { "idata": 5, "ndata": decimal.Decimal("45.6"), "ndata2": decimal.Decimal("45.0"), "nidata": decimal.Decimal("53"), "fdata": 45.68392, }, ) stmt = "SELECT idata, ndata, ndata2, nidata, fdata FROM foo" row = exec_sql(connection, stmt).fetchall()[0] eq_( [type(x) for x in row], [int, decimal.Decimal, decimal.Decimal, int, float], ) eq_( row, ( 5, decimal.Decimal("45.6"), decimal.Decimal("45"), 53, 45.683920000000001, ), ) # with a nested subquery, # both Numeric values that don't have decimal places, regardless # of their originating type, come back as ints with no useful # typing information beyond "numeric". So native handler # must convert to int. # this means our Decimal converters need to run no matter what. # totally sucks. stmt = """ SELECT (SELECT (SELECT idata FROM foo) FROM DUAL) AS idata, (SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2)) FROM DUAL) AS ndata, (SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2)) FROM DUAL) AS ndata2, (SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0)) FROM DUAL) AS nidata, (SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL) AS fdata FROM dual """ row = exec_sql(connection, stmt).fetchall()[0] eq_( [type(x) for x in row], [int, decimal.Decimal, int, int, decimal.Decimal], ) eq_( row, (5, decimal.Decimal("45.6"), 45, 53, decimal.Decimal("45.68392")), ) row = connection.execute( text(stmt).columns( idata=Integer(), ndata=Numeric(20, 2), ndata2=Numeric(20, 2), nidata=Numeric(5, 0), fdata=Float(), )).fetchall()[0] eq_( [type(x) for x in row], [int, decimal.Decimal, decimal.Decimal, decimal.Decimal, float], ) eq_( row, ( 5, decimal.Decimal("45.6"), decimal.Decimal("45"), decimal.Decimal("53"), 45.683920000000001, ), ) stmt = """ SELECT anon_1.idata AS anon_1_idata, anon_1.ndata AS anon_1_ndata, anon_1.ndata2 AS anon_1_ndata2, anon_1.nidata AS anon_1_nidata, anon_1.fdata AS anon_1_fdata FROM (SELECT idata, ndata, ndata2, nidata, fdata FROM ( SELECT (SELECT (SELECT idata FROM foo) FROM DUAL) AS idata, (SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2)) FROM DUAL) AS ndata, (SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2)) FROM DUAL) AS ndata2, (SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0)) FROM DUAL) AS nidata, (SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL) AS fdata FROM dual ) WHERE ROWNUM >= 0) anon_1 """ row = exec_sql(connection, stmt).fetchall()[0] eq_( [type(x) for x in row], [int, decimal.Decimal, int, int, decimal.Decimal], ) eq_( row, (5, decimal.Decimal("45.6"), 45, 53, decimal.Decimal("45.68392")), ) row = connection.execute( text(stmt).columns( anon_1_idata=Integer(), anon_1_ndata=Numeric(20, 2), anon_1_ndata2=Numeric(20, 2), anon_1_nidata=Numeric(5, 0), anon_1_fdata=Float(), )).fetchall()[0] eq_( [type(x) for x in row], [int, decimal.Decimal, decimal.Decimal, decimal.Decimal, float], ) eq_( row, ( 5, decimal.Decimal("45.6"), decimal.Decimal("45"), decimal.Decimal("53"), 45.683920000000001, ), ) row = connection.execute( text(stmt).columns( anon_1_idata=Integer(), anon_1_ndata=Numeric(20, 2, asdecimal=False), anon_1_ndata2=Numeric(20, 2, asdecimal=False), anon_1_nidata=Numeric(5, 0, asdecimal=False), anon_1_fdata=Float(asdecimal=True), )).fetchall()[0] eq_([type(x) for x in row], [int, float, float, float, decimal.Decimal]) eq_(row, (5, 45.6, 45, 53, decimal.Decimal("45.68392")))
def define_tables(cls, metadata): Table('test_table', metadata, Column('id', Integer, primary_key=True), Column('data', String(50)))
def test_text_w_quotes(self): m = MetaData() t = Table("t", m, Column("x", Integer, server_default=text("5 ' 8"))) self.assert_compile(CreateTable(t), "CREATE TABLE t (x INTEGER DEFAULT 5 ' 8)")