def test_passive_override(self): """ Primarily for postgres, tests that when we get a primary key column back from reflecting a table which has a default value on it, we pre-execute that DefaultClause upon insert, even though DefaultClause says "let the database execute this", because in postgres we must have all the primary key values in memory before insert; otherwise we can't locate the just inserted row. """ # TODO: move this to dialect/postgres try: meta = MetaData(testing.db) testing.db.execute(""" CREATE TABLE speedy_users ( speedy_user_id SERIAL PRIMARY KEY, user_name VARCHAR NOT NULL, user_password VARCHAR NOT NULL ); """, None) t = Table("speedy_users", meta, autoload=True) t.insert().execute(user_name='user', user_password='******') l = t.select().execute().fetchall() eq_(l, [(1, 'user', 'lala')]) finally: testing.db.execute("drop table speedy_users", None)
def test_passive_override(self): """ Primarily for postgres, tests that when we get a primary key column back from reflecting a table which has a default value on it, we pre-execute that DefaultClause upon insert, even though DefaultClause says "let the database execute this", because in postgres we must have all the primary key values in memory before insert; otherwise we can't locate the just inserted row. """ # TODO: move this to dialect/postgres try: meta = MetaData(testing.db) testing.db.execute( """ CREATE TABLE speedy_users ( speedy_user_id SERIAL PRIMARY KEY, user_name VARCHAR NOT NULL, user_password VARCHAR NOT NULL ); """, None) t = Table("speedy_users", meta, autoload=True) t.insert().execute(user_name='user', user_password='******') l = t.select().execute().fetchall() eq_(l, [(1, 'user', 'lala')]) finally: testing.db.execute("drop table speedy_users", None)
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.last_inserted_ids()[0] nodes.insert().execute(data='bar', parent_id=id_)
def setUp(self): global meta, table, engine engine = engines.reconnecting_engine() meta = MetaData(engine) table = Table('sometable', meta, Column('id', Integer, primary_key=True), Column('name', String(50))) meta.create_all() table.insert().execute( [{'id':i, 'name':'row %d' % i} for i in range(1, 100)] )
def setUp(self): global meta, table, engine engine = engines.reconnecting_engine() meta = MetaData(engine) table = Table('sometable', meta, Column('id', Integer, primary_key=True), Column('name', String(50))) meta.create_all() table.insert().execute([{ 'id': i, 'name': 'row %d' % i } for i in range(1, 100)])
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: # postgres + 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.SQLError, 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: # postgres + 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.SQLError, e: assert True
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.last_inserted_ids()[0] assert id_ is not None eq_(1, sa.select([func.count(sa.text('*'))], from_obj=single).scalar())
def test_empty_insert(self): metadata = MetaData(testing.db) t1 = Table('t1', metadata, Column('is_true', Boolean, server_default=('1'))) metadata.create_all() try: result = t1.insert().execute() self.assertEquals(1, select([func.count(text('*'))], from_obj=t1).scalar()) self.assertEquals(True, t1.select().scalar()) finally: metadata.drop_all()
def test_implicit_execution(self): metadata = MetaData() table = Table('test_table', metadata, Column('foo', Integer), test_needs_acid=True, ) conn = testing.db.connect() metadata.create_all(bind=conn) try: trans = conn.begin() metadata.bind = conn t = table.insert() assert t.bind is conn table.insert().execute(foo=5) table.insert().execute(foo=6) table.insert().execute(foo=7) trans.rollback() metadata.bind = None assert conn.execute("select count(1) from test_table").scalar() == 0 finally: metadata.drop_all(bind=conn)
def test_empty_insert(self): metadata = MetaData(testing.db) t1 = Table('t1', metadata, Column('is_true', Boolean, server_default=('1'))) metadata.create_all() try: result = t1.insert().execute() self.assertEquals( 1, select([func.count(text('*'))], from_obj=t1).scalar()) self.assertEquals(True, t1.select().scalar()) finally: metadata.drop_all()
def test_implicit_execution(self): metadata = MetaData() table = Table( 'test_table', metadata, Column('foo', Integer), test_needs_acid=True, ) conn = testing.db.connect() metadata.create_all(bind=conn) try: trans = conn.begin() metadata.bind = conn t = table.insert() assert t.bind is conn table.insert().execute(foo=5) table.insert().execute(foo=6) table.insert().execute(foo=7) trans.rollback() metadata.bind = None assert conn.execute( "select count(1) from test_table").scalar() == 0 finally: metadata.drop_all(bind=conn)
def test_proxy(self): stmts = [] cursor_stmts = [] class MyProxy(ConnectionProxy): def execute(self, conn, execute, clauseelement, *multiparams, **params): stmts.append((str(clauseelement), params, multiparams)) return execute(clauseelement, *multiparams, **params) def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): cursor_stmts.append((statement, parameters, None)) return execute(cursor, statement, parameters, context) def assert_stmts(expected, received): for stmt, params, posn in expected: if not received: assert False while received: teststmt, testparams, testmultiparams = received.pop(0) teststmt = re.compile(r'[\n\t ]+', re.M).sub(' ', teststmt).strip() if teststmt.startswith(stmt) and (testparams == params or testparams == posn): break for engine in ( engines.testing_engine(options=dict(proxy=MyProxy())), engines.testing_engine( options=dict(proxy=MyProxy(), strategy='threadlocal'))): m = MetaData(engine) t1 = Table( 't1', m, Column('c1', Integer, primary_key=True), Column('c2', String(50), default=func.lower('Foo'), primary_key=True)) m.create_all() try: t1.insert().execute(c1=5, c2='some data') t1.insert().execute(c1=6) assert engine.execute("select * from t1").fetchall() == [ (5, 'some data'), (6, 'foo') ] finally: m.drop_all() engine.dispose() compiled = [("CREATE TABLE t1", {}, None), ("INSERT INTO t1 (c1, c2)", { 'c2': 'some data', 'c1': 5 }, None), ("INSERT INTO t1 (c1, c2)", { 'c1': 6 }, None), ("select * from t1", {}, None), ("DROP TABLE t1", {}, None)] if engine.dialect.preexecute_pk_sequences: cursor = [("CREATE TABLE t1", {}, None), ("INSERT INTO t1 (c1, c2)", { 'c2': 'some data', 'c1': 5 }, [5, 'some data']), ("SELECT lower", { 'lower_2': 'Foo' }, ['Foo']), ("INSERT INTO t1 (c1, c2)", { 'c2': 'foo', 'c1': 6 }, [6, 'foo']), ("select * from t1", {}, None), ("DROP TABLE t1", {}, None)] else: cursor = [ ("CREATE TABLE t1", {}, ()), ("INSERT INTO t1 (c1, c2)", { 'c2': 'some data', 'c1': 5 }, [5, 'some data']), ("INSERT INTO t1 (c1, c2)", { 'c1': 6, "lower_2": "Foo" }, [6, "Foo" ]), # bind param name 'lower_2' might be incorrect ("select * from t1", {}, ()), ("DROP TABLE t1", {}, ()) ] assert_stmts(compiled, stmts) assert_stmts(cursor, cursor_stmts)
def test_proxy(self): stmts = [] cursor_stmts = [] class MyProxy(ConnectionProxy): def execute(self, conn, execute, clauseelement, *multiparams, **params): stmts.append( (str(clauseelement), params,multiparams) ) return execute(clauseelement, *multiparams, **params) def cursor_execute(self, execute, cursor, statement, parameters, context, executemany): cursor_stmts.append( (statement, parameters, None) ) return execute(cursor, statement, parameters, context) def assert_stmts(expected, received): for stmt, params, posn in expected: if not received: assert False while received: teststmt, testparams, testmultiparams = received.pop(0) teststmt = re.compile(r'[\n\t ]+', re.M).sub(' ', teststmt).strip() if teststmt.startswith(stmt) and (testparams==params or testparams==posn): break for engine in ( engines.testing_engine(options=dict(proxy=MyProxy())), engines.testing_engine(options=dict(proxy=MyProxy(), strategy='threadlocal')) ): m = MetaData(engine) t1 = Table('t1', m, Column('c1', Integer, primary_key=True), Column('c2', String(50), default=func.lower('Foo'), primary_key=True)) m.create_all() try: t1.insert().execute(c1=5, c2='some data') t1.insert().execute(c1=6) assert engine.execute("select * from t1").fetchall() == [(5, 'some data'), (6, 'foo')] finally: m.drop_all() engine.dispose() compiled = [ ("CREATE TABLE t1", {}, None), ("INSERT INTO t1 (c1, c2)", {'c2': 'some data', 'c1': 5}, None), ("INSERT INTO t1 (c1, c2)", {'c1': 6}, None), ("select * from t1", {}, None), ("DROP TABLE t1", {}, None) ] if engine.dialect.preexecute_pk_sequences: cursor = [ ("CREATE TABLE t1", {}, None), ("INSERT INTO t1 (c1, c2)", {'c2': 'some data', 'c1': 5}, [5, 'some data']), ("SELECT lower", {'lower_2':'Foo'}, ['Foo']), ("INSERT INTO t1 (c1, c2)", {'c2': 'foo', 'c1': 6}, [6, 'foo']), ("select * from t1", {}, None), ("DROP TABLE t1", {}, None) ] else: cursor = [ ("CREATE TABLE t1", {}, ()), ("INSERT INTO t1 (c1, c2)", {'c2': 'some data', 'c1': 5}, [5, 'some data']), ("INSERT INTO t1 (c1, c2)", {'c1': 6, "lower_2":"Foo"}, [6, "Foo"]), # bind param name 'lower_2' might be incorrect ("select * from t1", {}, ()), ("DROP TABLE t1", {}, ()) ] assert_stmts(compiled, stmts) assert_stmts(cursor, cursor_stmts)