Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit aa1a557

Browse filesBrowse files
CaselITGerrit Code Review
CaselIT
authored and
Gerrit Code Review
committed
Merge "Add has_multi_table reflection method" into main
2 parents 7f5db21 + e32fe92 commit aa1a557
Copy full SHA for aa1a557

11 files changed

+408-126Lines changed: 408 additions & 126 deletions

File tree

Expand file treeCollapse file tree
Open diff view settings
Filter options
Expand file treeCollapse file tree
Open diff view settings
Collapse file

‎doc/build/changelog/unreleased_21/13311.rst‎

Copy file name to clipboard
+15Lines changed: 15 additions & 0 deletions
  • Display the source diff
  • Display the rich diff
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
.. change::
2+
:tags: feature, reflection, performance
3+
:tickets: 13311
4+
5+
Created new reflection method :meth:`_engine.Inspector.has_multi_table`
6+
to check the existence of multiple tables at once, allowing for
7+
better performance when checking many tables. Like the other "multi"
8+
reflection methods, the default dialect offers a default implementation
9+
that just call the single method in a loop. Backends that wish to take
10+
advantage of this new method can implement it in their dialects.
11+
The PostgreSQL, Oracle and SQL Server dialects have been updated to use
12+
this new method.
13+
The implementation of :meth:`_schema.MetaData.create_all` has been updated
14+
to make use of this new method to check the existence of the tables,
15+
reducing the number of round trips to the database when creating many tables.
Collapse file

‎lib/sqlalchemy/dialects/mssql/base.py‎

Copy file name to clipboardExpand all lines: lib/sqlalchemy/dialects/mssql/base.py
+89-47Lines changed: 89 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -3373,11 +3373,29 @@ def _get_default_schema_name(self, connection):
33733373
else:
33743374
return self.schema_name
33753375

3376+
@reflection.cache
33763377
@_db_plus_owner
33773378
def has_table(self, connection, tablename, dbname, owner, schema, **kw):
33783379
self._ensure_has_table_connection(connection)
33793380

3380-
return self._internal_has_table(connection, tablename, owner, **kw)
3381+
return self._internal_has_multi_table(
3382+
connection, [tablename], owner, schema, **kw
3383+
)[schema, tablename]
3384+
3385+
def has_multi_table(self, connection, table_names, schema=None, **kw):
3386+
# this does not really matche the signature of _db_plus_owner_multi
3387+
# so manually do the unpacking here
3388+
dbname, owner = _owner_plus_db(self, schema)
3389+
return _switch_db(
3390+
dbname,
3391+
connection,
3392+
self._internal_has_multi_table,
3393+
connection,
3394+
table_names,
3395+
owner,
3396+
schema,
3397+
**kw,
3398+
).items()
33813399

33823400
@reflection.cache
33833401
@_db_plus_owner
@@ -3452,37 +3470,38 @@ def get_view_names(self, connection, dbname, owner, schema, **kw):
34523470
view_names = [r[0] for r in connection.execute(s)]
34533471
return view_names
34543472

3455-
@reflection.cache
3456-
def _internal_has_table(self, connection, tablename, owner, **kw):
3457-
if tablename.startswith("#"): # temporary table
3458-
# mssql does not support temporary views
3459-
# SQL Error [4103] [S0001]: "#v": Temporary views are not allowed
3460-
return bool(
3461-
connection.scalar(
3462-
# U filters on user tables only.
3463-
text("SELECT object_id(:table_name, 'U')"),
3464-
{"table_name": f"tempdb.dbo.[{tablename}]"},
3465-
)
3466-
)
3467-
else:
3468-
tables = ischema.tables
3469-
3470-
s = sql.select(tables.c.table_name).where(
3471-
sql.and_(
3472-
sql.or_(
3473-
tables.c.table_type == "BASE TABLE",
3474-
tables.c.table_type == "VIEW",
3475-
),
3476-
tables.c.table_name == tablename,
3477-
)
3473+
def _internal_has_multi_table(
3474+
self, connection, table_names, owner, schema, **kw
3475+
):
3476+
regular_names, multi_object_names, temp_names = (
3477+
self._partition_filter_names(
3478+
connection, owner, table_names, ObjectScope.ANY, ObjectKind.ANY
34783479
)
3480+
)
34793481

3480-
if owner:
3481-
s = s.where(tables.c.table_schema == owner)
3482+
result = {}
3483+
if multi_object_names:
3484+
# in multi_object_names we already have the names that exist in
3485+
# the database, no need to do another query here
3486+
name_map = self._multi_name_map(regular_names)
3487+
for server_name in multi_object_names:
3488+
result[(schema, name_map(server_name))] = True
34823489

3483-
c = connection.execute(s)
3490+
if temp_names:
3491+
# mssql does not support temporary views
3492+
# U filters on user tables only.
3493+
query = text("SELECT object_id(:table_name, 'U')")
3494+
for temp_name in temp_names:
3495+
result[(schema, temp_name)] = bool(
3496+
connection.scalar(
3497+
query,
3498+
{"table_name": f"tempdb..[{temp_name}]"},
3499+
)
3500+
)
34843501

3485-
return c.first() is not None
3502+
for name in table_names:
3503+
result.setdefault((schema, name), False)
3504+
return result
34863505

34873506
@reflection.cache
34883507
@_db_plus_owner
@@ -3853,6 +3872,34 @@ def _fk_query_sql(fk_info_where, extra_cols=""):
38533872

38543873
# --- multi-reflection API ---
38553874

3875+
@lru_cache
3876+
def _partition_filter_names_query(self, add_filter_names: bool):
3877+
sys_objects = ischema.sys_objects
3878+
sys_schemas = ischema.sys_schemas
3879+
3880+
s = (
3881+
sql.select(sys_objects.c.name)
3882+
.select_from(sys_objects)
3883+
.join(
3884+
sys_schemas,
3885+
onclause=sys_objects.c.schema_id == sys_schemas.c.schema_id,
3886+
)
3887+
.where(
3888+
sys_schemas.c.name == sql.bindparam("schema"),
3889+
sys_objects.c.type.in_(
3890+
sql.bindparam("type_filter", expanding=True)
3891+
),
3892+
)
3893+
)
3894+
if add_filter_names:
3895+
s = s.where(
3896+
sys_objects.c.name.in_(
3897+
sql.bindparam("filter_names", expanding=True)
3898+
)
3899+
)
3900+
3901+
return s
3902+
38563903
def _partition_filter_names(
38573904
self, connection, owner, filter_names, scope, kind
38583905
):
@@ -3914,29 +3961,20 @@ def _partition_filter_names(
39143961

39153962
type_filter = []
39163963
if ObjectKind.TABLE in kind:
3917-
type_filter.append("'U'")
3964+
type_filter.append("U")
39183965
if ObjectKind.VIEW in kind:
3919-
type_filter.append("'V'")
3966+
type_filter.append("V")
39203967
# SQL Server does not support materialized views, so ignore them
39213968
if not type_filter:
39223969
return (regular_names, [], temp_names)
39233970

3924-
query = (
3925-
"SELECT o.name "
3926-
"FROM sys.objects o "
3927-
"JOIN sys.schemas s ON o.schema_id = s.schema_id "
3928-
"WHERE s.name = :owner "
3929-
"AND o.type IN (%s)" % ", ".join(type_filter)
3930-
)
3931-
params = [sql.bindparam("owner", owner, ischema.CoerceUnicode())]
3932-
if regular_names:
3933-
query += " AND o.name IN :filter_names"
3934-
params.append(
3935-
sql.bindparam("filter_names", regular_names, expanding=True)
3936-
)
3971+
has_regular_names = bool(regular_names)
3972+
query = self._partition_filter_names_query(has_regular_names)
3973+
params = {"schema": owner, "type_filter": type_filter}
3974+
if has_regular_names:
3975+
params["filter_names"] = regular_names
39373976

3938-
rp = connection.execute(sql.text(query).bindparams(*params))
3939-
multi_object_names = [row[0] for row in rp]
3977+
multi_object_names = connection.scalars(query, params).all()
39403978

39413979
return (regular_names, multi_object_names, temp_names)
39423980

@@ -4305,9 +4343,13 @@ def get_multi_foreign_keys(
43054343
# return empty foreign_keys for temp tables that exist. We
43064344
# preserve that here by checking existence and returning the
43074345
# default (empty) reflection for each temp that is reachable.
4346+
has_temp_tables = self._internal_has_multi_table(
4347+
connection, temp_names, owner="dbo", schema=schema, **kw
4348+
)
43084349
for name in temp_names:
4309-
if self._internal_has_table(connection, name, owner="dbo"):
4310-
final[(schema, name)] = ReflectionDefaults.foreign_keys()
4350+
key = (schema, name)
4351+
if has_temp_tables.get(key):
4352+
final[key] = ReflectionDefaults.foreign_keys()
43114353

43124354
return final.items()
43134355

Collapse file

‎lib/sqlalchemy/dialects/oracle/base.py‎

Copy file name to clipboardExpand all lines: lib/sqlalchemy/dialects/oracle/base.py
+15-13Lines changed: 15 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -2311,7 +2311,7 @@ def visit_bindparam(bindparam):
23112311
)
23122312

23132313
@util.memoized_property
2314-
def _has_table_query(self):
2314+
def _has_multi_table_query(self):
23152315
# materialized views are returned by all_tables
23162316
tables = (
23172317
select(
@@ -2328,33 +2328,35 @@ def _has_table_query(self):
23282328
)
23292329

23302330
query = select(tables.c.table_name).where(
2331-
tables.c.table_name == bindparam("table_name"),
2331+
tables.c.table_name.in_(bindparam("table_names")),
23322332
tables.c.owner == bindparam("owner"),
23332333
)
23342334
return query
23352335

2336-
@reflection.cache
2337-
def has_table(
2338-
self, connection, table_name, schema=None, dblink=None, **kw
2336+
def has_multi_table(
2337+
self, connection, table_names, schema=None, dblink=None, **kw
23392338
):
23402339
"""Supported kw arguments are: ``dblink`` to reflect via a db link."""
2341-
self._ensure_has_table_connection(connection)
2342-
2343-
if not schema:
2344-
schema = self.default_schema_name
23452340

2341+
owner = schema or self.default_schema_name
2342+
db_tns = [self.denormalize_name(tn) for tn in table_names]
23462343
params = {
2347-
"table_name": self.denormalize_name(table_name),
2348-
"owner": self.denormalize_schema_name(schema),
2344+
"table_names": db_tns,
2345+
"owner": self.denormalize_schema_name(owner),
23492346
}
23502347
cursor = self._execute_reflection(
23512348
connection,
2352-
self._has_table_query,
2349+
self._has_multi_table_query,
23532350
dblink,
23542351
returns_long=False,
23552352
params=params,
23562353
)
2357-
return bool(cursor.scalar())
2354+
existing = set(cursor.scalars().all())
2355+
retval = {
2356+
(schema, table): db_tn in existing
2357+
for table, db_tn in zip(table_names, db_tns)
2358+
}
2359+
return retval.items()
23582360

23592361
@reflection.cache
23602362
def has_sequence(
Collapse file

‎lib/sqlalchemy/dialects/postgresql/base.py‎

Copy file name to clipboardExpand all lines: lib/sqlalchemy/dialects/postgresql/base.py
+15-4Lines changed: 15 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3906,9 +3906,11 @@ def _pg_class_relkind_condition(self, relkinds, pg_class_table=None):
39063906
return pg_class_table.c.relkind == sql.any_(_array.array(relkinds))
39073907

39083908
@lru_cache()
3909-
def _has_table_query(self, schema):
3909+
def _has_multi_table_query(self, schema):
39103910
query = select(pg_catalog.pg_class.c.relname).where(
3911-
pg_catalog.pg_class.c.relname == bindparam("table_name"),
3911+
pg_catalog.pg_class.c.relname.in_(
3912+
bindparam("table_names", expanding=True)
3913+
),
39123914
self._pg_class_relkind_condition(
39133915
pg_catalog.RELKINDS_ALL_TABLE_LIKE
39143916
),
@@ -3919,9 +3921,18 @@ def _has_table_query(self, schema):
39193921

39203922
@reflection.cache
39213923
def has_table(self, connection, table_name, schema=None, **kw):
3924+
# NOTE: it's not worth calling into the multi table since the query
3925+
# is compatible also with this single case.
39223926
self._ensure_has_table_connection(connection)
3923-
query = self._has_table_query(schema)
3924-
return bool(connection.scalar(query, {"table_name": table_name}))
3927+
query = self._has_multi_table_query(schema)
3928+
return bool(connection.scalar(query, {"table_names": [table_name]}))
3929+
3930+
def has_multi_table(self, connection, table_names, schema=None, **kw):
3931+
query = self._has_multi_table_query(schema)
3932+
params = {"table_names": table_names}
3933+
existing = set(connection.scalars(query, params).all())
3934+
retval = {(schema, table): table in existing for table in table_names}
3935+
return retval.items()
39253936

39263937
@reflection.cache
39273938
def has_sequence(self, connection, sequence_name, schema=None, **kw):
Collapse file

‎lib/sqlalchemy/engine/default.py‎

Copy file name to clipboardExpand all lines: lib/sqlalchemy/engine/default.py
+33Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@
2727
from typing import cast
2828
from typing import Dict
2929
from typing import Final
30+
from typing import Iterable
3031
from typing import List
3132
from typing import Literal
3233
from typing import Mapping
@@ -88,6 +89,7 @@
8889
from .interfaces import DBAPIModule
8990
from .interfaces import DBAPIType
9091
from .interfaces import IsolationLevel
92+
from .interfaces import TableKey
9193
from .row import Row
9294
from .url import URL
9395
from ..event import _ListenerFnType
@@ -128,6 +130,26 @@ class _BackendsMultiReflection(Dialect):
128130
(PostgreSQL, Oracle, MSSQL).
129131
"""
130132

133+
@reflection.cache
134+
def has_table(
135+
self,
136+
connection: Connection,
137+
table_name: str,
138+
schema: Optional[str] = None,
139+
**kw: Any,
140+
) -> bool:
141+
# NOTE: assume it's a subclass of DefaultDialect
142+
self._ensure_has_table_connection(connection) # type: ignore
143+
multi_res = self.has_multi_table(
144+
connection,
145+
table_names=[table_name],
146+
schema=schema,
147+
**kw,
148+
)
149+
# has_multi_table returns all the input table names so it's not
150+
# possible for the key to be missing
151+
return dict(multi_res)[(schema, table_name)]
152+
131153
def _value_or_raise(self, data, table, schema):
132154
try:
133155
return dict(data)[(schema, table)]
@@ -1273,6 +1295,17 @@ def _default_multi_reflect(
12731295
except exc.NoSuchTableError:
12741296
pass
12751297

1298+
def has_multi_table(
1299+
self,
1300+
connection: Connection,
1301+
table_names: Sequence[str],
1302+
schema: Optional[str] = None,
1303+
**kw: Any,
1304+
) -> Iterable[tuple[TableKey, bool]]:
1305+
for table_name in table_names:
1306+
exist = self.has_table(connection, table_name, schema=schema, **kw)
1307+
yield (schema, table_name), exist
1308+
12761309
def get_multi_table_options(self, connection, **kw):
12771310
return self._default_multi_reflect(
12781311
self.get_table_options, connection, **kw
Collapse file

‎lib/sqlalchemy/engine/interfaces.py‎

Copy file name to clipboardExpand all lines: lib/sqlalchemy/engine/interfaces.py
+24Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1913,6 +1913,30 @@ def has_table(
19131913

19141914
raise NotImplementedError()
19151915

1916+
def has_multi_table(
1917+
self,
1918+
connection: Connection,
1919+
table_names: Sequence[str],
1920+
schema: Optional[str] = None,
1921+
**kw: Any,
1922+
) -> Iterable[Tuple[TableKey, bool]]:
1923+
"""For internal dialect use, check the existence of a particular list
1924+
of tables or views in the database.
1925+
1926+
This is an internal dialect method. Applications should use
1927+
:meth:`.Inspector.has_multi_table`.
1928+
1929+
.. note:: The :class:`_engine.DefaultDialect` provides a default
1930+
implementation that will call the single table method for
1931+
each table name provided. Dialects that want to support a faster
1932+
implementation should implement this method.
1933+
1934+
.. versionadded:: 2.1
1935+
1936+
"""
1937+
1938+
raise NotImplementedError()
1939+
19161940
def has_index(
19171941
self,
19181942
connection: Connection,

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.