def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name, "
"mytable.description",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING char_length(mytable.name) AS "
"length_1",
)
def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name VALUES "
"(:name)",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name, "
"inserted.description VALUES (:name)",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"LEN(inserted.name) AS length_1 VALUES "
"(:name)",
)
def test_update_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
u = update(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name",
)
u = update(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name, "
"mytable.description",
)
u = update(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"char_length(mytable.name) AS length_1",
)
def _test_autoincrement(self, bind):
aitable = self.tables.aitable
ids = set()
rs = bind.execute(aitable.insert(), int1=1)
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(), str1="row 2")
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(), int1=3, str1="row 3")
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(values={"int1": func.length("four")}))
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
eq_(ids, set([1, 2, 3, 4]))
eq_(
list(bind.execute(aitable.select().order_by(aitable.c.id))),
[(1, 1, None), (2, None, "row 2"), (3, 3, "row 3"), (4, 4, None)],
)
def test_strlen(self):
metadata = self.metadata
# On FB the length() function is implemented by an external UDF,
# strlen(). Various SA tests fail because they pass a parameter
# to it, and that does not work (it always results the maximum
# string length the UDF was declared to accept). This test
# checks that at least it works ok in other cases.
t = Table(
"t1",
metadata,
Column("id", Integer, Sequence("t1idseq"), primary_key=True),
Column("name", String(10)),
)
metadata.create_all()
t.insert(values=dict(name="dante")).execute()
t.insert(values=dict(name="alighieri")).execute()
select([func.count(t.c.id)],
func.length(t.c.name) == 5).execute().first()[0] == 1
def test_update_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
u = update(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
u,
"UPDATE mytable SET name=:name OUTPUT "
"inserted.myid, inserted.name",
)
u = update(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
u,
"UPDATE mytable SET name=:name OUTPUT "
"inserted.myid, inserted.name, "
"inserted.description",
)
u = (update(table1, values=dict(name="foo")).returning(table1).where(
table1.c.name == "bar"))
self.assert_compile(
u,
"UPDATE mytable SET name=:name OUTPUT "
"inserted.myid, inserted.name, "
"inserted.description WHERE mytable.name = "
":name_1",
)
u = update(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
u,
"UPDATE mytable SET name=:name OUTPUT "
"LEN(inserted.name) AS length_1",
)
def _baseline_4_expressions(self):
Zoo = self.metadata.tables["Zoo"]
Animal = self.metadata.tables["Animal"]
engine = self.metadata.bind
def fulltable(select):
"""Iterate over the full result table."""
return [list(row) for row in engine.execute(select).fetchall()]
for x in range(ITERATIONS):
assert len(fulltable(Zoo.select())) == 5
assert len(fulltable(Animal.select())) == ITERATIONS + 12
assert len(fulltable(Animal.select(Animal.c.Legs == 4))) == 4
assert len(fulltable(Animal.select(Animal.c.Legs == 2))) == 5
assert (len(
fulltable(
Animal.select(and_(Animal.c.Legs >= 2,
Animal.c.Legs < 20)))) == ITERATIONS +
9)
assert len(fulltable(Animal.select(Animal.c.Legs > 10))) == 2
assert len(fulltable(Animal.select(Animal.c.Lifespan > 70))) == 2
assert (len(
fulltable(Animal.select(
Animal.c.Species.startswith("L")))) == 2)
assert (len(
fulltable(Animal.select(
Animal.c.Species.endswith("pede")))) == 2)
assert (len(fulltable(
Animal.select(Animal.c.LastEscape != None))) == 1) # noqa
assert (len(fulltable(
Animal.select(None == Animal.c.LastEscape))) == ITERATIONS + 11
) # noqa
# In operator (containedby)
assert (len(
fulltable(Animal.select(
Animal.c.Species.like("%pede%")))) == 2)
assert (len(
fulltable(
Animal.select(
Animal.c.Species.in_(["Lion", "Tiger",
"Bear"])))) == 3)
# Try In with cell references
class thing(object):
pass
pet, pet2 = thing(), thing()
pet.Name, pet2.Name = "Slug", "Ostrich"
assert (len(
fulltable(
Animal.select(Animal.c.Species.in_([pet.Name,
pet2.Name])))) == 2)
# logic and other functions
assert (len(fulltable(Animal.select(
Animal.c.Species.like("Slug")))) == 1)
assert (len(
fulltable(Animal.select(
Animal.c.Species.like("%pede%")))) == 2)
name = "Lion"
assert (len(
fulltable(
Animal.select(func.length(Animal.c.Species) == len(name))))
== ITERATIONS + 3)
assert (len(fulltable(Animal.select(
Animal.c.Species.like("%i%")))) == ITERATIONS + 7)
# Test now(), today(), year(), month(), day()
assert (len(
fulltable(
Zoo.select(
and_(
Zoo.c.Founded != None, # noqa
Zoo.c.Founded < func.current_timestamp(_type=Date),
)))) == 3)
assert (len(
fulltable(
Animal.select(
Animal.c.LastEscape == func.current_timestamp(
_type=Date)))) == 0)
assert (len(
fulltable(
Animal.select(
func.date_part("year", Animal.c.LastEscape) == 2004)))
== 1)
assert (len(
fulltable(
Animal.select(
func.date_part("month", Animal.c.LastEscape) == 12)))
== 1)
assert (len(
fulltable(
Animal.select(
func.date_part("day", Animal.c.LastEscape) == 21))) ==
1)
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"))
def _baseline_4_expressions(self):
for x in range(ITERATIONS):
assert len(list(self.session.query(Zoo))) == 5
assert len(list(self.session.query(Animal))) == ITERATIONS + 12
assert (len(
list(
self.session.query(Animal).filter(Animal.Legs == 4))) == 4)
assert (len(
list(
self.session.query(Animal).filter(Animal.Legs == 2))) == 5)
assert (len(
list(
self.session.query(Animal).filter(
and_(Animal.Legs >= 2,
Animal.Legs < 20)))) == ITERATIONS + 9)
assert (len(
list(
self.session.query(Animal).filter(Animal.Legs > 10))) == 2)
assert (len(
list(self.session.query(Animal).filter(
Animal.Lifespan > 70))) == 2)
assert (len(
list(
self.session.query(Animal).filter(
Animal.Species.like("L%")))) == 2)
assert (len(
list(
self.session.query(Animal).filter(
Animal.Species.like("%pede")))) == 2)
assert (len(
list(
self.session.query(Animal).filter(
Animal.LastEscape != None))) == 1) # noqa
assert (len(
list(
self.session.query(Animal).filter(
Animal.LastEscape == None))) == ITERATIONS + 11
) # noqa
# In operator (containedby)
assert (len(
list(
self.session.query(Animal).filter(
Animal.Species.like("%pede%")))) == 2)
assert (len(
list(
self.session.query(Animal).filter(
Animal.Species.in_(("Lion", "Tiger", "Bear"))))) == 3)
# Try In with cell references
class thing(object):
pass
pet, pet2 = thing(), thing()
pet.Name, pet2.Name = "Slug", "Ostrich"
assert (len(
list(
self.session.query(Animal).filter(
Animal.Species.in_((pet.Name, pet2.Name))))) == 2)
# logic and other functions
name = "Lion"
assert (len(
list(
self.session.query(Animal).filter(
func.length(Animal.Species) == len(name)))) ==
ITERATIONS + 3)
assert (len(
list(
self.session.query(Animal).filter(
Animal.Species.like("%i%")))) == ITERATIONS + 7)
# Test now(), today(), year(), month(), day()
assert (len(
list(
self.session.query(Zoo).filter(
and_(Zoo.Founded != None,
Zoo.Founded < func.now()) # noqa
))) == 3)
assert (len(
list(
self.session.query(Animal).filter(
Animal.LastEscape == func.now()))) == 0)
assert (len(
list(
self.session.query(Animal).filter(
func.date_part("year", Animal.LastEscape) == 2004))) ==
1)
assert (len(
list(
self.session.query(Animal).filter(
func.date_part("month", Animal.LastEscape) == 12))) ==
1)
assert (len(
list(
self.session.query(Animal).filter(
func.date_part("day", Animal.LastEscape) == 21))) == 1)
def test_function_overrides(self):
self.assert_compile(func.current_date(), "GETDATE()")
self.assert_compile(func.length(3), "LEN(:length_1)")
def define_tables(cls, metadata):
default_generator = cls.default_generator = {"x": 50}
def mydefault():
default_generator["x"] += 1
return default_generator["x"]
def myupdate_with_ctx(ctx):
conn = ctx.connection
return conn.execute(sa.select([sa.text("13")])).scalar()
def mydefault_using_connection(ctx):
conn = ctx.connection
return conn.execute(sa.select([sa.text("12")])).scalar()
use_function_defaults = testing.against("postgresql", "mssql")
is_oracle = testing.against("oracle")
class MyClass(object):
@classmethod
def gen_default(cls, ctx):
return "hi"
class MyType(TypeDecorator):
impl = String(50)
def process_bind_param(self, value, dialect):
if value is not None:
value = "BIND" + value
return value
cls.f = 6
cls.f2 = 11
with testing.db.connect() as conn:
currenttime = cls.currenttime = func.current_date(type_=sa.Date)
if is_oracle:
ts = conn.scalar(
sa.select([
func.trunc(
func.current_timestamp(),
sa.literal_column("'DAY'"),
type_=sa.Date,
)
]))
currenttime = cls.currenttime = func.trunc(
currenttime, sa.literal_column("'DAY'"), type_=sa.Date)
def1 = currenttime
def2 = func.trunc(
sa.text("current_timestamp"),
sa.literal_column("'DAY'"),
type_=sa.Date,
)
deftype = sa.Date
elif use_function_defaults:
def1 = currenttime
deftype = sa.Date
if testing.against("mssql"):
def2 = sa.text("getdate()")
else:
def2 = sa.text("current_date")
ts = conn.scalar(func.current_date())
else:
def1 = def2 = "3"
ts = 3
deftype = Integer
cls.ts = ts
Table(
"default_test",
metadata,
# python function
Column("col1", Integer, primary_key=True, default=mydefault),
# python literal
Column(
"col2",
String(20),
default="imthedefault",
onupdate="im the update",
),
# preexecute expression
Column(
"col3",
Integer,
default=func.length("abcdef"),
onupdate=func.length("abcdefghijk"),
),
# SQL-side default from sql expression
Column("col4", deftype, server_default=def1),
# SQL-side default from literal expression
Column("col5", deftype, server_default=def2),
# preexecute + update timestamp
Column("col6", sa.Date, default=currenttime, onupdate=currenttime),
Column("boolcol1", sa.Boolean, default=True),
Column("boolcol2", sa.Boolean, default=False),
# python function which uses ExecutionContext
Column(
"col7",
Integer,
default=mydefault_using_connection,
onupdate=myupdate_with_ctx,
),
# python builtin
Column(
"col8",
sa.Date,
default=datetime.date.today,
onupdate=datetime.date.today,
),
# combo
Column("col9", String(20), default="py", server_default="ddl"),
# python method w/ context
Column("col10", String(20), default=MyClass.gen_default),
# fixed default w/ type that has bound processor
Column("col11", MyType(), default="foo"),
)
def test_py_vs_server_default_detection_one(self):
has_ = self._check_default_slots
metadata = MetaData()
tbl = Table(
"default_test",
metadata,
# python function
Column("col1", Integer, primary_key=True, default="1"),
# python literal
Column(
"col2",
String(20),
default="imthedefault",
onupdate="im the update",
),
# preexecute expression
Column(
"col3",
Integer,
default=func.length("abcdef"),
onupdate=func.length("abcdefghijk"),
),
# SQL-side default from sql expression
Column("col4", Integer, server_default="1"),
# SQL-side default from literal expression
Column("col5", Integer, server_default="1"),
# preexecute + update timestamp
Column(
"col6",
sa.Date,
default=datetime.datetime.today,
onupdate=datetime.datetime.today,
),
Column("boolcol1", sa.Boolean, default=True),
Column("boolcol2", sa.Boolean, default=False),
# python function which uses ExecutionContext
Column(
"col7",
Integer,
default=lambda: 5,
onupdate=lambda: 10,
),
# python builtin
Column(
"col8",
sa.Date,
default=datetime.date.today,
onupdate=datetime.date.today,
),
Column("col9", String(20), default="py", server_default="ddl"),
)
has_(tbl, "col1", "default")
has_(tbl, "col2", "default", "onupdate")
has_(tbl, "col3", "default", "onupdate")
has_(tbl, "col4", "server_default")
has_(tbl, "col5", "server_default")
has_(tbl, "col6", "default", "onupdate")
has_(tbl, "boolcol1", "default")
has_(tbl, "boolcol2", "default")
has_(tbl, "col7", "default", "onupdate")
has_(tbl, "col8", "default", "onupdate")
has_(tbl, "col9", "default", "server_default")