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 _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_create_drop_bound(self): for meta in (MetaData,ThreadLocalMetaData): for bind in ( testing.db, testing.db.connect() ): metadata = meta() table = Table('test_table', metadata, Column('foo', Integer)) metadata.bind = bind assert metadata.bind is table.bind is bind metadata.create_all() assert table.exists() metadata.drop_all() table.create() table.drop() assert not table.exists() metadata = meta() table = Table('test_table', metadata, Column('foo', Integer)) metadata.bind = bind assert metadata.bind is table.bind is bind metadata.create_all() assert table.exists() metadata.drop_all() table.create() table.drop() assert not table.exists() if isinstance(bind, engine.Connection): bind.close()
def test_table_overrides_metadata_create(self): metadata = self.metadata s1 = Sequence("s1", metadata=metadata) s2 = Sequence("s2", metadata=metadata) s3 = Sequence("s3") t = Table('t', metadata, Column('c', Integer, s3, primary_key=True)) assert s3.metadata is metadata t.create(testing.db) s3.drop(testing.db) # 't' is created, and 's3' won't be # re-created since it's linked to 't'. # 's1' and 's2' are, however. metadata.create_all(testing.db) assert self._has_sequence('s1') assert self._has_sequence('s2') assert not self._has_sequence('s3') s2.drop(testing.db) assert self._has_sequence('s1') assert not self._has_sequence('s2') metadata.drop_all(testing.db) assert not self._has_sequence('s1') assert not self._has_sequence('s2')
def test_table_overrides_metadata_create(self): metadata = self.metadata s1 = Sequence("s1", metadata=metadata) s2 = Sequence("s2", metadata=metadata) s3 = Sequence("s3") t = Table('t', metadata, Column('c', Integer, s3, primary_key=True)) assert s3.metadata is metadata t.create(testing.db, checkfirst=True) s3.drop(testing.db) # 't' is created, and 's3' won't be # re-created since it's linked to 't'. # 's1' and 's2' are, however. metadata.create_all(testing.db) assert self._has_sequence('s1') assert self._has_sequence('s2') assert not self._has_sequence('s3') s2.drop(testing.db) assert self._has_sequence('s1') assert not self._has_sequence('s2') metadata.drop_all(testing.db) assert not self._has_sequence('s1') assert not self._has_sequence('s2')
def _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 _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_create_drop_bound(self): for meta in (MetaData, ThreadLocalMetaData): for bind in (testing.db, testing.db.connect()): metadata = meta() table = Table('test_table', metadata, Column('foo', Integer)) metadata.bind = bind assert metadata.bind is table.bind is bind metadata.create_all() assert table.exists() metadata.drop_all() table.create() table.drop() assert not table.exists() metadata = meta() table = Table('test_table', metadata, Column('foo', Integer)) metadata.bind = bind assert metadata.bind is table.bind is bind metadata.create_all() assert table.exists() metadata.drop_all() table.create() table.drop() assert not table.exists() if isinstance(bind, engine.Connection): bind.close()
def setup(self): meta = MetaData(testing.db) global table, seq seq = Sequence('tid_seq') table = Table('tables', meta, Column('id', Integer, seq, primary_key=True), Column('data', String(50))) table.create(checkfirst=True)
def setup_class(cls): global counters, metadata metadata = MetaData() counters = Table('forupdate_counters', metadata, Column('counter_id', INT, primary_key=True), Column('counter_value', INT), test_needs_acid=True) counters.create(testing.db)
def test_autoincrement_single_col(self): single = Table("single", self.metadata, Column("id", Integer, primary_key=True)) single.create() r = single.insert().execute() id_ = r.inserted_primary_key[0] eq_(id_, 1) eq_(1, sa.select([func.count(sa.text("*"))], from_obj=single).scalar())
def test_func_embedded_valuesbase(self): """test can use next_value() in values() of _ValuesBase""" metadata = self.metadata t1 = Table("t", metadata, Column("x", Integer)) t1.create(testing.db) s = Sequence("my_sequence") testing.db.execute(t1.insert().values(x=s.next_value())) self._assert_seq_result(testing.db.scalar(t1.select()))
def test_func_embedded_whereclause(self): """test can use next_value() in whereclause""" metadata = self.metadata t1 = Table("t", metadata, Column("x", Integer)) t1.create(testing.db) testing.db.execute(t1.insert(), [{"x": 1}, {"x": 300}, {"x": 301}]) s = Sequence("my_sequence") eq_(testing.db.execute(t1.select().where(t1.c.x > s.next_value())).fetchall(), [(300,), (301,)])
def setup(self): meta = MetaData(testing.db) global table table = Table('tables', meta, Column('id', Integer, primary_key=True, key='foo_id', test_needs_autoincrement=True), Column('data', String(20)), ) table.create(checkfirst=True)
def setup(self): meta = MetaData(testing.db) global table, seq seq = Sequence('tid_seq') table = Table('tables', meta, Column('id', Integer, seq, primary_key=True), Column('data', String(50)) ) table.create(checkfirst=True)
def setup_class(cls): global users, metadata metadata = MetaData() users = Table('query_users', metadata, Column('user_id', INT, primary_key = True), Column('user_name', VARCHAR(20)), test_needs_acid=True, ) users.create(testing.db)
def test_autoincrement_single_col(self): single = Table('single', self.metadata, Column('id', Integer, primary_key=True)) single.create() r = single.insert().execute() id_ = r.inserted_primary_key[0] eq_(id_, 1) eq_(1, sa.select([func.count(sa.text('*'))], from_obj=single).scalar())
def test_func_embedded_valuesbase(self): """test can use next_value() in values() of _ValuesBase""" metadata = self.metadata t1 = Table('t', metadata, Column('x', Integer)) t1.create(testing.db) s = Sequence("my_sequence") testing.db.execute(t1.insert().values(x=s.next_value())) self._assert_seq_result(testing.db.scalar(t1.select()))
def test_checkfirst_table(self): m = MetaData() s = Sequence("my_sequence") t = Table('t', m, Column('c', Integer, s, primary_key=True)) t.create(testing.db, checkfirst=False) assert self._has_sequence('my_sequence') t.create(testing.db, checkfirst=True) t.drop(testing.db, checkfirst=False) assert not self._has_sequence('my_sequence') t.drop(testing.db, checkfirst=True)
def test_autoincrement_fk(self): nodes = Table('nodes', self.metadata, Column('id', Integer, primary_key=True), Column('parent_id', Integer, ForeignKey('nodes.id')), Column('data', String(30))) nodes.create() r = nodes.insert().execute(data='foo') id_ = r.inserted_primary_key[0] nodes.insert().execute(data='bar', parent_id=id_)
def test_insert_no_pk(self): t = Table( "some_other_large_named_table", self.metadata, Column("this_is_the_primarykey_column", Integer, Sequence("this_is_some_large_seq"), primary_key=True), Column("this_is_the_data_column", String(30))) t.create(testing.db, checkfirst=True) testing.db.execute(t.insert(), **{"this_is_the_data_column": "data1"})
def test_insert_no_pk(self): t = Table("some_other_large_named_table", self.metadata, Column("this_is_the_primarykey_column", Integer, Sequence("this_is_some_large_seq"), primary_key=True), Column("this_is_the_data_column", String(30)) ) t.create(testing.db, checkfirst=True) testing.db.execute(t.insert(), **{"this_is_the_data_column":"data1"})
def setup_class(cls): global users, metadata metadata = MetaData() users = Table( 'query_users', metadata, Column('user_id', INT, primary_key=True), Column('user_name', VARCHAR(20)), test_needs_acid=True, ) users.create(testing.db)
def test_create_drop_explicit(self): metadata = MetaData() table = Table('test_table', metadata, Column('foo', Integer)) for bind in (testing.db, testing.db.connect()): for args in [([], {'bind': bind}), ([bind], {})]: metadata.create_all(*args[0], **args[1]) assert table.exists(*args[0], **args[1]) metadata.drop_all(*args[0], **args[1]) table.create(*args[0], **args[1]) table.drop(*args[0], **args[1]) assert not table.exists(*args[0], **args[1])
def test_inserted_pk_implicit_returning(self): """test inserted_primary_key contains the result when pk_col=next_value(), when implicit returning is used.""" metadata = self.metadata e = engines.testing_engine(options={"implicit_returning": True}) s = Sequence("my_sequence") metadata.bind = e t1 = Table("t", metadata, Column("x", Integer, primary_key=True)) t1.create() r = e.execute(t1.insert().values(x=s.next_value())) self._assert_seq_result(r.inserted_primary_key[0])
def test_inserted_pk_implicit_returning(self): """test inserted_primary_key contains the result when pk_col=next_value(), when implicit returning is used.""" metadata = self.metadata e = engines.testing_engine(options={'implicit_returning': True}) s = Sequence("my_sequence") metadata.bind = e t1 = Table('t', metadata, Column('x', Integer, primary_key=True)) t1.create() r = e.execute(t1.insert().values(x=s.next_value())) self._assert_seq_result(r.inserted_primary_key[0])
def test_inserted_pk_no_returning(self): """test inserted_primary_key contains [None] when pk_col=next_value(), implicit returning is not used.""" metadata = self.metadata e = engines.testing_engine(options={"implicit_returning": False}) s = Sequence("my_sequence") metadata.bind = e t1 = Table("t", metadata, Column("x", Integer, primary_key=True)) t1.create() r = e.execute(t1.insert().values(x=s.next_value())) eq_(r.inserted_primary_key, [None])
def test_inserted_pk_no_returning(self): """test inserted_primary_key contains [None] when pk_col=next_value(), implicit returning is not used.""" metadata = self.metadata e = engines.testing_engine(options={'implicit_returning': False}) s = Sequence("my_sequence") metadata.bind = e t1 = Table('t', metadata, Column('x', Integer, primary_key=True)) t1.create() r = e.execute(t1.insert().values(x=s.next_value())) eq_(r.inserted_primary_key, [None])
def test_func_embedded_whereclause(self): """test can use next_value() in whereclause""" metadata = self.metadata t1 = Table('t', metadata, Column('x', Integer)) t1.create(testing.db) testing.db.execute(t1.insert(), [{'x': 1}, {'x': 300}, {'x': 301}]) s = Sequence("my_sequence") eq_( testing.db.execute( t1.select().where(t1.c.x > s.next_value())).fetchall(), [(300, ), (301, )])
def test_autoincrement_fk(self): nodes = Table( "nodes", self.metadata, Column("id", Integer, primary_key=True), Column("parent_id", Integer, ForeignKey("nodes.id")), Column("data", String(30)), ) nodes.create() r = nodes.insert().execute(data="foo") id_ = r.inserted_primary_key[0] nodes.insert().execute(data="bar", parent_id=id_)
def setup(self): meta = MetaData(testing.db) global table table = Table( 'tables', meta, Column('id', Integer, primary_key=True, key='foo_id', test_needs_autoincrement=True), Column('data', String(20)), ) table.create(checkfirst=True)
def test_non_autoincrement(self): # sqlite INT primary keys can be non-unique! (only for ints) nonai = Table("nonaitest", self.metadata, Column('id', Integer, autoincrement=False, primary_key=True), Column('data', String(20))) nonai.create() try: # postgresql + mysql strict will fail on first row, # mysql in legacy mode fails on second row nonai.insert().execute(data='row 1') nonai.insert().execute(data='row 2') assert False except sa.exc.DBAPIError, e: assert True
def test_non_autoincrement(self): # sqlite INT primary keys can be non-unique! (only for ints) nonai = Table( "nonaitest", self.metadata, Column('id', Integer, autoincrement=False, primary_key=True), Column('data', String(20))) nonai.create() try: # postgresql + mysql strict will fail on first row, # mysql in legacy mode fails on second row nonai.insert().execute(data='row 1') nonai.insert().execute(data='row 2') assert False except sa.exc.DBAPIError, e: assert True
def test_create_drop_explicit(self): metadata = MetaData() table = Table('test_table', metadata, Column('foo', Integer)) for bind in ( testing.db, testing.db.connect() ): for args in [ ([], {'bind':bind}), ([bind], {}) ]: metadata.create_all(*args[0], **args[1]) assert table.exists(*args[0], **args[1]) metadata.drop_all(*args[0], **args[1]) table.create(*args[0], **args[1]) table.drop(*args[0], **args[1]) assert not table.exists(*args[0], **args[1])
def test_rollback_deadlock(self): """test that returning connections to the pool clears any object locks.""" conn1 = testing.db.connect() conn2 = testing.db.connect() users = Table('deadlock_users', metadata, Column('user_id', INT, primary_key=True), Column('user_name', VARCHAR(20)), test_needs_acid=True) users.create(conn1) conn1.execute('select * from deadlock_users') conn1.close() # without auto-rollback in the connection pool's return() logic, # this deadlocks in PostgreSQL, because conn1 is returned to the # pool but still has a lock on "deadlock_users". comment out the # rollback in pool/ConnectionFairy._close() to see ! users.drop(conn2) conn2.close()
def test_row_c_sequence_check(self): import csv import collections from StringIO import StringIO metadata = MetaData() metadata.bind = 'sqlite://' users = Table('users', metadata, Column('id', Integer, primary_key=True), Column('name', String(40)), ) users.create() users.insert().execute(name='Test') row = users.select().execute().fetchone() s = StringIO() writer = csv.writer(s) # csv performs PySequenceCheck call writer.writerow(row) assert s.getvalue().strip() == '1,Test'
def test_create_drop_constructor_bound(self): for bind in (testing.db, testing.db.connect()): try: for args in ( ([bind], {}), ([], { 'bind': bind }), ): metadata = MetaData(*args[0], **args[1]) table = Table('test_table', metadata, Column('foo', Integer)) assert metadata.bind is table.bind is bind metadata.create_all() assert table.exists() metadata.drop_all() table.create() table.drop() assert not table.exists() finally: if isinstance(bind, engine.Connection): bind.close()
def test_create_drop_constructor_bound(self): for bind in ( testing.db, testing.db.connect() ): try: for args in ( ([bind], {}), ([], {'bind':bind}), ): metadata = MetaData(*args[0], **args[1]) table = Table('test_table', metadata, Column('foo', Integer)) assert metadata.bind is table.bind is bind metadata.create_all() assert table.exists() metadata.drop_all() table.create() table.drop() assert not table.exists() finally: if isinstance(bind, engine.Connection): bind.close()
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)
class DDLEventTest(fixtures.TestBase): class Canary(object): def __init__(self, schema_item, bind): self.state = None self.schema_item = schema_item self.bind = bind def before_create(self, schema_item, bind, **kw): assert self.state is None assert schema_item is self.schema_item assert bind is self.bind self.state = 'before-create' def after_create(self, schema_item, bind, **kw): assert self.state in ('before-create', 'skipped') assert schema_item is self.schema_item assert bind is self.bind self.state = 'after-create' def before_drop(self, schema_item, bind, **kw): assert self.state is None assert schema_item is self.schema_item assert bind is self.bind self.state = 'before-drop' def after_drop(self, schema_item, bind, **kw): assert self.state in ('before-drop', 'skipped') assert schema_item is self.schema_item assert bind is self.bind self.state = 'after-drop' 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 = self.Canary(table, bind) event.listen(table, 'before_create', canary.before_create) table.create(bind) assert canary.state == 'before-create' table.drop(bind) assert canary.state == 'before-create' def test_table_create_after(self): table, bind = self.table, self.bind canary = self.Canary(table, bind) event.listen(table, 'after_create', canary.after_create) canary.state = 'skipped' table.create(bind) assert canary.state == 'after-create' table.drop(bind) assert canary.state == 'after-create' def test_table_create_both(self): table, bind = self.table, self.bind canary = self.Canary(table, bind) event.listen(table, 'before_create', canary.before_create) event.listen(table, 'after_create', canary.after_create) table.create(bind) assert canary.state == 'after-create' table.drop(bind) assert canary.state == 'after-create' def test_table_drop_before(self): table, bind = self.table, self.bind canary = self.Canary(table, bind) event.listen(table, 'before_drop', canary.before_drop) table.create(bind) assert canary.state is None table.drop(bind) assert canary.state == 'before-drop' def test_table_drop_after(self): table, bind = self.table, self.bind canary = self.Canary(table, bind) event.listen(table, 'after_drop', canary.after_drop) table.create(bind) assert canary.state is None canary.state = 'skipped' table.drop(bind) assert canary.state == 'after-drop' def test_table_drop_both(self): table, bind = self.table, self.bind canary = self.Canary(table, bind) event.listen(table, 'before_drop', canary.before_drop) event.listen(table, 'after_drop', canary.after_drop) table.create(bind) assert canary.state is None table.drop(bind) assert canary.state == 'after-drop' def test_table_all(self): table, bind = self.table, self.bind canary = self.Canary(table, bind) 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) assert canary.state is None table.create(bind) assert canary.state == 'after-create' canary.state = None table.drop(bind) assert canary.state == 'after-drop' def test_table_create_before(self): metadata, bind = self.metadata, self.bind canary = self.Canary(metadata, bind) event.listen(metadata, 'before_create', canary.before_create) metadata.create_all(bind) assert canary.state == 'before-create' metadata.drop_all(bind) assert canary.state == 'before-create' def test_metadata_create_after(self): metadata, bind = self.metadata, self.bind canary = self.Canary(metadata, bind) event.listen(metadata, 'after_create', canary.after_create) canary.state = 'skipped' metadata.create_all(bind) assert canary.state == 'after-create' metadata.drop_all(bind) assert canary.state == 'after-create' def test_metadata_create_both(self): metadata, bind = self.metadata, self.bind canary = self.Canary(metadata, bind) event.listen(metadata, 'before_create', canary.before_create) event.listen(metadata, 'after_create', canary.after_create) metadata.create_all(bind) assert canary.state == 'after-create' metadata.drop_all(bind) assert canary.state == 'after-create' def test_metadata_table_isolation(self): metadata, table, bind = self.metadata, self.table, self.bind table_canary = self.Canary(table, bind) event.listen(table, 'before_create', table_canary.before_create) metadata_canary = self.Canary(metadata, bind) event.listen(metadata, 'before_create', metadata_canary.before_create) self.table.create(self.bind) assert metadata_canary.state == None 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)
def setup_class(cls): global t, f, f2, ts, currenttime, metadata, default_generator db = testing.db metadata = MetaData(db) default_generator = {'x':50} def mydefault(): default_generator['x'] += 1 return default_generator['x'] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text('13')])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection try: return conn.execute(sa.select([sa.text('12')])).scalar() finally: # ensure a "close()" on this connection does nothing, # since its a "branched" connection conn.close() use_function_defaults = testing.against('postgresql', 'mssql', 'maxdb') is_oracle = testing.against('oracle') # select "count(1)" returns different results on different DBs also # correct for "current_date" compatible as column default, value # differences currenttime = func.current_date(type_=sa.Date, bind=db) if is_oracle: ts = db.scalar(sa.select([func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label('today')])) assert isinstance(ts, datetime.date) and not isinstance(ts, datetime.datetime) f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() # TODO: engine propigation across nested functions not working currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date) def1 = currenttime def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date) deftype = sa.Date elif use_function_defaults: f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() def1 = currenttime deftype = sa.Date if testing.against('maxdb'): def2 = sa.text("curdate") elif testing.against('mssql'): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = db.scalar(func.current_date()) else: f = len('abcdef') f2 = len('abcdefghijk') def1 = def2 = "3" ts = 3 deftype = Integer t = Table('default_test1', metadata, # python function Column('col1', Integer, primary_key=True, default=mydefault), # python literal Column('col2', String(20), default="imthedefault", onupdate="im the update"), # preexecute expression Column('col3', Integer, default=func.length('abcdef'), onupdate=func.length('abcdefghijk')), # SQL-side default from sql expression Column('col4', deftype, server_default=def1), # SQL-side default from literal expression Column('col5', deftype, server_default=def2), # preexecute + update timestamp Column('col6', sa.Date, default=currenttime, onupdate=currenttime), Column('boolcol1', sa.Boolean, default=True), Column('boolcol2', sa.Boolean, default=False), # python function which uses ExecutionContext Column('col7', Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx), # python builtin Column('col8', sa.Date, default=datetime.date.today, onupdate=datetime.date.today), # combo Column('col9', String(20), default='py', server_default='ddl')) t.create()
def setup_class(cls): global t, f, f2, ts, currenttime, metadata, default_generator db = testing.db metadata = MetaData(db) default_generator = {'x': 50} def mydefault(): default_generator['x'] += 1 return default_generator['x'] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text('13')])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection try: return conn.execute(sa.select([sa.text('12')])).scalar() finally: # ensure a "close()" on this connection does nothing, # since its a "branched" connection conn.close() use_function_defaults = testing.against('postgresql', 'mssql', 'maxdb') is_oracle = testing.against('oracle') # select "count(1)" returns different results on different DBs also # correct for "current_date" compatible as column default, value # differences currenttime = func.current_date(type_=sa.Date, bind=db) if is_oracle: ts = db.scalar( sa.select([ func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label('today') ])) assert isinstance( ts, datetime.date) and not isinstance(ts, datetime.datetime) f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() # TODO: engine propigation across nested functions not working currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date) def1 = currenttime def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date) deftype = sa.Date elif use_function_defaults: f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() def1 = currenttime deftype = sa.Date if testing.against('maxdb'): def2 = sa.text("curdate") elif testing.against('mssql'): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = db.scalar(func.current_date()) else: f = len('abcdef') f2 = len('abcdefghijk') def1 = def2 = "3" ts = 3 deftype = Integer t = Table( 'default_test1', metadata, # python function Column('col1', Integer, primary_key=True, default=mydefault), # python literal Column('col2', String(20), default="imthedefault", onupdate="im the update"), # preexecute expression Column('col3', Integer, default=func.length('abcdef'), onupdate=func.length('abcdefghijk')), # SQL-side default from sql expression Column('col4', deftype, server_default=def1), # SQL-side default from literal expression Column('col5', deftype, server_default=def2), # preexecute + update timestamp Column('col6', sa.Date, default=currenttime, onupdate=currenttime), Column('boolcol1', sa.Boolean, default=True), Column('boolcol2', sa.Boolean, default=False), # python function which uses ExecutionContext Column('col7', Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx), # python builtin Column('col8', sa.Date, default=datetime.date.today, onupdate=datetime.date.today), # combo Column('col9', String(20), default='py', server_default='ddl')) t.create()