def test_table_round_trip(self): oracle.RESERVED_WORDS.remove('UNION') metadata = self.metadata table = Table("t1", metadata, Column("option", Integer), Column("plain", Integer, quote=True), # test that quote works for a reserved word # that the dialect isn't aware of when quote # is set Column("union", Integer, quote=True)) metadata.create_all() table.insert().execute( {"option": 1, "plain": 1, "union": 1} ) eq_( testing.db.execute(table.select()).first(), (1, 1, 1) ) table.update().values(option=2, plain=2, union=2).execute() eq_( testing.db.execute(table.select()).first(), (2, 2, 2) )
def test_insertid_reserved(self, connection): meta = self.metadata table = Table("select", meta, Column("col", Integer, primary_key=True)) table.create(connection) connection.execute(table.insert(), {"col": 7}) eq_(connection.scalar(table.select()), 7)
def test_insertid_reserved(self): meta = self.metadata table = Table("select", meta, Column("col", Integer, primary_key=True)) table.create() table.insert().execute(col=7) eq_(table.select().scalar(), 7)
def initiateLinkSearch(link): table = Table(tableHtml, metadata, autoload=True) s = engine.execute(table.select().where(table.columns.linkValue == link)) links = s.fetchall() if (len(links) == 0): insertedHtml = engine.execute( table.insert().values(linkValue=link)).inserted_primary_key[0] else: insertedHtml = links[0].id return insertedHtml, link
def test_quoted_column_unicode(self): metadata = self.metadata table = Table("atable", metadata, Column(u("méil"), Unicode(255), primary_key=True)) metadata.create_all() table.insert().execute({u('méil'): u('’é')}, ) result = testing.db.execute( table.select().where(table.c[u('méil')] == u('’é'))).scalar() eq_(result, u('’é'))
def test_compiled_insert(self): table = Table('testtable', metadata, Column('id', Integer, primary_key=True), Column('data', String(30))) metadata.create_all() ins = table.insert(values={'data':bindparam('x')}).compile() ins.execute({'x':"five"}, {'x':"seven"}) assert table.select().execute().fetchall() == [(1, 'five'), (2, 'seven')]
def test_compiled_insert(self): table = Table('testtable', metadata, Column('id', Integer, primary_key=True), Column('data', String(30))) metadata.create_all() ins = table.insert(values={'data': bindparam('x')}).compile() ins.execute({'x': "five"}, {'x': "seven"}) assert table.select().execute().fetchall() == [(1, 'five'), (2, 'seven')]
def test_quoted_column_non_unicode(self): metadata = self.metadata table = Table( "atable", metadata, Column("_underscorecolumn", Unicode(255), primary_key=True)) metadata.create_all() table.insert().execute({'_underscorecolumn': u('’é')}, ) result = testing.db.execute(table.select().where( table.c._underscorecolumn == u('’é'))).scalar() eq_(result, u('’é'))
def test_quoted_column_unicode(self): metadata = self.metadata table = Table("atable", metadata, Column(u("méil"), Unicode(255), primary_key=True)) metadata.create_all() table.insert().execute( {u('méil'): u('’é')}, ) result = testing.db.execute( table.select().where(table.c[u('méil')] == u('’é')) ).scalar() eq_(result, u('’é'))
def test_quoted_column_non_unicode(self): metadata = self.metadata table = Table("atable", metadata, Column("_underscorecolumn", Unicode(255), primary_key=True)) metadata.create_all() table.insert().execute( {'_underscorecolumn': u('’é')}, ) result = testing.db.execute( table.select().where(table.c._underscorecolumn == u('’é')) ).scalar() eq_(result, u('’é'))
def _assert_data_with_sequence(self, table, seqname): def go(): table.insert().execute({'id':30, 'data':'d1'}) table.insert().execute({'data':'d2'}) table.insert().execute({'id':31, 'data':'d3'}, {'id':32, 'data':'d4'}) table.insert().execute({'data':'d5'}, {'data':'d6'}) table.insert(inline=True).execute({'id':33, 'data':'d7'}) table.insert(inline=True).execute({'data':'d8'}) self.assert_sql(testing.db, go, [], with_sequences=[ ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", {'id':30, 'data':'d1'} ), ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", {'id':1, 'data':'d2'} ), ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", [{'id':31, 'data':'d3'}, {'id':32, 'data':'d4'}] ), ( "INSERT INTO testtable (id, data) VALUES (nextval('%s'), :data)" % seqname, [{'data':'d5'}, {'data':'d6'}] ), ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", [{'id':33, 'data':'d7'}] ), ( "INSERT INTO testtable (id, data) VALUES (nextval('%s'), :data)" % seqname, [{'data':'d8'}] ), ]) assert table.select().execute().fetchall() == [ (30, 'd1'), (1, 'd2'), (31, 'd3'), (32, 'd4'), (2, 'd5'), (3, 'd6'), (33, 'd7'), (4, 'd8'), ]
table.insert().execute({'id':30, 'data':'d1'}) try: table.insert().execute({'data':'d2'}) assert False except exc.IntegrityError, e: assert "violates not-null constraint" in str(e) try: table.insert().execute({'data':'d2'}, {'data':'d3'}) assert False except exc.IntegrityError, e: assert "violates not-null constraint" in str(e) table.insert().execute({'id':31, 'data':'d2'}, {'id':32, 'data':'d3'}) table.insert(inline=True).execute({'id':33, 'data':'d4'}) assert table.select().execute().fetchall() == [ (30, 'd1'), (31, 'd2'), (32, 'd3'), (33, 'd4'), ] table.delete().execute() # test the same series of events using a reflected # version of the table m2 = MetaData(testing.db) table = Table(table.name, m2, autoload=True) table.insert().execute({'id':30, 'data':'d1'}) try: table.insert().execute({'data':'d2'}) assert False
try: table.insert().execute({'data': 'd2'}, {'data': 'd3'}) assert False except exc.IntegrityError, e: assert "violates not-null constraint" in str(e) table.insert().execute({ 'id': 31, 'data': 'd2' }, { 'id': 32, 'data': 'd3' }) table.insert(inline=True).execute({'id': 33, 'data': 'd4'}) assert table.select().execute().fetchall() == [ (30, 'd1'), (31, 'd2'), (32, 'd3'), (33, 'd4'), ] table.delete().execute() # test the same series of events using a reflected # version of the table m2 = MetaData(testing.db) table = Table(table.name, m2, autoload=True) table.insert().execute({'id': 30, 'data': 'd1'}) try: table.insert().execute({'data': 'd2'}) assert False
def _assert_data_autoincrement(self, table): def go(): # execute with explicit id r = table.insert().execute({'id': 30, 'data': 'd1'}) assert r.last_inserted_ids() == [30] # execute with prefetch id r = table.insert().execute({'data': 'd2'}) assert r.last_inserted_ids() == [1] # executemany with explicit ids table.insert().execute({ 'id': 31, 'data': 'd3' }, { 'id': 32, 'data': 'd4' }) # executemany, uses SERIAL table.insert().execute({'data': 'd5'}, {'data': 'd6'}) # single execute, explicit id, inline table.insert(inline=True).execute({'id': 33, 'data': 'd7'}) # single execute, inline, uses SERIAL table.insert(inline=True).execute({'data': 'd8'}) # note that the test framework doesnt capture the "preexecute" of a seqeuence # or default. we just see it in the bind params. self.assert_sql( testing.db, go, [], with_sequences=[ ("INSERT INTO testtable (id, data) VALUES (:id, :data)", { 'id': 30, 'data': 'd1' }), ("INSERT INTO testtable (id, data) VALUES (:id, :data)", { 'id': 1, 'data': 'd2' }), ("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{ 'id': 31, 'data': 'd3' }, { 'id': 32, 'data': 'd4' }]), ("INSERT INTO testtable (data) VALUES (:data)", [{ 'data': 'd5' }, { 'data': 'd6' }]), ("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{ 'id': 33, 'data': 'd7' }]), ("INSERT INTO testtable (data) VALUES (:data)", [{ 'data': 'd8' }]), ]) assert table.select().execute().fetchall() == [ (30, 'd1'), (1, 'd2'), (31, 'd3'), (32, 'd4'), (2, 'd5'), (3, 'd6'), (33, 'd7'), (4, 'd8'), ] table.delete().execute() # test the same series of events using a reflected # version of the table m2 = MetaData(testing.db) table = Table(table.name, m2, autoload=True) def go(): table.insert().execute({'id': 30, 'data': 'd1'}) r = table.insert().execute({'data': 'd2'}) assert r.last_inserted_ids() == [5] table.insert().execute({ 'id': 31, 'data': 'd3' }, { 'id': 32, 'data': 'd4' }) table.insert().execute({'data': 'd5'}, {'data': 'd6'}) table.insert(inline=True).execute({'id': 33, 'data': 'd7'}) table.insert(inline=True).execute({'data': 'd8'}) self.assert_sql( testing.db, go, [], with_sequences=[ ("INSERT INTO testtable (id, data) VALUES (:id, :data)", { 'id': 30, 'data': 'd1' }), ("INSERT INTO testtable (id, data) VALUES (:id, :data)", { 'id': 5, 'data': 'd2' }), ("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{ 'id': 31, 'data': 'd3' }, { 'id': 32, 'data': 'd4' }]), ("INSERT INTO testtable (data) VALUES (:data)", [{ 'data': 'd5' }, { 'data': 'd6' }]), ("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{ 'id': 33, 'data': 'd7' }]), ("INSERT INTO testtable (data) VALUES (:data)", [{ 'data': 'd8' }]), ]) assert table.select().execute().fetchall() == [ (30, 'd1'), (5, 'd2'), (31, 'd3'), (32, 'd4'), (6, 'd5'), (7, 'd6'), (33, 'd7'), (8, 'd8'), ] table.delete().execute()
def databaseOutput(count=10): tables = engine.table_names() for tableName in tables: table = Table(tableName, metadata, autoload=True) tableOutput = engine.execute(table.select().limit(count)).fetchall() print(tableOutput)
def getEnteredHtmlSet(): table = Table(tableHtml, metadata, autoload=True) s = engine.execute(table.select()) links = s.fetchall() return [entry.linkValue for entry in links]
def _assert_data_with_sequence(self, table, seqname): def go(): table.insert().execute({'id': 30, 'data': 'd1'}) table.insert().execute({'data': 'd2'}) table.insert().execute({ 'id': 31, 'data': 'd3' }, { 'id': 32, 'data': 'd4' }) table.insert().execute({'data': 'd5'}, {'data': 'd6'}) table.insert(inline=True).execute({'id': 33, 'data': 'd7'}) table.insert(inline=True).execute({'data': 'd8'}) self.assert_sql( testing.db, go, [], with_sequences=[ ("INSERT INTO testtable (id, data) VALUES (:id, :data)", { 'id': 30, 'data': 'd1' }), ("INSERT INTO testtable (id, data) VALUES (:id, :data)", { 'id': 1, 'data': 'd2' }), ("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{ 'id': 31, 'data': 'd3' }, { 'id': 32, 'data': 'd4' }]), ("INSERT INTO testtable (id, data) VALUES (nextval('%s'), :data)" % seqname, [{ 'data': 'd5' }, { 'data': 'd6' }]), ("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{ 'id': 33, 'data': 'd7' }]), ("INSERT INTO testtable (id, data) VALUES (nextval('%s'), :data)" % seqname, [{ 'data': 'd8' }]), ]) assert table.select().execute().fetchall() == [ (30, 'd1'), (1, 'd2'), (31, 'd3'), (32, 'd4'), (2, 'd5'), (3, 'd6'), (33, 'd7'), (4, 'd8'), ]
def _assert_data_autoincrement(self, table): def go(): # execute with explicit id r = table.insert().execute({'id':30, 'data':'d1'}) assert r.last_inserted_ids() == [30] # execute with prefetch id r = table.insert().execute({'data':'d2'}) assert r.last_inserted_ids() == [1] # executemany with explicit ids table.insert().execute({'id':31, 'data':'d3'}, {'id':32, 'data':'d4'}) # executemany, uses SERIAL table.insert().execute({'data':'d5'}, {'data':'d6'}) # single execute, explicit id, inline table.insert(inline=True).execute({'id':33, 'data':'d7'}) # single execute, inline, uses SERIAL table.insert(inline=True).execute({'data':'d8'}) # note that the test framework doesnt capture the "preexecute" of a seqeuence # or default. we just see it in the bind params. self.assert_sql(testing.db, go, [], with_sequences=[ ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", {'id':30, 'data':'d1'} ), ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", {'id':1, 'data':'d2'} ), ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", [{'id':31, 'data':'d3'}, {'id':32, 'data':'d4'}] ), ( "INSERT INTO testtable (data) VALUES (:data)", [{'data':'d5'}, {'data':'d6'}] ), ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", [{'id':33, 'data':'d7'}] ), ( "INSERT INTO testtable (data) VALUES (:data)", [{'data':'d8'}] ), ]) assert table.select().execute().fetchall() == [ (30, 'd1'), (1, 'd2'), (31, 'd3'), (32, 'd4'), (2, 'd5'), (3, 'd6'), (33, 'd7'), (4, 'd8'), ] table.delete().execute() # test the same series of events using a reflected # version of the table m2 = MetaData(testing.db) table = Table(table.name, m2, autoload=True) def go(): table.insert().execute({'id':30, 'data':'d1'}) r = table.insert().execute({'data':'d2'}) assert r.last_inserted_ids() == [5] table.insert().execute({'id':31, 'data':'d3'}, {'id':32, 'data':'d4'}) table.insert().execute({'data':'d5'}, {'data':'d6'}) table.insert(inline=True).execute({'id':33, 'data':'d7'}) table.insert(inline=True).execute({'data':'d8'}) self.assert_sql(testing.db, go, [], with_sequences=[ ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", {'id':30, 'data':'d1'} ), ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", {'id':5, 'data':'d2'} ), ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", [{'id':31, 'data':'d3'}, {'id':32, 'data':'d4'}] ), ( "INSERT INTO testtable (data) VALUES (:data)", [{'data':'d5'}, {'data':'d6'}] ), ( "INSERT INTO testtable (id, data) VALUES (:id, :data)", [{'id':33, 'data':'d7'}] ), ( "INSERT INTO testtable (data) VALUES (:data)", [{'data':'d8'}] ), ]) assert table.select().execute().fetchall() == [ (30, 'd1'), (5, 'd2'), (31, 'd3'), (32, 'd4'), (6, 'd5'), (7, 'd6'), (33, 'd7'), (8, 'd8'), ] table.delete().execute()