def test_table_friendly_name_dialect_option(faux_conn):
setup_table(
faux_conn,
"some_table",
sqlalchemy.Column("id", sqlalchemy.Integer),
bigquery_friendly_name="bob",
)
assert " ".join(faux_conn.test_data["execute"][-1][0].strip().split()) == (
"CREATE TABLE `some_table` ( `id` INT64 )"
" OPTIONS(friendly_name='bob')")
def test_table_description_dialect_option(faux_conn):
setup_table(
faux_conn,
"some_table",
sqlalchemy.Column("id", sqlalchemy.Integer),
bigquery_description="a fine table",
)
dialect = faux_conn.dialect
assert dialect.get_table_comment(faux_conn, "some_table") == {
"text": "a fine table"
}
def test_inline_comments(faux_conn):
setup_table(
faux_conn,
"some_table",
sqlalchemy.Column("id", sqlalchemy.Integer, comment="identifier"),
comment="a fine table",
)
dialect = faux_conn.dialect
assert dialect.get_table_comment(faux_conn, "some_table") == {
"text": "a fine table"
}
assert dialect.get_columns(faux_conn,
"some_table")[0]["comment"] == "identifier"
def test_likish(faux_conn, meth, arg, expected):
# See sqlalchemy.testing.suite.test_select.LikeFunctionsTest
table = setup_table(
faux_conn,
"t",
Column("id", Integer, primary_key=True),
Column("data", String(50)),
initial_data=[
{"id": 1, "data": "abcdefg"},
{"id": 2, "data": "ab/cdefg"},
{"id": 3, "data": "ab%cdefg"},
{"id": 4, "data": "ab_cdefg"},
{"id": 5, "data": "abcde/fg"},
{"id": 6, "data": "abcde%fg"},
{"id": 7, "data": "ab#cdefg"},
{"id": 8, "data": "ab9cdefg"},
{"id": 9, "data": "abcde#fg"},
{"id": 10, "data": "abcd9fg"},
],
)
expr = getattr(table.c.data, meth)(arg)
rows = {value for value, in faux_conn.execute(select([table.c.id]).where(expr))}
eq_(rows, expected)
all = {i for i in range(1, 11)}
expr = sqlalchemy.not_(expr)
rows = {value for value, in faux_conn.execute(select([table.c.id]).where(expr))}
eq_(rows, all - expected)
def some_table(connection):
return setup_table(
connection,
"some_table",
Column("id", Integer),
Column("x", Integer),
Column("y", Integer),
initial_data=[
{
"id": 1,
"x": 1,
"y": 2
},
{
"id": 2,
"x": 2,
"y": 3
},
{
"id": 3,
"x": 3,
"y": 4
},
{
"id": 4,
"x": 4,
"y": 5
},
],
)
def test_table_friendly_name_description_dialect_option(faux_conn):
setup_table(
faux_conn,
"some_table",
sqlalchemy.Column("id", sqlalchemy.Integer),
bigquery_friendly_name="bob",
bigquery_description="a fine table",
)
dialect = faux_conn.dialect
assert dialect.get_table_comment(faux_conn, "some_table") == {
"text": "a fine table"
}
assert " ".join(faux_conn.test_data["execute"][-1][0].strip().split()) == (
"CREATE TABLE `some_table` ( `id` INT64 )"
" OPTIONS(description='a fine table', friendly_name='bob')")
def test_force_quote(faux_conn):
from sqlalchemy.sql.elements import quoted_name
table = setup_table(
faux_conn, "t", sqlalchemy.Column(quoted_name("foo", True), sqlalchemy.Integer),
)
faux_conn.execute(sqlalchemy.select([table]))
assert faux_conn.test_data["execute"][-1][0] == ("SELECT `t`.`foo` \nFROM `t`")
def test_multi_value_insert(faux_conn, last_query):
table = setup_table(faux_conn, "t", sqlalchemy.Column("id", sqlalchemy.Integer))
faux_conn.execute(table.insert().values([dict(id=i) for i in range(3)]))
last_query(
"INSERT INTO `t` (`id`) VALUES"
" (%(id_m0:INT64)s), (%(id_m1:INT64)s), (%(id_m2:INT64)s)",
{"id_m0": 0, "id_m1": 1, "id_m2": 2},
)
def test_typed_parameters(faux_conn, type_, val, btype, vrep):
col_name = "foo"
table = setup_table(faux_conn, "t", sqlalchemy.Column(col_name, type_))
assert faux_conn.test_data["execute"].pop()[0].strip() == (
f"CREATE TABLE `t` (\n"
f"\t`{col_name}` {btype}\n"
f")")
faux_conn.execute(table.insert().values(**{col_name: val}))
if btype.startswith("ARRAY<"):
btype = btype[6:-1]
ptype = btype[:btype.index("(")] if "(" in btype else btype
assert faux_conn.test_data["execute"][-1] == (
f"INSERT INTO `t` (`{col_name}`) VALUES (%({col_name}:{ptype})s)",
{
col_name: val
},
)
faux_conn.execute(
table.insert().values(**{
col_name: sqlalchemy.literal(val, type_)
}).compile(
dialect=pybigquery.sqlalchemy_bigquery.BigQueryDialect(),
compile_kwargs=dict(literal_binds=True),
))
if not isinstance(vrep, str):
vrep = vrep(val)
assert faux_conn.test_data["execute"][-1] == (
f"INSERT INTO `t` (`{col_name}`) VALUES ({vrep})",
{},
)
assert list(map(list,
faux_conn.execute(sqlalchemy.select([table
])))) == [[val]] * 2
assert faux_conn.test_data["execute"][-1][
0] == "SELECT `t`.`foo` \nFROM `t`"
assert (list(
map(
list,
faux_conn.execute(sqlalchemy.select([table.c.foo],
use_labels=True)),
)) == [[val]] * 2)
assert faux_conn.test_data["execute"][-1][0] == (
"SELECT `t`.`foo` AS `t_foo` \nFROM `t`")
def test_unnest(faux_conn, alias):
from sqlalchemy import String
from sqlalchemy_bigquery import ARRAY
table = setup_table(faux_conn, "t", sqlalchemy.Column("objects", ARRAY(String)))
fcall = sqlalchemy.func.unnest(table.c.objects)
if alias:
query = fcall.alias("foo_objects").column
else:
query = fcall.column_valued("foo_objects")
compiled = str(sqlalchemy.select(query).compile(faux_conn.engine))
assert " ".join(compiled.strip().split()) == (
"SELECT `foo_objects` FROM `t` `t_1`, unnest(`t_1`.`objects`) AS `foo_objects`"
)
def test_cast_type_decorator(faux_conn, last_query):
# [artial dup of:
# sqlalchemy.testing.suite.test_types.CastTypeDecoratorTest.test_special_type
# That test failes without code that's otherwise not covered by the unit tests.
class StringAsInt(sqlalchemy.TypeDecorator):
impl = sqlalchemy.String(50)
def bind_expression(self, col):
return sqlalchemy.cast(col, String(50))
t = setup_table(faux_conn, "t", Column("x", StringAsInt()))
faux_conn.execute(t.insert(), [{"x": x} for x in [1, 2, 3]])
last_query("INSERT INTO `t` (`x`) VALUES (CAST(%(x:STRING)s AS STRING))",
{"x": 3})
def test_table_valued_alias_w_multiple_references_to_the_same_table(faux_conn, alias):
from sqlalchemy import String
from sqlalchemy_bigquery import ARRAY
table = setup_table(faux_conn, "t", sqlalchemy.Column("objects", ARRAY(String)))
fcall = sqlalchemy.func.foo(table.c.objects, table.c.objects)
if alias:
query = fcall.alias("foo_objects").column
else:
query = fcall.column_valued("foo_objects")
compiled = str(sqlalchemy.select(query).compile(faux_conn.engine))
assert " ".join(compiled.strip().split()) == (
"SELECT `foo_objects` "
"FROM `t` `t_1`, foo(`t_1`.`objects`, `t_1`.`objects`) AS `foo_objects`"
)
def test_literal_binds_kwarg_with_an_IN_operator_252(faux_conn):
table = setup_table(
faux_conn,
"test",
sqlalchemy.Column("val", sqlalchemy.Integer),
initial_data=[dict(val=i) for i in range(3)],
)
q = sqlalchemy.select([table.c.val]).where(table.c.val.in_([2]))
def nstr(q):
return " ".join(str(q).strip().split())
assert (
nstr(q.compile(faux_conn.engine, compile_kwargs={"literal_binds": True}))
== "SELECT `test`.`val` FROM `test` WHERE `test`.`val` IN (2)"
)
def test_set_drop_table_comment(faux_conn):
table = setup_table(
faux_conn,
"some_table",
sqlalchemy.Column("id", sqlalchemy.Integer),
)
dialect = faux_conn.dialect
assert dialect.get_table_comment(faux_conn, "some_table") == {"text": None}
table.comment = "a fine table"
faux_conn.execute(sqlalchemy.schema.SetTableComment(table))
assert dialect.get_table_comment(faux_conn, "some_table") == {
"text": "a fine table"
}
faux_conn.execute(sqlalchemy.schema.DropTableComment(table))
assert dialect.get_table_comment(faux_conn, "some_table") == {"text": None}
def test_group_by_composed(faux_conn):
table = setup_table(
faux_conn,
"t",
Column("id", Integer, primary_key=True),
Column("x", Integer),
Column("y", Integer),
Column("q", String(50)),
Column("p", String(50)),
initial_data=[
{"id": 1, "x": 1, "y": 2, "q": "q1", "p": "p3"},
{"id": 2, "x": 2, "y": 3, "q": "q2", "p": "p2"},
{"id": 3, "x": 3, "y": 4, "q": "q3", "p": "p1"},
],
)
expr = (table.c.x + table.c.y).label("lx")
stmt = (
select([sqlalchemy.func.count(table.c.id), expr]).group_by(expr).order_by(expr)
)
assert_result(faux_conn, stmt, [(1, 3), (1, 5), (1, 7)])
def test_compile_column(faux_conn):
table = setup_table(faux_conn, "t", sqlalchemy.Column("c", sqlalchemy.Integer))
assert table.c.c.compile(faux_conn).string == "`c`"
def test_geoalchemy2_core(faux_conn, last_query):
"""Make sure GeoAlchemy 2 Core Tutorial works as adapted to only having geometry
"""
conn = faux_conn
# Create the Table
from sqlalchemy import Column, String
from sqlalchemy_bigquery import GEOGRAPHY
lake_table = setup_table(conn, "lake", Column("name", String),
Column("geog", GEOGRAPHY))
# Insertions
conn.execute(lake_table.insert().values(
name="Majeur",
geog="POLYGON((0 0,1 0,1 1,0 1,0 0))",
))
last_query(
"INSERT INTO `lake` (`name`, `geog`)"
" VALUES (%(name:STRING)s, %(geog:geography)s)",
({
"geog": "POLYGON((0 0,1 0,1 1,0 1,0 0))",
"name": "Majeur"
}),
)
conn.execute(
lake_table.insert(),
[
{
"name": "Garde",
"geog": "POLYGON((1 0,3 0,3 2,1 2,1 0))"
},
{
"name": "Orta",
"geog": "POLYGON((3 0,6 0,6 3,3 3,3 0))"
},
],
)
last_query(
"INSERT INTO `lake` (`name`, `geog`)"
" VALUES (%(name:STRING)s, %(geog:geography)s)",
{
"name": "Garde",
"geog": "POLYGON((1 0,3 0,3 2,1 2,1 0))"
},
offset=2,
)
last_query(
"INSERT INTO `lake` (`name`, `geog`)"
" VALUES (%(name:STRING)s, %(geog:geography)s)",
{
"name": "Orta",
"geog": "POLYGON((3 0,6 0,6 3,3 3,3 0))"
},
)
# Selections
from sqlalchemy.sql import select
try:
conn.execute(select([lake_table]))
except Exception:
pass # sqlite had no special functions :)
last_query("SELECT `lake`.`name`, ST_AsBinary(`lake`.`geog`) AS `geog` \n"
"FROM `lake`")
# Spatial query
from sqlalchemy import func
try:
conn.execute(
select([lake_table.c.name],
func.ST_Contains(lake_table.c.geog, "POINT(4 1)")))
except Exception:
pass # sqlite had no special functions :)
last_query(
"SELECT `lake`.`name` \n"
"FROM `lake` \n"
"WHERE ST_Contains(`lake`.`geog`, %(ST_Contains_1:geography)s)",
{"ST_Contains_1": "POINT(4 1)"},
)
try:
conn.execute(
select(
[lake_table.c.name,
lake_table.c.geog.ST_AREA().label("area")]))
except Exception:
pass # sqlite had no special functions :)
last_query(
"SELECT `lake`.`name`, ST_Area(`lake`.`geog`) AS `area` \nFROM `lake`")
# Extra: Make sure we can save a retrieved value back:
from sqlalchemy_bigquery import WKB, WKT
geog = WKT("point(0 0)").wkb
assert isinstance(geog, WKB)
assert geog.data == (b"\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00"
b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00")
conn.execute(lake_table.insert().values(name="test", geog=geog))
last_query(
"INSERT INTO `lake` (`name`, `geog`)"
" VALUES (%(name:STRING)s, %(geog:geography)s)",
{
"name": "test",
"geog": "POINT (0 0)"
},
)
# and, while we're at it, that we can insert WKTs, although we
# normally wouldn't want to.
conn.execute(lake_table.insert().values(
name="test2",
geog=WKT("POLYGON((1 0,3 0,3 2,1 2,1 0))"),
))
last_query(
"INSERT INTO `lake` (`name`, `geog`)"
" VALUES (%(name:STRING)s, %(geog:geography)s)",
{
"name": "test2",
"geog": "POLYGON((1 0,3 0,3 2,1 2,1 0))"
},
)
def test_labels_not_forced(faux_conn):
table = setup_table(faux_conn, "t", sqlalchemy.Column("id", sqlalchemy.Integer))
result = faux_conn.execute(sqlalchemy.select([table.c.id]))
assert result.keys() == ["id"] # Look! Just the column name!
