def test_update_to_select_schema(self):
meta = MetaData()
table = Table(
"sometable", meta,
Column("sym", String),
Column("val", Integer),
schema="schema"
)
other = Table(
"#other", meta,
Column("sym", String),
Column("newval", Integer)
)
stmt = table.update().values(
val=select([other.c.newval]).
where(table.c.sym == other.c.sym).as_scalar())
self.assert_compile(
stmt,
"UPDATE [schema].sometable SET val="
"(SELECT [#other].newval FROM [#other] "
"WHERE [schema].sometable.sym = [#other].sym)",
)
stmt = table.update().values(val=other.c.newval).\
where(table.c.sym == other.c.sym)
self.assert_compile(
stmt,
"UPDATE [schema].sometable SET val="
"[#other].newval FROM [schema].sometable, "
"[#other] WHERE [schema].sometable.sym = [#other].sym",
)
def test_update_to_select_schema(self):
meta = MetaData()
table = Table(
"sometable",
meta,
Column("sym", String),
Column("val", Integer),
schema="schema",
)
other = Table("#other", meta, Column("sym", String),
Column("newval", Integer))
stmt = table.update().values(val=select([other.c.newval]).where(
table.c.sym == other.c.sym).scalar_subquery())
self.assert_compile(
stmt,
"UPDATE [schema].sometable SET val="
"(SELECT [#other].newval FROM [#other] "
"WHERE [schema].sometable.sym = [#other].sym)",
)
stmt = (table.update().values(val=other.c.newval).where(
table.c.sym == other.c.sym))
self.assert_compile(
stmt,
"UPDATE [schema].sometable SET val="
"[#other].newval FROM [schema].sometable, "
"[#other] WHERE [schema].sometable.sym = [#other].sym",
)
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_update_returning(self):
meta = MetaData(testing.db)
table = Table(
'tables', meta,
Column('id', Integer, Sequence('gen_tables_id'), primary_key=True),
Column('persons', Integer), Column('full', Boolean))
table.create()
try:
table.insert().execute([{
'persons': 5,
'full': False
}, {
'persons': 3,
'full': False
}])
result = table.update(table.c.persons > 4,
dict(full=True),
firebird_returning=[table.c.id]).execute()
self.assertEqual(result.fetchall(), [(1, )])
result2 = select([table.c.id,
table.c.full]).order_by(table.c.id).execute()
self.assertEqual(result2.fetchall(), [(1, True), (2, False)])
finally:
table.drop()
def test_update_returning(self):
meta = MetaData(testing.db)
table = Table('tables', meta,
Column('id', Integer, Sequence('gen_tables_id'), primary_key=True),
Column('persons', Integer),
Column('full', Boolean)
)
table.create()
try:
table.insert().execute([{'persons': 5, 'full': False}, {'persons': 3, 'full': False}])
result = table.update(table.c.persons > 4, dict(full=True), firebird_returning=[table.c.id]).execute()
eq_(result.fetchall(), [(1,)])
result2 = select([table.c.id, table.c.full]).order_by(table.c.id).execute()
eq_(result2.fetchall(), [(1,True),(2,False)])
finally:
table.drop()
