def test_rowcount_flag(self):
metadata = self.metadata
engine = engines.testing_engine(options={"enable_rowcount": True})
assert engine.dialect.supports_sane_rowcount
metadata.bind = engine
t = Table("t1", metadata, Column("data", String(10)))
metadata.create_all()
r = t.insert().execute({"data": "d1"}, {"data": "d2"}, {"data": "d3"})
r = t.update().where(t.c.data == "d2").values(data="d3").execute()
eq_(r.rowcount, 1)
r = t.delete().where(t.c.data == "d3").execute()
eq_(r.rowcount, 2)
r = t.delete().execution_options(enable_rowcount=False).execute()
eq_(r.rowcount, -1)
engine.dispose()
engine = engines.testing_engine(options={"enable_rowcount": False})
assert not engine.dialect.supports_sane_rowcount
metadata.bind = engine
r = t.insert().execute({"data": "d1"}, {"data": "d2"}, {"data": "d3"})
r = t.update().where(t.c.data == "d2").values(data="d3").execute()
eq_(r.rowcount, -1)
r = t.delete().where(t.c.data == "d3").execute()
eq_(r.rowcount, -1)
r = t.delete().execution_options(enable_rowcount=True).execute()
eq_(r.rowcount, 1)
r.close()
engine.dispose()
def test_schema_roundtrips(self):
meta = self.metadata
users = Table(
"users",
meta,
Column("id", Integer, primary_key=True),
Column("name", String(50)),
schema="test_schema",
)
users.create()
users.insert().execute(id=1, name="name1")
users.insert().execute(id=2, name="name2")
users.insert().execute(id=3, name="name3")
users.insert().execute(id=4, name="name4")
eq_(
users.select().where(users.c.name == "name2").execute().fetchall(),
[(2, "name2")],
)
eq_(
users.select(use_labels=True)
.where(users.c.name == "name2")
.execute()
.fetchall(),
[(2, "name2")],
)
users.delete().where(users.c.id == 3).execute()
eq_(
users.select().where(users.c.name == "name3").execute().fetchall(),
[],
)
users.update().where(users.c.name == "name4").execute(name="newname")
eq_(
users.select(use_labels=True)
.where(users.c.id == 4)
.execute()
.fetchall(),
[(4, "newname")],
)
def test_native_odbc_execute(self):
t1 = Table("t1", MetaData(), Column("c1", Integer))
dbapi = mock_dbapi()
engine = engines.testing_engine(
"mssql+mxodbc://localhost",
options={"module": dbapi, "_initialize": False},
)
conn = engine.connect()
# crud: uses execute
conn.execute(t1.insert().values(c1="foo"))
conn.execute(t1.delete().where(t1.c.c1 == "foo"))
conn.execute(t1.update().where(t1.c.c1 == "foo").values(c1="bar"))
# select: uses executedirect
conn.execute(t1.select())
# manual flagging
conn.execution_options(native_odbc_execute=True).execute(t1.select())
conn.execution_options(native_odbc_execute=False).execute(
t1.insert().values(c1="foo")
)
eq_(
# fmt: off
[
c[2]
for c in dbapi.connect.return_value.cursor.
return_value.execute.mock_calls
],
# fmt: on
[
{"direct": True},
{"direct": True},
{"direct": True},
{"direct": True},
{"direct": False},
{"direct": True},
]
)
def _test_round_trip(self, tab, cls, convert_int):
t = Table(
tab,
MetaData(),
Column("data", String(50)),
Column("rv", cls(convert_int=convert_int)),
)
with testing.db.connect() as conn:
conn.execute(t.insert().values(data="foo"))
last_ts_1 = conn.scalar("SELECT @@DBTS")
if convert_int:
last_ts_1 = int(codecs.encode(last_ts_1, "hex"), 16)
eq_(conn.scalar(select([t.c.rv])), last_ts_1)
conn.execute(
t.update().values(data="bar").where(t.c.data == "foo"))
last_ts_2 = conn.scalar("SELECT @@DBTS")
if convert_int:
last_ts_2 = int(codecs.encode(last_ts_2, "hex"), 16)
eq_(conn.scalar(select([t.c.rv])), last_ts_2)
def test_update_from(self):
metadata = MetaData()
table1 = Table(
"mytable",
metadata,
Column("myid", Integer),
Column("name", String(30)),
Column("description", String(50)),
)
table2 = Table(
"myothertable",
metadata,
Column("otherid", Integer),
Column("othername", String(30)),
)
mt = table1.alias()
u = (table1.update().values(name="foo").where(
table2.c.otherid == table1.c.myid))
# testing mssql.base.MSSQLCompiler.update_from_clause
self.assert_compile(
u,
"UPDATE mytable SET name=:name "
"FROM mytable, myothertable WHERE "
"myothertable.otherid = mytable.myid",
)
self.assert_compile(
u.where(table2.c.othername == mt.c.name),
"UPDATE mytable SET name=:name "
"FROM mytable, myothertable, mytable AS mytable_1 "
"WHERE myothertable.otherid = mytable.myid "
"AND myothertable.othername = mytable_1.name",
)
def test_update(self):
"""
Tests sending functions and SQL expressions to the VALUES and SET
clauses of INSERT/UPDATE instances, and that column-level defaults
get overridden.
"""
meta = self.metadata
t = Table(
"t1",
meta,
Column(
"id",
Integer,
Sequence("t1idseq", optional=True),
primary_key=True,
),
Column("value", Integer),
)
t2 = Table(
"t2",
meta,
Column(
"id",
Integer,
Sequence("t2idseq", optional=True),
primary_key=True,
),
Column("value", Integer, default=7),
Column("stuff", String(20), onupdate="thisisstuff"),
)
meta.create_all()
t.insert(values=dict(value=func.length("one"))).execute()
assert t.select().execute().first()["value"] == 3
t.update(values=dict(value=func.length("asfda"))).execute()
assert t.select().execute().first()["value"] == 5
r = t.insert(values=dict(value=func.length("sfsaafsda"))).execute()
id_ = r.inserted_primary_key[0]
assert t.select(t.c.id == id_).execute().first()["value"] == 9
t.update(values={t.c.value: func.length("asdf")}).execute()
assert t.select().execute().first()["value"] == 4
t2.insert().execute()
t2.insert(values=dict(value=func.length("one"))).execute()
t2.insert(values=dict(value=func.length("asfda") + -19)).execute(
stuff="hi"
)
res = exec_sorted(select([t2.c.value, t2.c.stuff]))
eq_(res, [(-14, "hi"), (3, None), (7, None)])
t2.update(values=dict(value=func.length("asdsafasd"))).execute(
stuff="some stuff"
)
assert select([t2.c.value, t2.c.stuff]).execute().fetchall() == [
(9, "some stuff"),
(9, "some stuff"),
(9, "some stuff"),
]
t2.delete().execute()
t2.insert(values=dict(value=func.length("one") + 8)).execute()
assert t2.select().execute().first()["value"] == 11
t2.update(values=dict(value=func.length("asfda"))).execute()
eq_(
select([t2.c.value, t2.c.stuff]).execute().first(),
(5, "thisisstuff"),
)
t2.update(
values={t2.c.value: func.length("asfdaasdf"), t2.c.stuff: "foo"}
).execute()
eq_(select([t2.c.value, t2.c.stuff]).execute().first(), (9, "foo"))
