def test_functions_with_cols(self):
users = table(
'users',
column('id'),
column('name'),
column('fullname'))
calculate = select([column('q'), column('z'), column('r')], from_obj=[
func.calculate(
bindparam('x', None), bindparam('y', None)
)])
self.assert_compile(select([users], users.c.id > calculate.c.z),
"SELECT users.id, users.name, users.fullname "
"FROM users, (SELECT q, z, r "
"FROM calculate(:x, :y)) "
"WHERE users.id > z"
)
s = select([users], users.c.id.between(
calculate.alias('c1').unique_params(x=17, y=45).c.z,
calculate.alias('c2').unique_params(x=5, y=12).c.z))
self.assert_compile(
s, "SELECT users.id, users.name, users.fullname "
"FROM users, (SELECT q, z, r "
"FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r "
"FROM calculate(:x_2, :y_2)) AS c2 "
"WHERE users.id BETWEEN c1.z AND c2.z", checkparams={
'y_1': 45, 'x_1': 17, 'y_2': 12, 'x_2': 5})
def test_functions_with_cols(self):
users = table("users", column("id"), column("name"),
column("fullname"))
calculate = (select(column("q"), column("z"), column("r")).select_from(
func.calculate(bindparam("x", None), bindparam("y",
None))).subquery())
self.assert_compile(
select(users).where(users.c.id > calculate.c.z),
"SELECT users.id, users.name, users.fullname "
"FROM users, (SELECT q, z, r "
"FROM calculate(:x, :y)) AS anon_1 "
"WHERE users.id > anon_1.z",
)
s = select(users).where(
users.c.id.between(
calculate.alias("c1").unique_params(x=17, y=45).c.z,
calculate.alias("c2").unique_params(x=5, y=12).c.z,
), )
self.assert_compile(
s,
"SELECT users.id, users.name, users.fullname "
"FROM users, (SELECT q, z, r "
"FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r "
"FROM calculate(:x_2, :y_2)) AS c2 "
"WHERE users.id BETWEEN c1.z AND c2.z",
checkparams={
"y_1": 45,
"x_1": 17,
"y_2": 12,
"x_2": 5
},
)
def test_functions_with_cols(self):
users = table("users", column("id"), column("name"),
column("fullname"))
calculate = select(column("q"), column("z"), column("r")).select_from(
func.calculate(bindparam("x", None), bindparam("y", None)))
with testing.expect_deprecated(
"The SelectBase.c and SelectBase.columns attributes are "
"deprecated and will be removed"):
self.assert_compile(
select(users).where(users.c.id > calculate.c.z),
"SELECT users.id, users.name, users.fullname "
"FROM users, (SELECT q, z, r "
"FROM calculate(:x, :y)) AS anon_1 "
"WHERE users.id > anon_1.z",
)
def test_functions_with_cols(self):
users = table(
"users", column("id"), column("name"), column("fullname")
)
calculate = select(
[column("q"), column("z"), column("r")],
from_obj=[
func.calculate(bindparam("x", None), bindparam("y", None))
],
)
self.assert_compile(
select([users], users.c.id > calculate.c.z),
"SELECT users.id, users.name, users.fullname "
"FROM users, (SELECT q, z, r "
"FROM calculate(:x, :y)) "
"WHERE users.id > z",
)
s = select(
[users],
users.c.id.between(
calculate.alias("c1").unique_params(x=17, y=45).c.z,
calculate.alias("c2").unique_params(x=5, y=12).c.z,
),
)
self.assert_compile(
s,
"SELECT users.id, users.name, users.fullname "
"FROM users, (SELECT q, z, r "
"FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r "
"FROM calculate(:x_2, :y_2)) AS c2 "
"WHERE users.id BETWEEN c1.z AND c2.z",
checkparams={"y_1": 45, "x_1": 17, "y_2": 12, "x_2": 5},
)
print(
"\nBelow, we use the result function scalar() to just read the first column of the first row and then close the result"
)
print(
conn.execute(
select([
func.max(addresses.c.email_address, type_=String).label('maxemail')
])).scalar())
# we can construct using “lexical” column objects as well as bind parameters:
print(
"\nwe can construct using “lexical” column objects as well as bind parameters:"
)
calculate = select([column('q'), column('z'),
column('r')]).select_from(
func.calculate(bindparam('x'), bindparam('y')))
calc = calculate.alias()
print(select([users]).where(users.c.id > calc.c.z))
# If we wanted to use our calculate statement twice with different bind parameters, the unique_params() function will
# create copies for us, and mark the bind parameters as “unique” so that conflicting names are isolated.
print(
"\nIf we wanted to use our calculate statement twice with different bind parameters, the unique_params() function "
"will create copies for us, and mark the bind parameters as “unique” so that conflicting names are isolated. "
)
calc1 = calculate.alias('c1').unique_params(x=17, y=45)
calc2 = calculate.alias('c2').unique_params(x=5, y=12)
s = select([users]).where(users.c.id.between(calc1.c.z, calc2.c.z))
print(s)
print(s.compile().params)
