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!