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 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()
