def test_twelve(self):
t = self.tables.t
actual_ts = self.bind.scalar(func.current_timestamp()).replace(
tzinfo=None) - datetime.datetime(2012, 5, 10, 12, 15, 25)
self._test(
func.current_timestamp() -
func.coalesce(t.c.dtme, func.current_timestamp()),
{"day": actual_ts.days},
)
def test_eleven(self):
self._test(
func.current_timestamp() - func.current_timestamp(),
{
"year": 0,
"month": 0,
"day": 0,
"hour": 0
},
)
def test_three(self):
self.tables.t
actual_ts = self.bind.scalar(
func.current_timestamp()) - datetime.timedelta(days=5)
self._test(
func.current_timestamp() - datetime.timedelta(days=5),
{
"hour": actual_ts.hour,
"year": actual_ts.year,
"month": actual_ts.month,
},
)
def test_compile(self):
for dialect in all_dialects(exclude=("sybase",)):
bindtemplate = BIND_TEMPLATES[dialect.paramstyle]
self.assert_compile(
func.current_timestamp(), "CURRENT_TIMESTAMP", dialect=dialect
)
self.assert_compile(func.localtime(), "LOCALTIME", dialect=dialect)
if dialect.name in ("firebird",):
self.assert_compile(
func.nosuchfunction(), "nosuchfunction", dialect=dialect
)
else:
self.assert_compile(
func.nosuchfunction(), "nosuchfunction()", dialect=dialect
)
# test generic function compile
class fake_func(GenericFunction):
__return_type__ = sqltypes.Integer
def __init__(self, arg, **kwargs):
GenericFunction.__init__(self, arg, **kwargs)
self.assert_compile(
fake_func("foo"),
"fake_func(%s)"
% bindtemplate
% {"name": "fake_func_1", "position": 1},
dialect=dialect,
)
functions._registry["_default"].pop("fake_func")
functions._case_sensitive_registry["_default"].pop("fake_func")
def test_no_paren_fns(self):
for fn, expected in [
(func.uid(), "uid"),
(func.UID(), "UID"),
(func.sysdate(), "sysdate"),
(func.row_number(), "row_number()"),
(func.rank(), "rank()"),
(func.now(), "CURRENT_TIMESTAMP"),
(func.current_timestamp(), "CURRENT_TIMESTAMP"),
(func.user(), "USER"),
]:
self.assert_compile(fn, expected)
def _baseline_5_aggregates(self):
Animal = self.metadata.tables["Animal"]
Zoo = self.metadata.tables["Zoo"]
# TODO: convert to ORM
engine = self.metadata.bind
for x in range(ITERATIONS):
# views
view = engine.execute(select([Animal.c.Legs])).fetchall()
legs = sorted([x[0] for x in view])
expected = {
"Leopard": 73.5,
"Slug": 0.75,
"Tiger": None,
"Lion": None,
"Bear": None,
"Ostrich": 103.2,
"Centipede": None,
"Emperor Penguin": None,
"Adelie Penguin": None,
"Millipede": None,
"Ape": None,
"Tick": None,
}
for species, lifespan in engine.execute(
select([Animal.c.Species, Animal.c.Lifespan])).fetchall():
assert lifespan == expected[species]
expected = ["Montr\xe9al Biod\xf4me", "Wild Animal Park"]
e = select(
[Zoo.c.Name],
and_(
Zoo.c.Founded != None, # noqa
Zoo.c.Founded <= func.current_timestamp(),
Zoo.c.Founded >= datetime.date(1990, 1, 1),
),
)
values = [val[0] for val in engine.execute(e).fetchall()]
assert set(values) == set(expected)
# distinct
legs = [
x[0] for x in engine.execute(
select([Animal.c.Legs], distinct=True)).fetchall()
]
legs.sort()
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_ansi_functions_with_args(self):
ct = func.current_timestamp("somearg")
self.assert_compile(ct, "CURRENT_TIMESTAMP(:current_timestamp_1)")
def test_five(self):
t = self.tables.t
self._test(
func.coalesce(t.c.dtme, func.current_timestamp()),
overrides={"epoch": 1336652125.0},
)
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"),
)