Skip to content

Commit 939de24

Browse files
zzzeekzzzeek
committed
propose emulated setinputsizes embedded in the compiler
Add a new system so that PostgreSQL and other dialects have a reliable way to add casts to bound parameters in SQL statements, replacing previous use of setinputsizes() for PG dialects. rationale: 1. psycopg3 will be using the same SQLAlchemy-side "setinputsizes" as asyncpg, so we will be seeing a lot more of this 2. the full rendering that SQLAlchemy's compilation is performing is in the engine log as well as error messages. Without this, we introduce three levels of SQL rendering, the compiler, the hidden "setinputsizes" in SQLAlchemy, and then whatever the DBAPI driver does. With this new approach, users reporting bugs etc. will be less confused that there are as many as two separate layers of "hidden rendering"; SQLAlchemy's rendering is again fully transparent 3. calling upon a setinputsizes() method for every statement execution is expensive. this way, the work is done behind the caching layer 4. for "fast insertmany()", I also want there to be a fast approach towards setinputsizes. As it was, we were going to be taking a SQL INSERT with thousands of bound parameter placeholders and running a whole second pass on it to apply typecasts. this way, we will at least be able to build the SQL string once without a huge second pass over the whole string 5. psycopg2 can use this same system for its ARRAY casts 6. the general need for PostgreSQL to have lots of type casts is now mostly in the base PostgreSQL dialect and works independently of a DBAPI being present. dependence on DBAPI symbols that aren't complete / consistent / hashable is removed I was originally going to try to build this into bind_expression(), but it was revealed this worked poorly with custom bind_expression() as well as empty sets. the current impl also doesn't need to run a second expression pass over the POSTCOMPILE sections, which came out better than I originally thought it would. Change-Id: I363e6d593d059add7bcc6d1f6c3f91dd2e683c0c
1 parent d3a4e96 commit 939de24

25 files changed

Lines changed: 771 additions & 443 deletions

File tree

‎doc/build/changelog/unreleased_20/postgresql_binds.rst‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
.. change::
2+
:tags: change, postgresql
3+
4+
In support of new PostgreSQL features including the psycopg3 dialect as
5+
well as extended "fast insertmany" support, the system by which typing
6+
information for bound parameters is passed to the PostgreSQL database has
7+
been redesigned to use inline casts emitted by the SQL compiler, and is now
8+
applied to all PostgreSQL dialects. This is in contrast to the previous
9+
approach which would rely upon the DBAPI in use to render these casts
10+
itself, which in cases such as that of pg8000 and the adapted asyncpg
11+
driver, would use the pep-249 ``setinputsizes()`` method, or with the
12+
psycopg2 driver would rely on the driver itself in most cases, with some
13+
special exceptions made for ARRAY.
14+
15+
The new approach now has all PostgreSQL dialects rendering these casts as
16+
needed using PostgreSQL double-colon style within the compiler, and the use
17+
of ``setinputsizes()`` is removed for PostgreSQL dialects, as this was not
18+
generally part of these DBAPIs in any case (pg8000 being the only
19+
exception, which added the method at the request of SQLAlchemy developers).
20+
21+
Advantages to this approach include per-statement performance, as no second
22+
pass over the compiled statement is required at execution time, better
23+
support for all DBAPIs, as there is now one consistent system of applying
24+
typing information, and improved transparency, as the SQL logging output,
25+
as well as the string output of a compiled statement, will show these casts
26+
present in the statement directly, whereas previously these casts were not
27+
visible in logging output as they would occur after the statement were
28+
logged.
29+
30+

‎doc/build/core/internals.rst‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,6 +7,9 @@ Some key internal constructs are listed here.
77

88
.. currentmodule:: sqlalchemy
99

10+
.. autoclass:: sqlalchemy.engine.BindTyping
11+
:members:
12+
1013
.. autoclass:: sqlalchemy.engine.Compiled
1114
:members:
1215

‎lib/sqlalchemy/connectors/pyodbc.py‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,7 @@
99

1010
from . import Connector
1111
from .. import util
12+
from ..engine import interfaces
1213

1314

1415
class PyODBCConnector(Connector):
@@ -21,15 +22,14 @@ class PyODBCConnector(Connector):
2122
supports_native_decimal = True
2223
default_paramstyle = "named"
2324

24-
use_setinputsizes = False
25-
2625
# for non-DSN connections, this *may* be used to
2726
# hold the desired driver name
2827
pyodbc_driver_name = None
2928

3029
def __init__(self, use_setinputsizes=False, **kw):
3130
super(PyODBCConnector, self).__init__(**kw)
32-
self.use_setinputsizes = use_setinputsizes
31+
if use_setinputsizes:
32+
self.bind_typing = interfaces.BindTyping.SETINPUTSIZES
3333

3434
@classmethod
3535
def dbapi(cls):
@@ -160,8 +160,9 @@ def do_set_input_sizes(self, cursor, list_of_tuples, context):
160160
# for types such as pyodbc.SQL_WLONGVARCHAR, which is the datatype
161161
# that ticket #5649 is targeting.
162162

163-
# NOTE: as of #6058, this won't be called if the use_setinputsizes flag
164-
# is False, or if no types were specified in list_of_tuples
163+
# NOTE: as of #6058, this won't be called if the use_setinputsizes
164+
# parameter were not passed to the dialect, or if no types were
165+
# specified in list_of_tuples
165166

166167
cursor.setinputsizes(
167168
[

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

Lines changed: 6 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -446,6 +446,7 @@ def _remove_clob(inputsizes, cursor, statement, parameters, context):
446446
from ... import types as sqltypes
447447
from ... import util
448448
from ...engine import cursor as _cursor
449+
from ...engine import interfaces
449450

450451

451452
class _OracleInteger(sqltypes.Integer):
@@ -783,8 +784,6 @@ def pre_exec(self):
783784

784785
self._generate_cursor_outputtype_handler()
785786

786-
self.include_set_input_sizes = self.dialect._include_setinputsizes
787-
788787
def post_exec(self):
789788
if self.compiled and self.out_parameters and self.compiled.returning:
790789
# create a fake cursor result from the out parameters. unlike
@@ -833,7 +832,7 @@ class OracleDialect_cx_oracle(OracleDialect):
833832
supports_sane_rowcount = True
834833
supports_sane_multi_rowcount = True
835834

836-
use_setinputsizes = True
835+
bind_typing = interfaces.BindTyping.SETINPUTSIZES
837836

838837
driver = "cx_oracle"
839838

@@ -909,7 +908,6 @@ def __init__(
909908
cx_Oracle = self.dbapi
910909

911910
if cx_Oracle is None:
912-
self._include_setinputsizes = {}
913911
self.cx_oracle_ver = (0, 0, 0)
914912
else:
915913
self.cx_oracle_ver = self._parse_cx_oracle_ver(cx_Oracle.version)
@@ -925,7 +923,7 @@ def __init__(
925923
)
926924
self._cursor_var_unicode_kwargs = util.immutabledict()
927925

928-
self._include_setinputsizes = {
926+
self.include_set_input_sizes = {
929927
cx_Oracle.DATETIME,
930928
cx_Oracle.NCLOB,
931929
cx_Oracle.CLOB,
@@ -935,9 +933,9 @@ def __init__(
935933
cx_Oracle.BLOB,
936934
cx_Oracle.FIXED_CHAR,
937935
cx_Oracle.TIMESTAMP,
938-
_OracleInteger,
939-
_OracleBINARY_FLOAT,
940-
_OracleBINARY_DOUBLE,
936+
int, # _OracleInteger,
937+
# _OracleBINARY_FLOAT, _OracleBINARY_DOUBLE,
938+
cx_Oracle.NATIVE_FLOAT,
941939
}
942940

943941
self._paramval = lambda value: value.getvalue()

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

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -330,9 +330,6 @@ def _against_native_enum(self):
330330
and self.item_type.native_enum
331331
)
332332

333-
def bind_expression(self, bindvalue):
334-
return bindvalue
335-
336333
def bind_processor(self, dialect):
337334
item_proc = self.item_type.dialect_impl(dialect).bind_processor(
338335
dialect

0 commit comments

Comments
 (0)