sqlalchemy-bigquery - pypi Package Compare versions

Comparing version 1.10.0 to 1.11.0

AUTHORS

@@ -22,2 +22,3 @@ # This is the list of sqlalchemy-bigquery's significant contributors.
 Romain Rigaux
+Sharoon Thomas (sharoonthomas)
 Sumedh Sakdeo
@@ -24,0 +25,0 @@ Tim Swast (tswast)

PKG-INFO

 Metadata-Version: 2.1
 Name: sqlalchemy-bigquery
-Version: 1.10.0
+Version: 1.11.0
 Summary: SQLAlchemy dialect for BigQuery
@@ -29,5 +29,5 @@ Home-page: https://github.com/googleapis/python-bigquery-sqlalchemy
 Requires-Dist: google-auth<3.0.0dev,>=1.25.0
-Requires-Dist: google-cloud-bigquery<4.0.0dev,>=2.25.2
+Requires-Dist: google-cloud-bigquery<4.0.0dev,>=3.3.6
 Requires-Dist: packaging
-Requires-Dist: sqlalchemy<2.0.0dev,>=1.2.0
+Requires-Dist: sqlalchemy<3.0.0dev,>=1.4.16
 Provides-Extra: geography
@@ -47,11 +47,11 @@ Requires-Dist: GeoAlchemy2; extra == "geography"
 Provides-Extra: all
+Requires-Dist: pytz; extra == "all"
+Requires-Dist: packaging; extra == "all"
+Requires-Dist: pyarrow>=3.0.0; extra == "all"
+Requires-Dist: grpcio<2.0dev,>=1.47.0; extra == "all"
+Requires-Dist: google-cloud-bigquery-storage<3.0.0dev,>=2.0.0; extra == "all"
 Requires-Dist: alembic; extra == "all"
-Requires-Dist: google-cloud-bigquery-storage<3.0.0dev,>=2.0.0; extra == "all"
+Requires-Dist: GeoAlchemy2; extra == "all"
+Requires-Dist: grpcio<2.0dev,>=1.49.1; python_version >= "3.11" and extra == "all"
 Requires-Dist: shapely; extra == "all"
-Requires-Dist: pyarrow>=3.0.0; extra == "all"
-Requires-Dist: grpcio<2.0dev,>=1.49.1; python_version >= "3.11" and extra == "all"
-Requires-Dist: packaging; extra == "all"
-Requires-Dist: pytz; extra == "all"
-Requires-Dist: GeoAlchemy2; extra == "all"
-Requires-Dist: grpcio<2.0dev,>=1.47.0; extra == "all"
 
@@ -94,4 +94,2 @@ SQLAlchemy Dialect for BigQuery
 
-.. note::
-   This library is only compatible with SQLAlchemy versions < 2.0.0
 
@@ -169,4 +167,5 @@ Installation
     table = Table('dataset.table', MetaData(bind=engine), autoload=True)
-    print(select([func.count('*')], from_obj=table).scalar())
+    print(select([func.count('*')], from_obj=table().scalar())
 
+
 Project
@@ -343,3 +342,3 @@ ^^^^^^^
         'bigquery://some-project/some-dataset?user_supplied_client=True',
-	connect_args={'client': custom_bq_client},
+	    connect_args={'client': custom_bq_client},
     )
@@ -346,0 +345,0 @@

README.rst

@@ -37,4 +37,2 @@ SQLAlchemy Dialect for BigQuery
 
-.. note::
-   This library is only compatible with SQLAlchemy versions < 2.0.0
 
@@ -112,4 +110,5 @@ Installation
     table = Table('dataset.table', MetaData(bind=engine), autoload=True)
-    print(select([func.count('*')], from_obj=table).scalar())
+    print(select([func.count('*')], from_obj=table().scalar())
 
+
 Project
@@ -286,3 +285,3 @@ ^^^^^^^
         'bigquery://some-project/some-dataset?user_supplied_client=True',
-	connect_args={'client': custom_bq_client},
+	    connect_args={'client': custom_bq_client},
     )
@@ -289,0 +288,0 @@

setup.py

@@ -102,5 +102,5 @@ #!/usr/bin/env python
         "google-auth>=1.25.0,<3.0.0dev",  # Work around pip wack.
-        "google-cloud-bigquery>=2.25.2,<4.0.0dev",
+        "google-cloud-bigquery>=3.3.6,<4.0.0dev",
         "packaging",
-        "sqlalchemy>=1.2.0,<2.0.0dev",
+        "sqlalchemy>=1.4.16,<3.0.0dev",
     ],
@@ -107,0 +107,0 @@ extras_require=extras,

sqlalchemy_bigquery.egg-info/PKG-INFO

 Metadata-Version: 2.1
 Name: sqlalchemy-bigquery
-Version: 1.10.0
+Version: 1.11.0
 Summary: SQLAlchemy dialect for BigQuery
@@ -29,5 +29,5 @@ Home-page: https://github.com/googleapis/python-bigquery-sqlalchemy
 Requires-Dist: google-auth<3.0.0dev,>=1.25.0
-Requires-Dist: google-cloud-bigquery<4.0.0dev,>=2.25.2
+Requires-Dist: google-cloud-bigquery<4.0.0dev,>=3.3.6
 Requires-Dist: packaging
-Requires-Dist: sqlalchemy<2.0.0dev,>=1.2.0
+Requires-Dist: sqlalchemy<3.0.0dev,>=1.4.16
 Provides-Extra: geography
@@ -47,11 +47,11 @@ Requires-Dist: GeoAlchemy2; extra == "geography"
 Provides-Extra: all
+Requires-Dist: pytz; extra == "all"
+Requires-Dist: packaging; extra == "all"
+Requires-Dist: pyarrow>=3.0.0; extra == "all"
+Requires-Dist: grpcio<2.0dev,>=1.47.0; extra == "all"
+Requires-Dist: google-cloud-bigquery-storage<3.0.0dev,>=2.0.0; extra == "all"
 Requires-Dist: alembic; extra == "all"
-Requires-Dist: google-cloud-bigquery-storage<3.0.0dev,>=2.0.0; extra == "all"
+Requires-Dist: GeoAlchemy2; extra == "all"
+Requires-Dist: grpcio<2.0dev,>=1.49.1; python_version >= "3.11" and extra == "all"
 Requires-Dist: shapely; extra == "all"
-Requires-Dist: pyarrow>=3.0.0; extra == "all"
-Requires-Dist: grpcio<2.0dev,>=1.49.1; python_version >= "3.11" and extra == "all"
-Requires-Dist: packaging; extra == "all"
-Requires-Dist: pytz; extra == "all"
-Requires-Dist: GeoAlchemy2; extra == "all"
-Requires-Dist: grpcio<2.0dev,>=1.47.0; extra == "all"
 
@@ -94,4 +94,2 @@ SQLAlchemy Dialect for BigQuery
 
-.. note::
-   This library is only compatible with SQLAlchemy versions < 2.0.0
 
@@ -169,4 +167,5 @@ Installation
     table = Table('dataset.table', MetaData(bind=engine), autoload=True)
-    print(select([func.count('*')], from_obj=table).scalar())
+    print(select([func.count('*')], from_obj=table().scalar())
 
+
 Project
@@ -343,3 +342,3 @@ ^^^^^^^
         'bigquery://some-project/some-dataset?user_supplied_client=True',
-	connect_args={'client': custom_bq_client},
+	    connect_args={'client': custom_bq_client},
     )
@@ -346,0 +345,0 @@

sqlalchemy_bigquery.egg-info/requires.txt

 google-api-core!=2.0.*,!=2.1.*,!=2.2.*,!=2.3.0,<3.0.0dev,>=1.31.5
 google-auth<3.0.0dev,>=1.25.0
-google-cloud-bigquery<4.0.0dev,>=2.25.2
+google-cloud-bigquery<4.0.0dev,>=3.3.6
 packaging
-sqlalchemy<2.0.0dev,>=1.2.0
+sqlalchemy<3.0.0dev,>=1.4.16
 
@@ -11,10 +11,10 @@ [alembic]
 [all]
+pytz
+packaging
+pyarrow>=3.0.0
+grpcio<2.0dev,>=1.47.0
+google-cloud-bigquery-storage<3.0.0dev,>=2.0.0
 alembic
-google-cloud-bigquery-storage<3.0.0dev,>=2.0.0
+GeoAlchemy2
 shapely
-pyarrow>=3.0.0
-packaging
-pytz
-GeoAlchemy2
-grpcio<2.0dev,>=1.47.0
 
@@ -21,0 +21,0 @@ [all:python_version >= "3.11"]

sqlalchemy_bigquery/_struct.py

@@ -20,3 +20,2 @@ # Copyright (c) 2021 The sqlalchemy-bigquery Authors
 
-import packaging.version
 import sqlalchemy.sql.default_comparator
@@ -28,11 +27,6 @@ import sqlalchemy.sql.sqltypes
 
-sqlalchemy_1_4_or_more = packaging.version.parse(
-    sqlalchemy.__version__
-) >= packaging.version.parse("1.4")
+import sqlalchemy.sql.coercions
+import sqlalchemy.sql.roles
 
-if sqlalchemy_1_4_or_more:
-    import sqlalchemy.sql.coercions
-    import sqlalchemy.sql.roles
 
-
 def _get_subtype_col_spec(type_):
@@ -108,2 +102,4 @@ global _get_subtype_col_spec
                 return self[name]
+            else:
+                raise AttributeError(name)
 
@@ -113,28 +109,12 @@ comparator_factory = Comparator
 
-# In the implementations of _field_index below, we're stealing from
-# the JSON type implementation, but the code to steal changed in
-# 1.4. :/
+def _field_index(self, name, operator):
+    return sqlalchemy.sql.coercions.expect(
+        sqlalchemy.sql.roles.BinaryElementRole,
+        name,
+        expr=self.expr,
+        operator=operator,
+        bindparam_type=sqlalchemy.types.String(),
+    )
 
-if sqlalchemy_1_4_or_more:
 
-    def _field_index(self, name, operator):
-        return sqlalchemy.sql.coercions.expect(
-            sqlalchemy.sql.roles.BinaryElementRole,
-            name,
-            expr=self.expr,
-            operator=operator,
-            bindparam_type=sqlalchemy.types.String(),
-        )
-
-else:
-
-    def _field_index(self, name, operator):
-        return sqlalchemy.sql.default_comparator._check_literal(
-            self.expr,
-            operator,
-            name,
-            bindparam_type=sqlalchemy.types.String(),
-        )
-
-
 def struct_getitem_op(a, b):
@@ -141,0 +121,0 @@ raise NotImplementedError()

sqlalchemy_bigquery/base.py

@@ -166,3 +166,3 @@ # Copyright (c) 2017 The sqlalchemy-bigquery Authors
     )
-    def __distribute_types_to_expanded_placeholders(self, m):
+    def __distribute_types_to_expanded_placeholders(self, m):  # pragma: NO COVER
         # If we have an in parameter, it sometimes gets expaned to 0 or more
@@ -178,2 +178,4 @@ # parameters and we need to move the type marker to each
         # `%(foo)s` gets expaneded to `%(foo_0)s, `%(foo_1)s, ...`.
+
+        # Coverage: despite our best efforts, never recognized this segment of code as being tested.
         placeholders, type_ = m.groups()
@@ -224,3 +226,3 @@ if placeholders:
         #   print(
-        #      select([func.unnest(foo.c.objects).alias('foo_objects').column])
+        #      select(func.unnest(foo.c.objects).alias('foo_objects').column)
         #      .compile(engine))
@@ -342,3 +344,10 @@ #
         if within_group_by:
-            kwargs["render_label_as_label"] = args[0]
+            column_label = args[0]
+            sql_keywords = {"GROUPING SETS", "ROLLUP", "CUBE"}
+            for keyword in sql_keywords:
+                if keyword in str(column_label):
+                    break
+            else:  # for/else always happens unless break gets called
+                kwargs["render_label_as_label"] = column_label
+
         return super(BigQueryCompiler, self).visit_label(*args, **kwargs)
@@ -363,7 +372,3 @@
 
-    __expanding_text = (
-        "EXPANDING"
-        if __sqlalchemy_version_info < packaging.version.parse("1.4")
-        else "POSTCOMPILE"
-    )
+    __expanding_text = "POSTCOMPILE"
 
@@ -396,5 +401,2 @@ # https://github.com/sqlalchemy/sqlalchemy/commit/f79df12bd6d99b8f6f09d4bf07722638c4b4c159
 
-    def visit_empty_set_expr(self, element_types):
-        return ""
-
     def visit_not_in_op_binary(self, binary, operator, **kw):
@@ -409,4 +411,2 @@ return (
 
-    visit_notin_op_binary = visit_not_in_op_binary  # before 1.4
-
     ############################################################################
@@ -434,4 +434,4 @@
 
-    def visit_notcontains_op_binary(self, binary, operator, **kw):
-        return super(BigQueryCompiler, self).visit_notcontains_op_binary(
+    def visit_not_contains_op_binary(self, binary, operator, **kw):
+        return super(BigQueryCompiler, self).visit_not_contains_op_binary(
             self._maybe_reescape(binary), operator, **kw
@@ -445,4 +445,4 @@ )
 
-    def visit_notstartswith_op_binary(self, binary, operator, **kw):
-        return super(BigQueryCompiler, self).visit_notstartswith_op_binary(
+    def visit_not_startswith_op_binary(self, binary, operator, **kw):
+        return super(BigQueryCompiler, self).visit_not_startswith_op_binary(
             self._maybe_reescape(binary), operator, **kw
@@ -456,4 +456,4 @@ )
 
-    def visit_notendswith_op_binary(self, binary, operator, **kw):
-        return super(BigQueryCompiler, self).visit_notendswith_op_binary(
+    def visit_not_endswith_op_binary(self, binary, operator, **kw):
+        return super(BigQueryCompiler, self).visit_not_endswith_op_binary(
             self._maybe_reescape(binary), operator, **kw
@@ -523,3 +523,4 @@ )
             #
-            if getattr(bindparam, "expand_op", None) is not None:
+            # Coverage: despite our best efforts, never recognized this segment of code as being tested.
+            if getattr(bindparam, "expand_op", None) is not None:  # pragma: NO COVER
                 assert bindparam.expand_op.__name__.endswith("in_op")  # in in
@@ -658,11 +659,11 @@ bindparam = bindparam._clone(maintain_key=True)
     # BigQuery has no support for foreign keys.
-    def visit_foreign_key_constraint(self, constraint):
+    def visit_foreign_key_constraint(self, constraint, **kw):
         return None
 
     # BigQuery has no support for primary keys.
-    def visit_primary_key_constraint(self, constraint):
+    def visit_primary_key_constraint(self, constraint, **kw):
         return None
 
     # BigQuery has no support for unique constraints.
-    def visit_unique_constraint(self, constraint):
+    def visit_unique_constraint(self, constraint, **kw):
         return None
@@ -775,3 +776,3 @@
 
-    def visit_set_table_comment(self, create):
+    def visit_set_table_comment(self, create, **kw):
         table_name = self.preparer.format_table(create.element)
@@ -783,3 +784,3 @@ description = self.sql_compiler.render_literal_value(
 
-    def visit_drop_table_comment(self, drop):
+    def visit_drop_table_comment(self, drop, **kw):
         table_name = self.preparer.format_table(drop.element)
@@ -1047,4 +1048,12 @@ return f"ALTER TABLE {table_name} SET OPTIONS(description=null)"
     def dbapi(cls):
+        """
+        Use `import_dbapi()` instead.
+        Maintained for backward compatibility.
+        """
         return dbapi
 
+    @classmethod
+    def import_dbapi(cls):
+        return dbapi
+
     @staticmethod
@@ -1220,3 +1229,17 @@ def _build_formatted_table_id(table):
 
-    def has_table(self, connection, table_name, schema=None):
+    def has_table(self, connection, table_name, schema=None, **kw):
+        """Checks whether a table exists in BigQuery.
+
+        Args:
+            connection (google.cloud.bigquery.client.Client): The client
+                object used to interact with BigQuery.
+            table_name (str): The name of the table to check for.
+            schema (str, optional): The name of the schema to which the table
+                belongs. Defaults to the default schema.
+            **kw (dict): Any extra keyword arguments will be ignored.
+
+        Returns:
+            bool: True if the table exists, False otherwise.
+
+        """
         try:
@@ -1275,6 +1298,2 @@ self._get_table(connection, table_name, schema)
 
-    def _check_unicode_returns(self, connection, additional_tests=None):
-        # requests gives back Unicode strings
-        return True
-
     def get_view_definition(self, connection, view_name, schema=None, **kw):
@@ -1299,3 +1318,9 @@ if isinstance(connection, Engine):
         if isinstance(arg, sqlalchemy.sql.expression.ColumnElement):
-            if not isinstance(arg.type, sqlalchemy.sql.sqltypes.ARRAY):
+            if not (
+                isinstance(arg.type, sqlalchemy.sql.sqltypes.ARRAY)
+                or (
+                    hasattr(arg.type, "impl")
+                    and isinstance(arg.type.impl, sqlalchemy.sql.sqltypes.ARRAY)
+                )
+            ):
                 raise TypeError("The argument to unnest must have an ARRAY type.")
@@ -1302,0 +1327,0 @@ self.type = arg.type.item_type

sqlalchemy_bigquery/requirements.py

@@ -140,2 +140,7 @@ # Copyright (c) 2021 The sqlalchemy-bigquery Authors
     @property
+    def array_type(self):
+        """Target database must support array_type"""
+        return supported()
+
+    @property
     def implicit_default_schema(self):
@@ -142,0 +147,0 @@ """target system has a strong concept of 'default' schema that can

sqlalchemy_bigquery/version.py

@@ -20,2 +20,2 @@ # Copyright (c) 2021 The sqlalchemy-bigquery Authors
 
-__version__ = "1.10.0"
+__version__ = "1.11.0"

tests/sqlalchemy_d...t_compliance/test_dialect_compliance.py

@@ -21,2 +21,3 @@ # Copyright (c) 2021 The sqlalchemy-bigquery Authors
 import datetime
+import decimal
 import mock
@@ -31,12 +32,18 @@ import packaging.version
 import sqlalchemy.sql.sqltypes
-from sqlalchemy.testing import util
+from sqlalchemy.testing import util, config
 from sqlalchemy.testing.assertions import eq_
-from sqlalchemy.testing.suite import config, select, exists
+from sqlalchemy.testing.suite import select, exists
 from sqlalchemy.testing.suite import *  # noqa
+from sqlalchemy.testing.suite import Integer, Table, Column, String, bindparam, testing
 from sqlalchemy.testing.suite import (
-    ComponentReflectionTest as _ComponentReflectionTest,
     CTETest as _CTETest,
     ExistsTest as _ExistsTest,
+    FetchLimitOffsetTest as _FetchLimitOffsetTest,
+    DifficultParametersTest as _DifficultParametersTest,
+    DistinctOnTest,
+    HasIndexTest,
+    IdentityAutoincrementTest,
     InsertBehaviorTest as _InsertBehaviorTest,
     LongNameBlowoutTest,
+    PostCompileParamsTest,
     QuotedNameArgumentTest,
@@ -47,27 +54,179 @@ SimpleUpdateDeleteTest as _SimpleUpdateDeleteTest,
 
+from sqlalchemy.testing.suite.test_types import (
+    ArrayTest,
+)
 
-if packaging.version.parse(sqlalchemy.__version__) < packaging.version.parse("1.4"):
-    from sqlalchemy.testing.suite import LimitOffsetTest as _LimitOffsetTest
+from sqlalchemy.testing.suite.test_reflection import (
+    BizarroCharacterFKResolutionTest,
+    ComponentReflectionTest,
+    HasTableTest,
+)
 
-    class LimitOffsetTest(_LimitOffsetTest):
-        @pytest.mark.skip("BigQuery doesn't allow an offset without a limit.")
-        def test_simple_offset(self):
-            pass
+if packaging.version.parse(sqlalchemy.__version__) >= packaging.version.parse("2.0"):
+    import uuid
+    from sqlalchemy.sql import type_coerce
+    from sqlalchemy.testing.suite import (
+        TrueDivTest as _TrueDivTest,
+        IntegerTest as _IntegerTest,
+        NumericTest as _NumericTest,
+        StringTest as _StringTest,
+        UuidTest as _UuidTest,
+    )
 
-        test_bound_offset = test_simple_offset
+    class DifficultParametersTest(_DifficultParametersTest):
+        """There are some parameters that don't work with bigquery that were removed from this test"""
 
-    class TimestampMicrosecondsTest(_TimestampMicrosecondsTest):
-        data = datetime.datetime(2012, 10, 15, 12, 57, 18, 396, tzinfo=pytz.UTC)
+        tough_parameters = testing.combinations(
+            ("boring",),
+            ("per cent",),
+            ("per % cent",),
+            ("%percent",),
+            ("col:ons",),
+            ("_starts_with_underscore",),
+            ("more :: %colons%",),
+            ("_name",),
+            ("___name",),
+            ("42numbers",),
+            ("percent%signs",),
+            ("has spaces",),
+            ("1param",),
+            ("1col:on",),
+            argnames="paramname",
+        )
 
-        def test_literal(self):
-            # The base tests doesn't set up the literal properly, because
-            # it doesn't pass its datatype to `literal`.
+        @tough_parameters
+        @config.requirements.unusual_column_name_characters
+        def test_round_trip_same_named_column(self, paramname, connection, metadata):
+            name = paramname
 
-            def literal(value):
-                assert value == self.data
-                return sqlalchemy.sql.elements.literal(value, self.datatype)
+            t = Table(
+                "t",
+                metadata,
+                Column("id", Integer, primary_key=True),
+                Column(name, String(50), nullable=False),
+            )
 
-            with mock.patch("sqlalchemy.testing.suite.test_types.literal", literal):
-                super(TimestampMicrosecondsTest, self).test_literal()
+            # table is created
+            t.create(connection)
 
+            # automatic param generated by insert
+            connection.execute(t.insert().values({"id": 1, name: "some name"}))
+
+            # automatic param generated by criteria, plus selecting the column
+            stmt = select(t.c[name]).where(t.c[name] == "some name")
+
+            eq_(connection.scalar(stmt), "some name")
+
+            # use the name in a param explicitly
+            stmt = select(t.c[name]).where(t.c[name] == bindparam(name))
+
+            row = connection.execute(stmt, {name: "some name"}).first()
+
+            # name works as the key from cursor.description
+            eq_(row._mapping[name], "some name")
+
+            # use expanding IN
+            stmt = select(t.c[name]).where(
+                t.c[name].in_(["some name", "some other_name"])
+            )
+
+            row = connection.execute(stmt).first()
+
+        @testing.fixture
+        def multirow_fixture(self, metadata, connection):
+            mytable = Table(
+                "mytable",
+                metadata,
+                Column("myid", Integer),
+                Column("name", String(50)),
+                Column("desc", String(50)),
+            )
+
+            mytable.create(connection)
+
+            connection.execute(
+                mytable.insert(),
+                [
+                    {"myid": 1, "name": "a", "desc": "a_desc"},
+                    {"myid": 2, "name": "b", "desc": "b_desc"},
+                    {"myid": 3, "name": "c", "desc": "c_desc"},
+                    {"myid": 4, "name": "d", "desc": "d_desc"},
+                ],
+            )
+            yield mytable
+
+        @tough_parameters
+        def test_standalone_bindparam_escape(
+            self, paramname, connection, multirow_fixture
+        ):
+            tbl1 = multirow_fixture
+            stmt = select(tbl1.c.myid).where(
+                tbl1.c.name == bindparam(paramname, value="x")
+            )
+            res = connection.scalar(stmt, {paramname: "c"})
+            eq_(res, 3)
+
+        @tough_parameters
+        def test_standalone_bindparam_escape_expanding(
+            self, paramname, connection, multirow_fixture
+        ):
+            tbl1 = multirow_fixture
+            stmt = (
+                select(tbl1.c.myid)
+                .where(tbl1.c.name.in_(bindparam(paramname, value=["a", "b"])))
+                .order_by(tbl1.c.myid)
+            )
+
+            res = connection.scalars(stmt, {paramname: ["d", "a"]}).all()
+            eq_(res, [1, 4])
+
+    # BQ has no autoinc and client-side defaults can't work for select
+    del _IntegerTest.test_huge_int_auto_accommodation
+
+    class NumericTest(_NumericTest):
+        """Added a where clause for BQ compatibility."""
+
+        @testing.fixture
+        def do_numeric_test(self, metadata, connection):
+            def run(type_, input_, output, filter_=None, check_scale=False):
+                t = Table("t", metadata, Column("x", type_))
+                t.create(connection)
+                connection.execute(t.insert(), [{"x": x} for x in input_])
+
+                result = {row[0] for row in connection.execute(t.select())}
+                output = set(output)
+                if filter_:
+                    result = {filter_(x) for x in result}
+                    output = {filter_(x) for x in output}
+                eq_(result, output)
+                if check_scale:
+                    eq_([str(x) for x in result], [str(x) for x in output])
+
+                where_expr = True
+
+                connection.execute(t.delete().where(where_expr))
+
+                if type_.asdecimal:
+                    test_value = decimal.Decimal("2.9")
+                    add_value = decimal.Decimal("37.12")
+                else:
+                    test_value = 2.9
+                    add_value = 37.12
+
+                connection.execute(t.insert(), {"x": test_value})
+                assert_we_are_a_number = connection.scalar(
+                    select(type_coerce(t.c.x + add_value, type_))
+                )
+                eq_(
+                    round(assert_we_are_a_number, 3),
+                    round(test_value + add_value, 3),
+                )
+
+            return run
+
+    class TimestampMicrosecondsTest(_TimestampMicrosecondsTest):
+        """BQ has no support for BQ util.text_type"""
+
+        data = datetime.datetime(2012, 10, 15, 12, 57, 18, 396, tzinfo=pytz.UTC)
+
         def test_select_direct(self, connection):
@@ -88,41 +247,246 @@ # This func added because this test was failing when passed the
 
+    def test_round_trip_executemany(self, connection):
+        unicode_table = self.tables.unicode_table
+        connection.execute(
+            unicode_table.insert(),
+            [{"id": i, "unicode_data": self.data} for i in range(3)],
+        )
+
+        rows = connection.execute(select(unicode_table.c.unicode_data)).fetchall()
+        eq_(rows, [(self.data,) for i in range(3)])
+        for row in rows:
+            assert isinstance(row[0], str)
+
+    sqlalchemy.testing.suite.test_types._UnicodeFixture.test_round_trip_executemany = (
+        test_round_trip_executemany
+    )
+
+    class TrueDivTest(_TrueDivTest):
+        @pytest.mark.skip("BQ rounds based on datatype")
+        def test_floordiv_integer(self):
+            pass
+
+        @pytest.mark.skip("BQ rounds based on datatype")
+        def test_floordiv_integer_bound(self):
+            pass
+
+    class SimpleUpdateDeleteTest(_SimpleUpdateDeleteTest):
+        """The base tests fail if operations return rows for some reason."""
+
+        def test_update(self):
+            t = self.tables.plain_pk
+            connection = config.db.connect()
+            # In SQLAlchemy 2.0, the datatype changed to dict in the following function.
+            r = connection.execute(t.update().where(t.c.id == 2), dict(data="d2_new"))
+            assert not r.is_insert
+
+            eq_(
+                connection.execute(t.select().order_by(t.c.id)).fetchall(),
+                [(1, "d1"), (2, "d2_new"), (3, "d3")],
+            )
+
+        def test_delete(self):
+            t = self.tables.plain_pk
+            connection = config.db.connect()
+            r = connection.execute(t.delete().where(t.c.id == 2))
+            assert not r.is_insert
+            eq_(
+                connection.execute(t.select().order_by(t.c.id)).fetchall(),
+                [(1, "d1"), (3, "d3")],
+            )
+
+    class StringTest(_StringTest):
+        """Added a where clause for BQ compatibility"""
+
+        def test_dont_truncate_rightside(
+            self, metadata, connection, expr=None, expected=None
+        ):
+            t = Table(
+                "t",
+                metadata,
+                Column("x", String(2)),
+                Column("id", Integer, primary_key=True),
+            )
+            t.create(connection)
+            connection.connection.commit()
+            connection.execute(
+                t.insert(),
+                [{"x": "AB", "id": 1}, {"x": "BC", "id": 2}, {"x": "AC", "id": 3}],
+            )
+            combinations = [("%B%", ["AB", "BC"]), ("A%C", ["AC"]), ("A%C%Z", [])]
+
+            for args in combinations:
+                eq_(
+                    list(
+                        sorted(
+                            connection.scalars(
+                                select(t.c.x).where(t.c.x.like(args[0]))
+                            ).all()
+                        )
+                    ),
+                    list(sorted(args[1])),
+                )
+
+    class UuidTest(_UuidTest):
+        """BQ needs to pass in UUID as a string"""
+
+        @classmethod
+        def define_tables(cls, metadata):
+            Table(
+                "uuid_table",
+                metadata,
+                Column("id", Integer, primary_key=True, test_needs_autoincrement=True),
+                Column("uuid_data", String),  # Use native UUID for primary data
+                Column(
+                    "uuid_text_data", String, nullable=True
+                ),  # Optional text representation
+                Column("uuid_data_nonnative", String),
+                Column("uuid_text_data_nonnative", String),
+            )
+
+        def test_uuid_round_trip(self, connection):
+            data = str(uuid.uuid4())
+            uuid_table = self.tables.uuid_table
+
+            connection.execute(
+                uuid_table.insert(),
+                {"id": 1, "uuid_data": data, "uuid_data_nonnative": data},
+            )
+            row = connection.execute(
+                select(uuid_table.c.uuid_data, uuid_table.c.uuid_data_nonnative).where(
+                    uuid_table.c.uuid_data == data,
+                    uuid_table.c.uuid_data_nonnative == data,
+                )
+            ).first()
+            eq_(row, (data, data))
+
+        def test_uuid_text_round_trip(self, connection):
+            data = str(uuid.uuid4())
+            uuid_table = self.tables.uuid_table
+
+            connection.execute(
+                uuid_table.insert(),
+                {
+                    "id": 1,
+                    "uuid_text_data": data,
+                    "uuid_text_data_nonnative": data,
+                },
+            )
+            row = connection.execute(
+                select(
+                    uuid_table.c.uuid_text_data,
+                    uuid_table.c.uuid_text_data_nonnative,
+                ).where(
+                    uuid_table.c.uuid_text_data == data,
+                    uuid_table.c.uuid_text_data_nonnative == data,
+                )
+            ).first()
+            eq_((row[0].lower(), row[1].lower()), (data, data))
+
+        def test_literal_uuid(self, literal_round_trip):
+            data = str(uuid.uuid4())
+            literal_round_trip(String(), [data], [data])
+
+        def test_literal_text(self, literal_round_trip):
+            data = str(uuid.uuid4())
+            literal_round_trip(
+                String(),
+                [data],
+                [data],
+                filter_=lambda x: x.lower(),
+            )
+
+        def test_literal_nonnative_uuid(self, literal_round_trip):
+            data = str(uuid.uuid4())
+            literal_round_trip(String(), [data], [data])
+
+        def test_literal_nonnative_text(self, literal_round_trip):
+            data = str(uuid.uuid4())
+            literal_round_trip(
+                String(),
+                [data],
+                [data],
+                filter_=lambda x: x.lower(),
+            )
+
+        @testing.requires.insert_returning
+        def test_uuid_returning(self, connection):
+            data = str(uuid.uuid4())
+            str_data = str(data)
+            uuid_table = self.tables.uuid_table
+
+            result = connection.execute(
+                uuid_table.insert().returning(
+                    uuid_table.c.uuid_data,
+                    uuid_table.c.uuid_text_data,
+                    uuid_table.c.uuid_data_nonnative,
+                    uuid_table.c.uuid_text_data_nonnative,
+                ),
+                {
+                    "id": 1,
+                    "uuid_data": data,
+                    "uuid_text_data": str_data,
+                    "uuid_data_nonnative": data,
+                    "uuid_text_data_nonnative": str_data,
+                },
+            )
+            row = result.first()
+
+            eq_(row, (data, str_data, data, str_data))
+
 else:
     from sqlalchemy.testing.suite import (
-        FetchLimitOffsetTest as _FetchLimitOffsetTest,
         RowCountTest as _RowCountTest,
     )
 
-    class FetchLimitOffsetTest(_FetchLimitOffsetTest):
-        @pytest.mark.skip("BigQuery doesn't allow an offset without a limit.")
-        def test_simple_offset(self):
-            pass
+    del DifficultParametersTest  # exercises column names illegal in BQ
 
-        test_bound_offset = test_simple_offset
-        test_expr_offset = test_simple_offset_zero = test_simple_offset
+    class RowCountTest(_RowCountTest):
+        """"""
 
-        # The original test is missing an order by.
+        @classmethod
+        def insert_data(cls, connection):
+            cls.data = data = [
+                ("Angela", "A"),
+                ("Andrew", "A"),
+                ("Anand", "A"),
+                ("Bob", "B"),
+                ("Bobette", "B"),
+                ("Buffy", "B"),
+                ("Charlie", "C"),
+                ("Cynthia", "C"),
+                ("Chris", "C"),
+            ]
 
-        # Also, note that sqlalchemy union is a union distinct, not a
-        # union all. This test caught that were were getting that wrong.
-        def test_limit_render_multiple_times(self, connection):
-            table = self.tables.some_table
-            stmt = select(table.c.id).order_by(table.c.id).limit(1).scalar_subquery()
+            employees_table = cls.tables.employees
+            connection.execute(
+                employees_table.insert(),
+                [
+                    {"employee_id": i, "name": n, "department": d}
+                    for i, (n, d) in enumerate(data)
+                ],
+            )
 
-            u = sqlalchemy.union(select(stmt), select(stmt)).subquery().select()
+    class SimpleUpdateDeleteTest(_SimpleUpdateDeleteTest):
+        """The base tests fail if operations return rows for some reason."""
 
-            self._assert_result(
-                connection,
-                u,
-                [(1,)],
+        def test_update(self):
+            t = self.tables.plain_pk
+            r = config.db.execute(t.update().where(t.c.id == 2), data="d2_new")
+            assert not r.is_insert
+
+            eq_(
+                config.db.execute(t.select().order_by(t.c.id)).fetchall(),
+                [(1, "d1"), (2, "d2_new"), (3, "d3")],
             )
 
-    del DifficultParametersTest  # exercises column names illegal in BQ
-    del DistinctOnTest  # expects unquoted table names.
-    del HasIndexTest  # BQ doesn't do the indexes that SQLA is loooking for.
-    del IdentityAutoincrementTest  # BQ doesn't do autoincrement
+        def test_delete(self):
+            t = self.tables.plain_pk
+            r = config.db.execute(t.delete().where(t.c.id == 2))
+            assert not r.is_insert
+            eq_(
+                config.db.execute(t.select().order_by(t.c.id)).fetchall(),
+                [(1, "d1"), (3, "d3")],
+            )
 
-    # This test makes makes assertions about generated sql and trips
-    # over the backquotes that we add everywhere. XXX Why do we do that?
-    del PostCompileParamsTest
-
     class TimestampMicrosecondsTest(_TimestampMicrosecondsTest):
@@ -178,36 +542,10 @@ data = datetime.datetime(2012, 10, 15, 12, 57, 18, 396, tzinfo=pytz.UTC)
 
-    class RowCountTest(_RowCountTest):
-        @classmethod
-        def insert_data(cls, connection):
-            cls.data = data = [
-                ("Angela", "A"),
-                ("Andrew", "A"),
-                ("Anand", "A"),
-                ("Bob", "B"),
-                ("Bobette", "B"),
-                ("Buffy", "B"),
-                ("Charlie", "C"),
-                ("Cynthia", "C"),
-                ("Chris", "C"),
-            ]
 
-            employees_table = cls.tables.employees
-            connection.execute(
-                employees_table.insert(),
-                [
-                    {"employee_id": i, "name": n, "department": d}
-                    for i, (n, d) in enumerate(data)
-                ],
-            )
+class CTETest(_CTETest):
+    @pytest.mark.skip("Can't use CTEs with insert")
+    def test_insert_from_select_round_trip(self):
+        pass
 
-
-# Quotes aren't allowed in BigQuery table names.
-del QuotedNameArgumentTest
-
-
-class InsertBehaviorTest(_InsertBehaviorTest):
-    @pytest.mark.skip(
-        "BQ has no autoinc and client-side defaults can't work for select."
-    )
-    def test_insert_from_select_autoinc(cls):
+    @pytest.mark.skip("Recusive CTEs aren't supported.")
+    def test_select_recursive_round_trip(self):
         pass
@@ -228,3 +566,3 @@
             connection.execute(
-                select([stuff.c.id]).where(
+                select(stuff.c.id).where(
                     and_(
@@ -243,3 +581,3 @@ stuff.c.id == 1,
             connection.execute(
-                select([stuff.c.id]).where(exists().where(stuff.c.data == "no data"))
+                select(stuff.c.id).where(exists().where(stuff.c.data == "no data"))
             ).fetchall(),
@@ -250,50 +588,63 @@ [],
 
-# This test requires features (indexes, primary keys, etc., that BigQuery doesn't have.
-del LongNameBlowoutTest
+class FetchLimitOffsetTest(_FetchLimitOffsetTest):
+    @pytest.mark.skip("BigQuery doesn't allow an offset without a limit.")
+    def test_simple_offset(self):
+        pass
 
+    test_bound_offset = test_simple_offset
+    test_expr_offset = test_simple_offset_zero = test_simple_offset
+    test_limit_offset_nobinds = test_simple_offset  # TODO figure out
+    # how to prevent this from failing
+    # The original test is missing an order by.
 
-class SimpleUpdateDeleteTest(_SimpleUpdateDeleteTest):
-    """The base tests fail if operations return rows for some reason."""
+    # Also, note that sqlalchemy union is a union distinct, not a
+    # union all. This test caught that we were getting that wrong.
+    def test_limit_render_multiple_times(self, connection):
+        table = self.tables.some_table
+        stmt = select(table.c.id).order_by(table.c.id).limit(1).scalar_subquery()
 
-    def test_update(self):
-        t = self.tables.plain_pk
-        r = config.db.execute(t.update().where(t.c.id == 2), data="d2_new")
-        assert not r.is_insert
-        # assert not r.returns_rows
+        u = sqlalchemy.union(select(stmt), select(stmt)).subquery().select()
 
-        eq_(
-            config.db.execute(t.select().order_by(t.c.id)).fetchall(),
-            [(1, "d1"), (2, "d2_new"), (3, "d3")],
+        self._assert_result(
+            connection,
+            u,
+            [(1,)],
         )
 
-    def test_delete(self):
-        t = self.tables.plain_pk
-        r = config.db.execute(t.delete().where(t.c.id == 2))
-        assert not r.is_insert
-        # assert not r.returns_rows
-        eq_(
-            config.db.execute(t.select().order_by(t.c.id)).fetchall(),
-            [(1, "d1"), (3, "d3")],
-        )
 
-
-class CTETest(_CTETest):
-    @pytest.mark.skip("Can't use CTEs with insert")
-    def test_insert_from_select_round_trip(self):
+class InsertBehaviorTest(_InsertBehaviorTest):
+    @pytest.mark.skip(
+        "BQ has no autoinc and client-side defaults can't work for select."
+    )
+    def test_insert_from_select_autoinc(cls):
         pass
 
-    @pytest.mark.skip("Recusive CTEs aren't supported.")
-    def test_select_recursive_round_trip(self):
+    @pytest.mark.skip(
+        "BQ has no autoinc and client-side defaults can't work for select."
+    )
+    def test_no_results_for_non_returning_insert(cls):
         pass
 
 
-class ComponentReflectionTest(_ComponentReflectionTest):
-    @pytest.mark.skip("Big query types don't track precision, length, etc.")
-    def course_grained_types():
-        pass
+del ComponentReflectionTest  # Multiple tests re: CHECK CONSTRAINTS, etc which
+# BQ does not support
+# class ComponentReflectionTest(_ComponentReflectionTest):
+#     @pytest.mark.skip("Big query types don't track precision, length, etc.")
+#     def course_grained_types():
+#         pass
 
-    test_numeric_reflection = test_varchar_reflection = course_grained_types
+#     test_numeric_reflection = test_varchar_reflection = course_grained_types
 
-    @pytest.mark.skip("BQ doesn't have indexes (in the way these tests expect).")
-    def test_get_indexes(self):
-        pass
+#     @pytest.mark.skip("BQ doesn't have indexes (in the way these tests expect).")
+#     def test_get_indexes(self):
+#         pass
+
+del ArrayTest  # only appears to apply to postgresql
+del BizarroCharacterFKResolutionTest
+del HasTableTest.test_has_table_cache  # TODO confirm whether BQ has table caching
+del DistinctOnTest  # expects unquoted table names.
+del HasIndexTest  # BQ doesn't do the indexes that SQLA is loooking for.
+del IdentityAutoincrementTest  # BQ doesn't do autoincrement
+del LongNameBlowoutTest  # Requires features (indexes, primary keys, etc., that BigQuery doesn't have.
+del PostCompileParamsTest  # BQ adds backticks to bind parameters, causing failure of tests TODO: fix this?
+del QuotedNameArgumentTest  # Quotes aren't allowed in BigQuery table names.

tests/system/test__struct.py

@@ -57,3 +57,3 @@ # Copyright (c) 2021 The sqlalchemy-bigquery Authors
 
-    assert list(conn.execute(sqlalchemy.select([table]))) == [
+    assert list(conn.execute(sqlalchemy.select(table))) == [
         (
@@ -66,12 +66,12 @@ {
     ]
-    assert list(conn.execute(sqlalchemy.select([table.c.person.NAME]))) == [("bob",)]
-    assert list(conn.execute(sqlalchemy.select([table.c.person.children[0]]))) == [
+    assert list(conn.execute(sqlalchemy.select(table.c.person.NAME))) == [("bob",)]
+    assert list(conn.execute(sqlalchemy.select(table.c.person.children[0]))) == [
         ({"name": "billy", "bdate": datetime.date(2020, 1, 1)},)
     ]
+    assert list(conn.execute(sqlalchemy.select(table.c.person.children[0].bdate))) == [
+        (datetime.date(2020, 1, 1),)
+    ]
     assert list(
-        conn.execute(sqlalchemy.select([table.c.person.children[0].bdate]))
-    ) == [(datetime.date(2020, 1, 1),)]
-    assert list(
         conn.execute(
-            sqlalchemy.select([table]).where(table.c.person.children[0].NAME == "billy")
+            sqlalchemy.select(table).where(table.c.person.children[0].NAME == "billy")
         )
@@ -89,3 +89,3 @@ ) == [
             conn.execute(
-                sqlalchemy.select([table]).where(
+                sqlalchemy.select(table).where(
                     table.c.person.children[0].NAME == "sally"
@@ -105,10 +105,13 @@ )
     table_name = f"{bigquery_dataset}.test_comples_literals_pr_67"
-    engine.execute(
-        f"""
-        create table {table_name} as (
-            select 'a' as id,
-            struct(1 as x__count, 2 as y__count, 3 as z__count) as dimensions
+    with engine.connect() as conn:
+        conn.execute(
+            sqlalchemy.text(
+                f"""
+            create table {table_name} as (
+                select 'a' as id,
+                struct(1 as x__count, 2 as y__count, 3 as z__count) as dimensions
+                )
+            """
             )
-        """
-    )
+        )
 
@@ -118,5 +121,3 @@ table = sqlalchemy.Table(table_name, metadata, autoload_with=engine)
     got = str(
-        sqlalchemy.select([(table.c.dimensions.x__count + 5).label("c")]).compile(
-            engine
-        )
+        sqlalchemy.select((table.c.dimensions.x__count + 5).label("c")).compile(engine)
     )
@@ -157,5 +158,7 @@ want = (
         mock_table.insert(),
-        dict(mock_id="x"),
-        dict(mock_id="y"),
-        dict(mock_id="z"),
+        [
+            dict(mock_id="x"),
+            dict(mock_id="y"),
+            dict(mock_id="z"),
+        ],
     )
@@ -162,0 +165,0 @@ conn.execute(

tests/system/test_geography.py

@@ -77,3 +77,3 @@ # Copyright (c) 2021 The PyBigQuery Authors
     assert sorted(
-        (r.name, r.geog.desc[:4]) for r in conn.execute(select([lake_table]))
+        (r.name, r.geog.desc[:4]) for r in conn.execute(select(lake_table))
     ) == [("Garde", "0103"), ("Majeur", "0103"), ("Orta", "0103")]
@@ -86,3 +86,5 @@
     [[result]] = conn.execute(
-        select([lake_table.c.name], func.ST_Contains(lake_table.c.geog, "POINT(4 1)"))
+        select(lake_table.c.name).where(
+            func.ST_Contains(lake_table.c.geog, "POINT(4 1)")
+        )
     )
@@ -94,3 +96,3 @@ assert result == "Orta"
         for r in conn.execute(
-            select([lake_table.c.name, lake_table.c.geog.ST_AREA().label("area")])
+            select(lake_table.c.name, lake_table.c.geog.ST_AREA().label("area"))
         )
@@ -101,3 +103,5 @@ ) == [("Garde", 49452374328), ("Majeur", 12364036567), ("Orta", 111253664228)]
 
-    [[geog]] = conn.execute(select([lake_table.c.geog], lake_table.c.name == "Garde"))
+    [[geog]] = conn.execute(
+        select(lake_table.c.geog).where(lake_table.c.name == "Garde")
+    )
     conn.execute(lake_table.insert().values(name="test", geog=geog))
@@ -108,3 +112,5 @@ assert (
                 conn.execute(
-                    select([lake_table.c.geog.st_area()], lake_table.c.name == "test")
+                    select(lake_table.c.geog.st_area()).where(
+                        lake_table.c.name == "test"
+                    )
                 )
@@ -130,3 +136,5 @@ )[0][0]
                 conn.execute(
-                    select([lake_table.c.geog.st_area()], lake_table.c.name == "test2")
+                    select(lake_table.c.geog.st_area()).where(
+                        lake_table.c.name == "test2"
+                    )
                 )
@@ -133,0 +141,0 @@ )[0][0]

tests/system/test_sqlalchemy_bigquery.py

@@ -160,3 +160,3 @@ # Copyright (c) 2017 The sqlalchemy-bigquery Authors
 def table(engine, bigquery_dataset):
-    return Table(f"{bigquery_dataset}.sample", MetaData(bind=engine), autoload=True)
+    return Table(f"{bigquery_dataset}.sample", MetaData(), autoload_with=engine)
 
@@ -166,3 +166,3 @@
 def table_using_test_dataset(engine_using_test_dataset):
-    return Table("sample", MetaData(bind=engine_using_test_dataset), autoload=True)
+    return Table("sample", MetaData(), autoload_with=engine_using_test_dataset)
 
@@ -172,5 +172,3 @@
 def table_one_row(engine, bigquery_dataset):
-    return Table(
-        f"{bigquery_dataset}.sample_one_row", MetaData(bind=engine), autoload=True
-    )
+    return Table(f"{bigquery_dataset}.sample_one_row", MetaData(), autoload_with=engine)
 
@@ -180,3 +178,3 @@
 def table_dml(engine, bigquery_empty_table):
-    return Table(bigquery_empty_table, MetaData(bind=engine), autoload=True)
+    return Table(bigquery_empty_table, MetaData(), autoload_with=engine)
 
@@ -223,3 +221,3 @@
         query = (
-            select([col1, col2, col3])
+            select(col1, col2, col3)
             .where(col1 < "2017-01-01 00:00:00")
@@ -235,21 +233,30 @@ .group_by(col1)
 def test_engine_with_dataset(engine_using_test_dataset, bigquery_dataset):
-    rows = engine_using_test_dataset.execute("SELECT * FROM sample_one_row").fetchall()
-    assert list(rows[0]) == ONE_ROW_CONTENTS
+    with engine_using_test_dataset.connect() as conn:
+        rows = conn.execute(sqlalchemy.text("SELECT * FROM sample_one_row")).fetchall()
+        assert list(rows[0]) == ONE_ROW_CONTENTS
 
-    table_one_row = Table(
-        "sample_one_row", MetaData(bind=engine_using_test_dataset), autoload=True
-    )
-    rows = table_one_row.select(use_labels=True).execute().fetchall()
-    assert list(rows[0]) == ONE_ROW_CONTENTS_EXPANDED
+        table_one_row = Table(
+            "sample_one_row", MetaData(), autoload_with=engine_using_test_dataset
+        )
+        rows = conn.execute(
+            table_one_row.select().set_label_style(
+                sqlalchemy.LABEL_STYLE_TABLENAME_PLUS_COL
+            )
+        ).fetchall()
+        assert list(rows[0]) == ONE_ROW_CONTENTS_EXPANDED
 
-    table_one_row = Table(
-        f"{bigquery_dataset}.sample_one_row",
-        MetaData(bind=engine_using_test_dataset),
-        autoload=True,
-    )
-    rows = table_one_row.select(use_labels=True).execute().fetchall()
-    # verify that we are pulling from the specifically-named dataset,
-    # instead of pulling from the default dataset of the engine (which
-    # does not have this table at all)
-    assert list(rows[0]) == ONE_ROW_CONTENTS_EXPANDED
+        table_one_row = Table(
+            f"{bigquery_dataset}.sample_one_row",
+            MetaData(),
+            autoload_with=engine_using_test_dataset,
+        )
+        rows = conn.execute(
+            table_one_row.select().set_label_style(
+                sqlalchemy.LABEL_STYLE_TABLENAME_PLUS_COL
+            )
+        ).fetchall()
+        # verify that we are pulling from the specifically-named dataset,
+        # instead of pulling from the default dataset of the engine (which
+        # does not have this table at all)
+        assert list(rows[0]) == ONE_ROW_CONTENTS_EXPANDED
 
@@ -260,9 +267,10 @@
 ):
-    rows = engine_with_location.execute(
-        f"SELECT * FROM {bigquery_regional_dataset}.sample_one_row"
-    ).fetchall()
-    assert list(rows[0]) == ONE_ROW_CONTENTS
+    with engine_with_location.connect() as conn:
+        rows = conn.execute(
+            sqlalchemy.text(f"SELECT * FROM {bigquery_regional_dataset}.sample_one_row")
+        ).fetchall()
+        assert list(rows[0]) == ONE_ROW_CONTENTS
 
 
-def test_reflect_select(table, table_using_test_dataset):
+def test_reflect_select(table, engine_using_test_dataset, table_using_test_dataset):
     for table in [table, table_using_test_dataset]:
@@ -288,26 +296,37 @@ assert table.comment == "A sample table containing most data types."
 
-        # Force unique column labels using `use_labels` below to deal
-        # with BQ sometimes complaining about duplicate column names
-        # when a destination table is specified, even though no
-        # destination table is specified. When this test was written,
-        # `use_labels` was forced by the dialect.
-        rows = table.select(use_labels=True).execute().fetchall()
-        assert len(rows) == 1000
+        with engine_using_test_dataset.connect() as conn:
+            rows = conn.execute(
+                table.select().set_label_style(
+                    sqlalchemy.LABEL_STYLE_TABLENAME_PLUS_COL
+                )
+            ).fetchall()
+            assert len(rows) == 1000
 
 
 def test_content_from_raw_queries(engine, bigquery_dataset):
-    rows = engine.execute(f"SELECT * FROM {bigquery_dataset}.sample_one_row").fetchall()
-    assert list(rows[0]) == ONE_ROW_CONTENTS
+    with engine.connect() as conn:
+        rows = conn.execute(
+            sqlalchemy.text(f"SELECT * FROM {bigquery_dataset}.sample_one_row")
+        ).fetchall()
+        assert list(rows[0]) == ONE_ROW_CONTENTS
 
 
 def test_record_content_from_raw_queries(engine, bigquery_dataset):
-    rows = engine.execute(
-        f"SELECT record.name FROM {bigquery_dataset}.sample_one_row"
-    ).fetchall()
-    assert rows[0][0] == "John Doe"
+    with engine.connect() as conn:
+        rows = conn.execute(
+            sqlalchemy.text(
+                f"SELECT record.name FROM {bigquery_dataset}.sample_one_row"
+            )
+        ).fetchall()
+        assert rows[0][0] == "John Doe"
 
 
 def test_content_from_reflect(engine, table_one_row):
-    rows = table_one_row.select(use_labels=True).execute().fetchall()
-    assert list(rows[0]) == ONE_ROW_CONTENTS_EXPANDED
+    with engine.connect() as conn:
+        rows = conn.execute(
+            table_one_row.select().set_label_style(
+                sqlalchemy.LABEL_STYLE_TABLENAME_PLUS_COL
+            )
+        ).fetchall()
+        assert list(rows[0]) == ONE_ROW_CONTENTS_EXPANDED
 
@@ -317,6 +336,8 @@
     unicode_str = "白人看不懂"
-    returned_str = sqlalchemy.select(
-        [expression.bindparam("好", unicode_str)],
-        from_obj=table_one_row,
-    ).scalar()
+    with engine.connect() as conn:
+        returned_str = conn.execute(
+            sqlalchemy.select(expression.bindparam("好", unicode_str)).select_from(
+                table_one_row
+            )
+        ).scalar()
     assert returned_str == unicode_str
@@ -327,6 +348,7 @@
     one_row = Table(
-        "bigquery-public-data.samples.natality", MetaData(bind=engine), autoload=True
+        "bigquery-public-data.samples.natality", MetaData(), autoload_with=engine
     )
-    row = one_row.select().limit(1).execute().first()
-    assert len(row) >= 1
+    with engine.connect() as conn:
+        row = conn.execute(one_row.select().limit(1)).first()
+        assert len(row) >= 1
 
@@ -338,10 +360,8 @@
             f"{bigquery_dataset}.table_does_not_exist",
-            MetaData(bind=engine),
-            autoload=True,
+            MetaData(),
+            autoload_with=engine,
         )
 
     assert (
-        Table(
-            f"{bigquery_dataset}.table_does_not_exist", MetaData(bind=engine)
-        ).exists()
+        sqlalchemy.inspect(engine).has_table(f"{bigquery_dataset}.table_does_not_exist")
         is False
@@ -355,4 +375,4 @@ )
             "dataset_does_not_exist.table_does_not_exist",
-            MetaData(bind=engine),
-            autoload=True,
+            MetaData(),
+            autoload_with=engine,
         )
@@ -362,3 +382,3 @@
 def test_tables_list(engine, engine_using_test_dataset, bigquery_dataset):
-    tables = engine.table_names()
+    tables = sqlalchemy.inspect(engine).get_table_names()
     assert f"{bigquery_dataset}.sample" in tables
@@ -368,3 +388,3 @@ assert f"{bigquery_dataset}.sample_one_row" in tables
 
-    tables = engine_using_test_dataset.table_names()
+    tables = sqlalchemy.inspect(engine_using_test_dataset).get_table_names()
     assert "sample" in tables
@@ -396,3 +416,3 @@ assert "sample_one_row" in tables
         sqlalchemy.func.sum(
-            sqlalchemy.case([[sqlalchemy.literal(True), col.label("inner")]]).label(
+            sqlalchemy.case((sqlalchemy.literal(True), col.label("inner"))).label(
                 "outer"
@@ -403,3 +423,3 @@ )
             sqlalchemy.func.sum(
-                sqlalchemy.case([[sqlalchemy.literal(True), col.label("inner")]]).label(
+                sqlalchemy.case((sqlalchemy.literal(True), col.label("inner"))).label(
                     "middle"
@@ -431,3 +451,3 @@ )
                 func.sum(
-                    case([(table.c.boolean == sqlalchemy.literal(True), 1)], else_=0)
+                    case((table.c.boolean == sqlalchemy.literal(True), 1), else_=0)
                 ),
@@ -465,11 +485,12 @@ )
     q = query(table)
-    result = engine.execute(q).fetchall()
-    assert len(result) > 0
+    with engine.connect() as conn:
+        result = conn.execute(q).fetchall()
+        assert len(result) > 0
 
     q = query(table_using_test_dataset)
-    result = engine_using_test_dataset.execute(q).fetchall()
+    with engine_using_test_dataset.connect() as conn:
+        result = conn.execute(q).fetchall()
+        assert len(result) > 0
 
-    assert len(result) > 0
 
-
 def test_compiled_query_literal_binds(
@@ -480,4 +501,5 @@ engine, engine_using_test_dataset, table, table_using_test_dataset, query
     compiled = q.compile(engine, compile_kwargs={"literal_binds": True})
-    result = engine.execute(compiled).fetchall()
-    assert len(result) > 0
+    with engine.connect() as conn:
+        result = conn.execute(compiled).fetchall()
+        assert len(result) > 0
 
@@ -488,4 +510,5 @@ q = query(table_using_test_dataset)
     )
-    result = engine_using_test_dataset.execute(compiled).fetchall()
-    assert len(result) > 0
+    with engine_using_test_dataset.connect() as conn:
+        result = conn.execute(compiled).fetchall()
+        assert len(result) > 0
 
@@ -519,27 +542,42 @@
     table = Table(
-        "bigquery-public-data.noaa_gsod.gsod*", MetaData(bind=engine), autoload=True
+        "bigquery-public-data.noaa_gsod.gsod*", MetaData(), autoload_with=engine
     )
-    rows = table.select().limit(1).execute().first()
-    assert len(rows) > 0
+    with engine.connect() as conn:
+        rows = conn.execute(table.select().limit(1)).first()
+        assert len(rows) > 0
 
 
 def test_dml(engine, session, table_dml):
+    """
+    Test DML operations on a table with no data. This table is created
+    in the `bigquery_empty_table` fixture.
+
+    Modern versions of sqlalchemy does not really require setting the
+    label style. This has been maintained to retain this test.
+    """
     # test insert
-    engine.execute(table_dml.insert(ONE_ROW_CONTENTS_DML))
-    result = table_dml.select(use_labels=True).execute().fetchall()
-    assert len(result) == 1
+    with engine.connect() as conn:
+        conn.execute(table_dml.insert().values(ONE_ROW_CONTENTS_DML))
+        result = conn.execute(
+            table_dml.select().set_label_style(sqlalchemy.LABEL_STYLE_DEFAULT)
+        ).fetchall()
+        assert len(result) == 1
 
-    # test update
-    session.query(table_dml).filter(table_dml.c.string == "test").update(
-        {"string": "updated_row"}, synchronize_session=False
-    )
-    updated_result = table_dml.select(use_labels=True).execute().fetchone()
-    assert updated_result[table_dml.c.string] == "updated_row"
+        # test update
+        session.query(table_dml).filter(table_dml.c.string == "test").update(
+            {"string": "updated_row"}, synchronize_session=False
+        )
+        updated_result = conn.execute(
+            table_dml.select().set_label_style(sqlalchemy.LABEL_STYLE_DEFAULT)
+        ).fetchone()
+        assert updated_result._mapping[table_dml.c.string] == "updated_row"
 
-    # test delete
-    session.query(table_dml).filter(table_dml.c.string == "updated_row").delete(
-        synchronize_session=False
-    )
-    result = table_dml.select(use_labels=True).execute().fetchall()
-    assert len(result) == 0
+        # test delete
+        session.query(table_dml).filter(table_dml.c.string == "updated_row").delete(
+            synchronize_session=False
+        )
+        result = conn.execute(
+            table_dml.select().set_label_style(sqlalchemy.LABEL_STYLE_DEFAULT)
+        ).fetchall()
+        assert len(result) == 0
 
@@ -703,12 +741,30 @@
 def test_has_table(engine, engine_using_test_dataset, bigquery_dataset):
-    assert engine.has_table("sample", bigquery_dataset) is True
-    assert engine.has_table(f"{bigquery_dataset}.sample") is True
-    assert engine.has_table(f"{bigquery_dataset}.nonexistent_table") is False
-    assert engine.has_table("nonexistent_table", "nonexistent_dataset") is False
+    assert sqlalchemy.inspect(engine).has_table("sample", bigquery_dataset) is True
+    assert sqlalchemy.inspect(engine).has_table(f"{bigquery_dataset}.sample") is True
+    assert (
+        sqlalchemy.inspect(engine).has_table(f"{bigquery_dataset}.nonexistent_table")
+        is False
+    )
+    assert (
+        sqlalchemy.inspect(engine).has_table("nonexistent_table", "nonexistent_dataset")
+        is False
+    )
 
-    assert engine_using_test_dataset.has_table("sample") is True
-    assert engine_using_test_dataset.has_table("sample", bigquery_dataset) is True
-    assert engine_using_test_dataset.has_table(f"{bigquery_dataset}.sample") is True
+    assert sqlalchemy.inspect(engine_using_test_dataset).has_table("sample") is True
+    assert (
+        sqlalchemy.inspect(engine_using_test_dataset).has_table(
+            "sample", bigquery_dataset
+        )
+        is True
+    )
+    assert (
+        sqlalchemy.inspect(engine_using_test_dataset).has_table(
+            f"{bigquery_dataset}.sample"
+        )
+        is True
+    )
 
-    assert engine_using_test_dataset.has_table("sample_alt") is False
+    assert (
+        sqlalchemy.inspect(engine_using_test_dataset).has_table("sample_alt") is False
+    )
 
@@ -760,3 +816,3 @@
             conn.execute(
-                sqlalchemy.select([sqlalchemy.literal(-1).in_(list(range(99999)))])
+                sqlalchemy.select(sqlalchemy.literal(-1).in_(list(range(99999))))
             )
@@ -791,3 +847,3 @@ ) == [(False,)]
     )
-    query = select([func.unnest(table.c.objects).alias("foo_objects").column])
+    query = select(func.unnest(table.c.objects).alias("foo_objects").column)
     compiled = str(query.compile(engine))
@@ -827,6 +883,4 @@ assert " ".join(compiled.strip().split()) == (
     query = select(
-        [
-            selectable.c.foo,
-            func.unnest(selectable.c.bars).column_valued("unnest_bars"),
-        ]
+        selectable.c.foo,
+        func.unnest(selectable.c.bars).column_valued("unnest_bars"),
     ).select_from(selectable)
@@ -833,0 +887,0 @@ compiled = str(query.compile(engine))

tests/unit/conftest.py

@@ -33,14 +33,10 @@ # Copyright (c) 2021 The sqlalchemy-bigquery Authors
 sqlalchemy_version = packaging.version.parse(sqlalchemy.__version__)
-sqlalchemy_1_3_or_higher = pytest.mark.skipif(
-    sqlalchemy_version < packaging.version.parse("1.3"),
-    reason="requires sqlalchemy 1.3 or higher",
+sqlalchemy_before_2_0 = pytest.mark.skipif(
+    sqlalchemy_version >= packaging.version.parse("2.0"),
+    reason="requires sqlalchemy 1.3 or lower",
 )
-sqlalchemy_1_4_or_higher = pytest.mark.skipif(
-    sqlalchemy_version < packaging.version.parse("1.4"),
+sqlalchemy_2_0_or_higher = pytest.mark.skipif(
+    sqlalchemy_version < packaging.version.parse("2.0"),
     reason="requires sqlalchemy 1.4 or higher",
 )
-sqlalchemy_before_1_4 = pytest.mark.skipif(
-    sqlalchemy_version >= packaging.version.parse("1.4"),
-    reason="requires sqlalchemy 1.3 or lower",
-)
 
@@ -47,0 +43,0 @@

tests/unit/test__struct.py

@@ -87,3 +87,3 @@ # Copyright (c) 2017 The sqlalchemy-bigquery Authors
     sql = f"SELECT {sql} AS `anon_1` \nFROM `t`"
-    assert str(sqlalchemy.select([expr]).compile(faux_conn.engine)) == sql
+    assert str(sqlalchemy.select(expr).compile(faux_conn.engine)) == sql
 
@@ -121,3 +121,3 @@
     want = f"SELECT `t`.`person` \nFROM `t`, `t` \nWHERE {sql}"
-    got = str(sqlalchemy.select([_col()]).where(expr).compile(faux_conn.engine))
+    got = str(sqlalchemy.select(_col()).where(expr).compile(faux_conn.engine))
     assert got == want
@@ -124,0 +124,0 @@

tests/unit/test_compiler.py

@@ -24,5 +24,26 @@ # Copyright (c) 2021 The sqlalchemy-bigquery Authors
 from .conftest import setup_table
-from .conftest import sqlalchemy_1_4_or_higher, sqlalchemy_before_1_4
+from .conftest import (
+    sqlalchemy_2_0_or_higher,
+    sqlalchemy_before_2_0,
+)
+from sqlalchemy.sql.functions import rollup, cube, grouping_sets
 
 
[e-mail]
+def table(faux_conn, metadata):
+    # Fixture to create a sample table for testing
+
+    table = setup_table(
+        faux_conn,
+        "table1",
+        metadata,
+        sqlalchemy.Column("foo", sqlalchemy.Integer),
+        sqlalchemy.Column("bar", sqlalchemy.ARRAY(sqlalchemy.Integer)),
+    )
+
+    yield table
+
+    table.drop(faux_conn)
+
+
 def test_constraints_are_ignored(faux_conn, metadata):
@@ -62,3 +83,2 @@ sqlalchemy.Table(
 
-@sqlalchemy_1_4_or_higher
 def test_no_alias_for_known_tables(faux_conn, metadata):
@@ -85,3 +105,2 @@ # See: https://github.com/googleapis/python-bigquery-sqlalchemy/issues/353
 
-@sqlalchemy_1_4_or_higher
 def test_no_alias_for_known_tables_cte(faux_conn, metadata):
@@ -148,6 +167,6 @@ # See: https://github.com/googleapis/python-bigquery-sqlalchemy/issues/368
 
-@sqlalchemy_before_1_4
-def test_no_implicit_join_asterix_for_inner_unnest_before_1_4(faux_conn, metadata):
+@sqlalchemy_before_2_0
+def test_no_implicit_join_asterix_for_inner_unnest_before_2_0(faux_conn, metadata):
     # See: https://github.com/googleapis/python-bigquery-sqlalchemy/issues/368
-    q = prepare_implicit_join_base_query(faux_conn, metadata, True, True)
+    q = prepare_implicit_join_base_query(faux_conn, metadata, True, False)
     expected_initial_sql = (
@@ -160,3 +179,4 @@ "SELECT `table1`.`foo`, `table2`.`bar` \n"
 
-    q = sqlalchemy.select(["*"]).select_from(q)
+    q = q.subquery()
+    q = sqlalchemy.select("*").select_from(q)
 
@@ -166,3 +186,3 @@ expected_outer_sql = (
         "FROM (SELECT `table1`.`foo` AS `foo`, `table2`.`bar` AS `bar` \n"
-        "FROM `table2`, unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`)"
+        "FROM `table2`, unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`) AS `anon_1`"
     )
@@ -173,3 +193,3 @@ found_outer_sql = q.compile(faux_conn).string
 
-@sqlalchemy_1_4_or_higher
+@sqlalchemy_2_0_or_higher
 def test_no_implicit_join_asterix_for_inner_unnest(faux_conn, metadata):
@@ -180,3 +200,3 @@ # See: https://github.com/googleapis/python-bigquery-sqlalchemy/issues/368
         "SELECT `table1`.`foo`, `table2`.`bar` \n"
-        "FROM `table2`, unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`"
+        "FROM unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`, `table2`"
     )
@@ -192,3 +212,3 @@ found_initial_sql = q.compile(faux_conn).string
         "FROM (SELECT `table1`.`foo` AS `foo`, `table2`.`bar` AS `bar` \n"
-        "FROM `table2`, unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`) AS `anon_1`"
+        "FROM unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`, `table2`) AS `anon_1`"
     )
@@ -199,6 +219,6 @@ found_outer_sql = q.compile(faux_conn).string
 
-@sqlalchemy_before_1_4
-def test_no_implicit_join_for_inner_unnest_before_1_4(faux_conn, metadata):
+@sqlalchemy_before_2_0
+def test_no_implicit_join_for_inner_unnest_before_2_0(faux_conn, metadata):
     # See: https://github.com/googleapis/python-bigquery-sqlalchemy/issues/368
-    q = prepare_implicit_join_base_query(faux_conn, metadata, True, True)
+    q = prepare_implicit_join_base_query(faux_conn, metadata, True, False)
     expected_initial_sql = (
@@ -211,8 +231,9 @@ "SELECT `table1`.`foo`, `table2`.`bar` \n"
 
-    q = sqlalchemy.select([q.c.foo]).select_from(q)
+    q = q.subquery()
+    q = sqlalchemy.select(q.c.foo).select_from(q)
 
     expected_outer_sql = (
-        "SELECT `foo` \n"
+        "SELECT `anon_1`.`foo` \n"
         "FROM (SELECT `table1`.`foo` AS `foo`, `table2`.`bar` AS `bar` \n"
-        "FROM `table2`, unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`)"
+        "FROM `table2`, unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`) AS `anon_1`"
     )
@@ -223,3 +244,3 @@ found_outer_sql = q.compile(faux_conn).string
 
-@sqlalchemy_1_4_or_higher
+@sqlalchemy_2_0_or_higher
 def test_no_implicit_join_for_inner_unnest(faux_conn, metadata):
@@ -230,3 +251,3 @@ # See: https://github.com/googleapis/python-bigquery-sqlalchemy/issues/368
         "SELECT `table1`.`foo`, `table2`.`bar` \n"
-        "FROM `table2`, unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`"
+        "FROM unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`, `table2`"
     )
@@ -242,3 +263,3 @@ found_initial_sql = q.compile(faux_conn).string
         "FROM (SELECT `table1`.`foo` AS `foo`, `table2`.`bar` AS `bar` \n"
-        "FROM `table2`, unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`) AS `anon_1`"
+        "FROM unnest(`table2`.`foos`) AS `unnested_foos` JOIN `table1` ON `table1`.`foo` = `unnested_foos`, `table2`) AS `anon_1`"
     )
@@ -249,3 +270,2 @@ found_outer_sql = q.compile(faux_conn).string
 
-@sqlalchemy_1_4_or_higher
 def test_no_implicit_join_asterix_for_inner_unnest_no_table2_column(
@@ -275,3 +295,2 @@ faux_conn, metadata
 
-@sqlalchemy_1_4_or_higher
 def test_no_implicit_join_for_inner_unnest_no_table2_column(faux_conn, metadata):
@@ -297,1 +316,92 @@ # See: https://github.com/googleapis/python-bigquery-sqlalchemy/issues/368
     assert found_outer_sql == expected_outer_sql
+
+
+grouping_ops = (
+    "grouping_op, grouping_op_func",
+    [("GROUPING SETS", grouping_sets), ("ROLLUP", rollup), ("CUBE", cube)],
+)
+
+
[e-mail](*grouping_ops)
+def test_grouping_ops_vs_single_column(faux_conn, table, grouping_op, grouping_op_func):
+    # Tests each of the grouping ops against a single column
+
+    q = sqlalchemy.select(table.c.foo).group_by(grouping_op_func(table.c.foo))
+    found_sql = q.compile(faux_conn).string
+
+    expected_sql = (
+        f"SELECT `table1`.`foo` \n"
+        f"FROM `table1` GROUP BY {grouping_op}(`table1`.`foo`)"
+    )
+
+    assert found_sql == expected_sql
+
+
[e-mail](*grouping_ops)
+def test_grouping_ops_vs_multi_columns(faux_conn, table, grouping_op, grouping_op_func):
+    # Tests each of the grouping ops against multiple columns
+
+    q = sqlalchemy.select(table.c.foo, table.c.bar).group_by(
+        grouping_op_func(table.c.foo, table.c.bar)
+    )
+    found_sql = q.compile(faux_conn).string
+
+    expected_sql = (
+        f"SELECT `table1`.`foo`, `table1`.`bar` \n"
+        f"FROM `table1` GROUP BY {grouping_op}(`table1`.`foo`, `table1`.`bar`)"
+    )
+
+    assert found_sql == expected_sql
+
+
[e-mail](*grouping_ops)
+def test_grouping_op_with_grouping_op(faux_conn, table, grouping_op, grouping_op_func):
+    # Tests multiple grouping ops in a single statement
+
+    q = sqlalchemy.select(table.c.foo, table.c.bar).group_by(
+        grouping_op_func(table.c.foo, table.c.bar), grouping_op_func(table.c.foo)
+    )
+    found_sql = q.compile(faux_conn).string
+
+    expected_sql = (
+        f"SELECT `table1`.`foo`, `table1`.`bar` \n"
+        f"FROM `table1` GROUP BY {grouping_op}(`table1`.`foo`, `table1`.`bar`), {grouping_op}(`table1`.`foo`)"
+    )
+
+    assert found_sql == expected_sql
+
+
[e-mail](*grouping_ops)
+def test_grouping_ops_vs_group_by(faux_conn, table, grouping_op, grouping_op_func):
+    # Tests grouping op against regular group by statement
+
+    q = sqlalchemy.select(table.c.foo, table.c.bar).group_by(
+        table.c.foo, grouping_op_func(table.c.bar)
+    )
+    found_sql = q.compile(faux_conn).string
+
+    expected_sql = (
+        f"SELECT `table1`.`foo`, `table1`.`bar` \n"
+        f"FROM `table1` GROUP BY `table1`.`foo`, {grouping_op}(`table1`.`bar`)"
+    )
+
+    assert found_sql == expected_sql
+
+
[e-mail](*grouping_ops)
+def test_complex_grouping_ops_vs_nested_grouping_ops(
+    faux_conn, table, grouping_op, grouping_op_func
+):
+    # Tests grouping ops nested within grouping ops
+
+    q = sqlalchemy.select(table.c.foo, table.c.bar).group_by(
+        grouping_sets(table.c.foo, grouping_op_func(table.c.bar))
+    )
+    found_sql = q.compile(faux_conn).string
+
+    expected_sql = (
+        f"SELECT `table1`.`foo`, `table1`.`bar` \n"
+        f"FROM `table1` GROUP BY GROUPING SETS(`table1`.`foo`, {grouping_op}(`table1`.`bar`))"
+    )
+
+    assert found_sql == expected_sql

tests/unit/test_compliance.py

@@ -30,3 +30,3 @@ # Copyright (c) 2021 The sqlalchemy-bigquery Authors
 
-from .conftest import setup_table, sqlalchemy_1_3_or_higher
+from .conftest import setup_table
 
@@ -56,4 +56,4 @@
     table = some_table(faux_conn)
-    s1 = select([table]).where(table.c.id == 2).distinct()
-    s2 = select([table]).where(table.c.id == 3).distinct()
+    s1 = select(table).where(table.c.id == 2).distinct()
+    s2 = select(table).where(table.c.id == 3).distinct()
 
@@ -67,3 +67,3 @@ u1 = union(s1, s2).limit(2)
     s1 = (
-        select([table])
+        select(table)
         .where(table.c.id == 2)
@@ -76,3 +76,3 @@ .limit(1)
     s2 = (
-        select([table])
+        select(table)
         .where(table.c.id == 3)
@@ -100,3 +100,3 @@ .limit(1)
         faux_conn.scalar(
-            select([t.c.data]).where(t.c.data == literal_column("'some % value'"))
+            select(t.c.data).where(t.c.data == literal_column("'some % value'"))
         ),
@@ -108,5 +108,3 @@ "some % value",
         faux_conn.scalar(
-            select([t.c.data]).where(
-                t.c.data == literal_column("'some %% other value'")
-            )
+            select(t.c.data).where(t.c.data == literal_column("'some %% other value'"))
         ),
@@ -117,3 +115,2 @@ "some %% other value",
 
-@sqlalchemy_1_3_or_higher
 def test_empty_set_against_integer(faux_conn):
@@ -123,3 +120,3 @@ table = some_table(faux_conn)
     stmt = (
-        select([table.c.id])
+        select(table.c.id)
         .where(table.c.x.in_(sqlalchemy.bindparam("q", expanding=True)))
@@ -132,18 +129,13 @@ .order_by(table.c.id)
 
-@sqlalchemy_1_3_or_higher
 def test_null_in_empty_set_is_false(faux_conn):
     stmt = select(
-        [
-            sqlalchemy.case(
-                [
-                    (
-                        sqlalchemy.null().in_(
-                            sqlalchemy.bindparam("foo", value=(), expanding=True)
-                        ),
-                        sqlalchemy.true(),
-                    )
-                ],
-                else_=sqlalchemy.false(),
-            )
-        ]
+        sqlalchemy.case(
+            (
+                sqlalchemy.null().in_(
+                    sqlalchemy.bindparam("foo", value=(), expanding=True)
+                ),
+                sqlalchemy.true(),
+            ),
+            else_=sqlalchemy.false(),
+        )
     )
@@ -182,3 +174,3 @@ in_(faux_conn.execute(stmt).fetchone()[0], (False, 0))
     expr = getattr(table.c.data, meth)(arg)
-    rows = {value for value, in faux_conn.execute(select([table.c.id]).where(expr))}
+    rows = {value for value, in faux_conn.execute(select(table.c.id).where(expr))}
     eq_(rows, expected)
@@ -188,3 +180,3 @@
     expr = sqlalchemy.not_(expr)
-    rows = {value for value, in faux_conn.execute(select([table.c.id]).where(expr))}
+    rows = {value for value, in faux_conn.execute(select(table.c.id).where(expr))}
     eq_(rows, all - expected)
@@ -210,5 +202,3 @@
     expr = (table.c.x + table.c.y).label("lx")
-    stmt = (
-        select([sqlalchemy.func.count(table.c.id), expr]).group_by(expr).order_by(expr)
-    )
+    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)])
@@ -215,0 +205,0 @@

tests/unit/test_geography.py

@@ -79,3 +79,3 @@ # Copyright (c) 2021 The PyBigQuery Authors
     try:
-        conn.execute(select([lake_table]))
+        conn.execute(select(lake_table))
     except Exception:
@@ -93,4 +93,4 @@ pass  # sqlite had no special functions :)
         conn.execute(
-            select(
-                [lake_table.c.name], func.ST_Contains(lake_table.c.geog, "POINT(4 1)")
+            select(lake_table.c.name).where(
+                func.ST_Contains(lake_table.c.geog, "POINT(4 1)")
             )
@@ -109,3 +109,3 @@ )
         conn.execute(
-            select([lake_table.c.name, lake_table.c.geog.ST_Area().label("area")])
+            select(lake_table.c.name, lake_table.c.geog.ST_Area().label("area"))
         )
@@ -177,3 +177,3 @@ except Exception:
     try:
-        faux_conn.execute(select([func.ST_GeogFromText("point(0 0)")]))
+        faux_conn.execute(select(func.ST_GeogFromText("point(0 0)")))
     except Exception:
@@ -180,0 +180,0 @@ pass  # sqlite had no special functions :)

tests/unit/test_select.py

@@ -23,3 +23,2 @@ # Copyright (c) 2021 The sqlalchemy-bigquery Authors
 
-import packaging.version
 import pytest
@@ -31,9 +30,3 @@ import sqlalchemy
 
-from .conftest import (
-    setup_table,
-    sqlalchemy_version,
-    sqlalchemy_1_3_or_higher,
-    sqlalchemy_1_4_or_higher,
-    sqlalchemy_before_1_4,
-)
+from .conftest import setup_table
 
@@ -43,3 +36,3 @@
     table = setup_table(faux_conn, "t", sqlalchemy.Column("id", sqlalchemy.Integer))
-    result = faux_conn.execute(sqlalchemy.select([table.c.id]))
+    result = faux_conn.execute(sqlalchemy.select(table.c.id))
     assert result.keys() == ["id"]  # Look! Just the column name!
@@ -160,3 +153,3 @@
 
-    assert list(map(list, faux_conn.execute(sqlalchemy.select([table])))) == [[val]] * 2
+    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`"
@@ -168,3 +161,7 @@
                 list,
-                faux_conn.execute(sqlalchemy.select([table.c.foo], use_labels=True)),
+                faux_conn.execute(
+                    sqlalchemy.select(table.c.foo).set_label_style(
+                        sqlalchemy.LABEL_STYLE_TABLENAME_PLUS_COL
+                    )
+                ),
             )
@@ -191,3 +188,3 @@ )
 
-    row = list(faux_conn.execute(sqlalchemy.select([table])))[0]
+    row = list(faux_conn.execute(sqlalchemy.select(table)))[0]
     # We expect the raw string, because sqlite3, unlike BigQuery
@@ -200,3 +197,3 @@ # doesn't deserialize for us.
     # Make sure label names are legal identifiers
-    faux_conn.execute(sqlalchemy.select([sqlalchemy.literal(1).label("2foo")]))
+    faux_conn.execute(sqlalchemy.select(sqlalchemy.literal(1).label("2foo")))
     assert (
@@ -215,3 +212,3 @@ faux_conn.test_data["execute"][-1][0] == "SELECT %(param_1:INT64)s AS `_2foo`"
     )
-    faux_conn.execute(sqlalchemy.select([table]))
+    faux_conn.execute(sqlalchemy.select(table))
     assert faux_conn.test_data["execute"][-1][0] == ("SELECT `t`.`foo` \nFROM `t`")
@@ -228,22 +225,8 @@
     )
-    faux_conn.execute(sqlalchemy.select([table]))
+    faux_conn.execute(sqlalchemy.select(table))
     assert faux_conn.test_data["execute"][-1][0] == ("SELECT `t`.foo \nFROM `t`")
 
 
-@sqlalchemy_before_1_4
-def test_select_in_lit_13(faux_conn):
-    [[isin]] = faux_conn.execute(
-        sqlalchemy.select([sqlalchemy.literal(1).in_([1, 2, 3])])
-    )
-    assert isin
-    assert faux_conn.test_data["execute"][-1] == (
-        "SELECT %(param_1:INT64)s IN "
-        "(%(param_2:INT64)s, %(param_3:INT64)s, %(param_4:INT64)s) AS `anon_1`",
-        {"param_1": 1, "param_2": 1, "param_3": 2, "param_4": 3},
-    )
-
-
-@sqlalchemy_1_4_or_higher
 def test_select_in_lit(faux_conn, last_query):
-    faux_conn.execute(sqlalchemy.select([sqlalchemy.literal(1).in_([1, 2, 3])]))
+    faux_conn.execute(sqlalchemy.select(sqlalchemy.literal(1).in_([1, 2, 3])))
     last_query(
@@ -256,79 +239,43 @@ "SELECT %(param_1:INT64)s IN UNNEST(%(param_2:INT64)s) AS `anon_1`",
 def test_select_in_param(faux_conn, last_query):
-    [[isin]] = faux_conn.execute(
+    faux_conn.execute(
         sqlalchemy.select(
-            [sqlalchemy.literal(1).in_(sqlalchemy.bindparam("q", expanding=True))]
+            sqlalchemy.literal(1).in_(sqlalchemy.bindparam("q", expanding=True))
         ),
         dict(q=[1, 2, 3]),
     )
-    if sqlalchemy_version >= packaging.version.parse("1.4"):
-        last_query(
-            "SELECT %(param_1:INT64)s IN UNNEST(%(q:INT64)s) AS `anon_1`",
-            {"param_1": 1, "q": [1, 2, 3]},
-        )
-    else:
-        assert isin
-        last_query(
-            "SELECT %(param_1:INT64)s IN UNNEST("
-            "[ %(q_1:INT64)s, %(q_2:INT64)s, %(q_3:INT64)s ]"
-            ") AS `anon_1`",
-            {"param_1": 1, "q_1": 1, "q_2": 2, "q_3": 3},
-        )
 
+    last_query(
+        "SELECT %(param_1:INT64)s IN UNNEST(%(q:INT64)s) AS `anon_1`",
+        {"param_1": 1, "q": [1, 2, 3]},
+    )
 
+
 def test_select_in_param1(faux_conn, last_query):
-    [[isin]] = faux_conn.execute(
+    faux_conn.execute(
         sqlalchemy.select(
-            [sqlalchemy.literal(1).in_(sqlalchemy.bindparam("q", expanding=True))]
+            sqlalchemy.literal(1).in_(sqlalchemy.bindparam("q", expanding=True))
         ),
         dict(q=[1]),
     )
-    if sqlalchemy_version >= packaging.version.parse("1.4"):
-        last_query(
-            "SELECT %(param_1:INT64)s IN UNNEST(%(q:INT64)s) AS `anon_1`",
-            {"param_1": 1, "q": [1]},
-        )
-    else:
-        assert isin
-        last_query(
-            "SELECT %(param_1:INT64)s IN UNNEST(" "[ %(q_1:INT64)s ]" ") AS `anon_1`",
-            {"param_1": 1, "q_1": 1},
-        )
+    last_query(
+        "SELECT %(param_1:INT64)s IN UNNEST(%(q:INT64)s) AS `anon_1`",
+        {"param_1": 1, "q": [1]},
+    )
 
 
-@sqlalchemy_1_3_or_higher
 def test_select_in_param_empty(faux_conn, last_query):
-    [[isin]] = faux_conn.execute(
+    faux_conn.execute(
         sqlalchemy.select(
-            [sqlalchemy.literal(1).in_(sqlalchemy.bindparam("q", expanding=True))]
+            sqlalchemy.literal(1).in_(sqlalchemy.bindparam("q", expanding=True))
         ),
         dict(q=[]),
     )
-    if sqlalchemy_version >= packaging.version.parse("1.4"):
-        last_query(
-            "SELECT %(param_1:INT64)s IN UNNEST(%(q:INT64)s) AS `anon_1`",
-            {"param_1": 1, "q": []},
-        )
-    else:
-        assert not isin
-        last_query(
-            "SELECT %(param_1:INT64)s IN UNNEST([  ]) AS `anon_1`", {"param_1": 1}
-        )
-
-
-@sqlalchemy_before_1_4
-def test_select_notin_lit13(faux_conn):
-    [[isnotin]] = faux_conn.execute(
-        sqlalchemy.select([sqlalchemy.literal(0).notin_([1, 2, 3])])
+    last_query(
+        "SELECT %(param_1:INT64)s IN UNNEST(%(q:INT64)s) AS `anon_1`",
+        {"param_1": 1, "q": []},
     )
-    assert isnotin
-    assert faux_conn.test_data["execute"][-1] == (
-        "SELECT (%(param_1:INT64)s NOT IN "
-        "(%(param_2:INT64)s, %(param_3:INT64)s, %(param_4:INT64)s)) AS `anon_1`",
-        {"param_1": 0, "param_2": 1, "param_3": 2, "param_4": 3},
-    )
 
 
-@sqlalchemy_1_4_or_higher
 def test_select_notin_lit(faux_conn, last_query):
-    faux_conn.execute(sqlalchemy.select([sqlalchemy.literal(0).notin_([1, 2, 3])]))
+    faux_conn.execute(sqlalchemy.select(sqlalchemy.literal(0).notin_([1, 2, 3])))
     last_query(
@@ -341,41 +288,25 @@ "SELECT (%(param_1:INT64)s NOT IN UNNEST(%(param_2:INT64)s)) AS `anon_1`",
 def test_select_notin_param(faux_conn, last_query):
-    [[isnotin]] = faux_conn.execute(
+    faux_conn.execute(
         sqlalchemy.select(
-            [sqlalchemy.literal(1).notin_(sqlalchemy.bindparam("q", expanding=True))]
+            sqlalchemy.literal(1).notin_(sqlalchemy.bindparam("q", expanding=True))
         ),
         dict(q=[1, 2, 3]),
     )
-    if sqlalchemy_version >= packaging.version.parse("1.4"):
-        last_query(
-            "SELECT (%(param_1:INT64)s NOT IN UNNEST(%(q:INT64)s)) AS `anon_1`",
-            {"param_1": 1, "q": [1, 2, 3]},
-        )
-    else:
-        assert not isnotin
-        last_query(
-            "SELECT (%(param_1:INT64)s NOT IN UNNEST("
-            "[ %(q_1:INT64)s, %(q_2:INT64)s, %(q_3:INT64)s ]"
-            ")) AS `anon_1`",
-            {"param_1": 1, "q_1": 1, "q_2": 2, "q_3": 3},
-        )
+    last_query(
+        "SELECT (%(param_1:INT64)s NOT IN UNNEST(%(q:INT64)s)) AS `anon_1`",
+        {"param_1": 1, "q": [1, 2, 3]},
+    )
 
 
-@sqlalchemy_1_3_or_higher
 def test_select_notin_param_empty(faux_conn, last_query):
-    [[isnotin]] = faux_conn.execute(
+    faux_conn.execute(
         sqlalchemy.select(
-            [sqlalchemy.literal(1).notin_(sqlalchemy.bindparam("q", expanding=True))]
+            sqlalchemy.literal(1).notin_(sqlalchemy.bindparam("q", expanding=True))
         ),
         dict(q=[]),
     )
-    if sqlalchemy_version >= packaging.version.parse("1.4"):
-        last_query(
-            "SELECT (%(param_1:INT64)s NOT IN UNNEST(%(q:INT64)s)) AS `anon_1`",
-            {"param_1": 1, "q": []},
-        )
-    else:
-        assert isnotin
-        last_query(
-            "SELECT (%(param_1:INT64)s NOT IN UNNEST([  ])) AS `anon_1`", {"param_1": 1}
-        )
+    last_query(
+        "SELECT (%(param_1:INT64)s NOT IN UNNEST(%(q:INT64)s)) AS `anon_1`",
+        {"param_1": 1, "q": []},
+    )
 
@@ -390,3 +321,3 @@
     )
-    q = sqlalchemy.select([table.c.val]).where(table.c.val.in_([2]))
+    q = sqlalchemy.select(table.c.val).where(table.c.val.in_([2]))
 
@@ -402,3 +333,2 @@ def nstr(q):
 
-@sqlalchemy_1_4_or_higher
 @pytest.mark.parametrize("alias", [True, False])
@@ -421,3 +351,2 @@ def test_unnest(faux_conn, alias):
 
-@sqlalchemy_1_4_or_higher
 @pytest.mark.parametrize("alias", [True, False])
@@ -441,3 +370,2 @@ def test_table_valued_alias_w_multiple_references_to_the_same_table(faux_conn, alias):
 
-@sqlalchemy_1_4_or_higher
 @pytest.mark.parametrize("alias", [True, False])
@@ -462,10 +390,6 @@ def test_unnest_w_no_table_references(faux_conn, alias):
     )
-    got = str(sqlalchemy.select([t.c.a[0]]).compile(faux_conn.engine))
+    got = str(sqlalchemy.select(t.c.a[0]).compile(faux_conn.engine))
     assert got == "SELECT `t`.`a`[OFFSET(%(a_1:INT64)s)] AS `anon_1` \nFROM `t`"
 
 
[e-mail](
-    packaging.version.parse(sqlalchemy.__version__) < packaging.version.parse("1.4"),
-    reason="regexp_match support requires version 1.4 or higher",
-)
 def test_visit_regexp_match_op_binary(faux_conn):
@@ -487,6 +411,2 @@ table = setup_table(
 
[e-mail](
-    packaging.version.parse(sqlalchemy.__version__) < packaging.version.parse("1.4"),
-    reason="regexp_match support requires version 1.4 or higher",
-)
 def test_visit_not_regexp_match_op_binary(faux_conn):
@@ -493,0 +413,0 @@ table = setup_table(

tests/unit/test_sqlalchemy_bigquery.py

@@ -13,3 +13,2 @@ # Copyright 2021 The sqlalchemy-bigquery Authors
 from google.cloud.bigquery.table import TableListItem
-import packaging.version
 import pytest
@@ -102,3 +101,3 @@ import sqlalchemy
     mock_bigquery_client.list_tables.side_effect = tables_lists
-    table_names = engine_under_test.table_names()
+    table_names = sqlalchemy.inspect(engine_under_test).get_table_names()
     mock_bigquery_client.list_datasets.assert_called_once()
@@ -232,8 +231,3 @@ assert mock_bigquery_client.list_tables.call_count == len(datasets_list)
     assert isinstance(f.type, sqlalchemy.String)
-    if packaging.version.parse(sqlalchemy.__version__) >= packaging.version.parse(
-        "1.4"
-    ):
-        assert isinstance(
-            sqlalchemy.select([f]).subquery().c.unnest.type, sqlalchemy.String
-        )
+    assert isinstance(sqlalchemy.select(f).subquery().c.unnest.type, sqlalchemy.String)
 
@@ -240,0 +234,0 @@

Alert delta unavailable

Currently unable to show alert delta for PyPI packages.

Improved metrics

Total package byte prevSize

569590

2.66%

Lines of code

8702

4.33%