From 939de240d31a5441ad7380738d410a976d4ecc3a Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 22 Nov 2021 14:28:26 -0500 Subject: 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 --- lib/sqlalchemy/dialects/postgresql/pg8000.py | 95 +++++++++++++--------------- 1 file changed, 45 insertions(+), 50 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/pg8000.py') diff --git a/lib/sqlalchemy/dialects/postgresql/pg8000.py b/lib/sqlalchemy/dialects/postgresql/pg8000.py index 324007e7e..e849d0499 100644 --- a/lib/sqlalchemy/dialects/postgresql/pg8000.py +++ b/lib/sqlalchemy/dialects/postgresql/pg8000.py @@ -94,7 +94,6 @@ import re from uuid import UUID as _python_UUID from .array import ARRAY as PGARRAY -from .base import _ColonCast from .base import _DECIMAL_TYPES from .base import _FLOAT_TYPES from .base import _INT_TYPES @@ -115,7 +114,13 @@ from ... import util from ...sql.elements import quoted_name +class _PGString(sqltypes.String): + render_bind_cast = True + + class _PGNumeric(sqltypes.Numeric): + render_bind_cast = True + def result_processor(self, dialect, coltype): if self.asdecimal: if coltype in _FLOAT_TYPES: @@ -141,26 +146,29 @@ class _PGNumeric(sqltypes.Numeric): ) +class _PGFloat(_PGNumeric): + __visit_name__ = "float" + render_bind_cast = True + + class _PGNumericNoBind(_PGNumeric): def bind_processor(self, dialect): return None class _PGJSON(JSON): + render_bind_cast = True + def result_processor(self, dialect, coltype): return None - def get_dbapi_type(self, dbapi): - return dbapi.JSON - class _PGJSONB(JSONB): + render_bind_cast = True + def result_processor(self, dialect, coltype): return None - def get_dbapi_type(self, dbapi): - return dbapi.JSONB - class _PGJSONIndexType(sqltypes.JSON.JSONIndexType): def get_dbapi_type(self, dbapi): @@ -168,21 +176,26 @@ class _PGJSONIndexType(sqltypes.JSON.JSONIndexType): class _PGJSONIntIndexType(sqltypes.JSON.JSONIntIndexType): - def get_dbapi_type(self, dbapi): - return dbapi.INTEGER + __visit_name__ = "json_int_index" + + render_bind_cast = True class _PGJSONStrIndexType(sqltypes.JSON.JSONStrIndexType): - def get_dbapi_type(self, dbapi): - return dbapi.STRING + __visit_name__ = "json_str_index" + + render_bind_cast = True class _PGJSONPathType(JSONPathType): - def get_dbapi_type(self, dbapi): - return 1009 + pass + + # DBAPI type 1009 class _PGUUID(UUID): + render_bind_cast = True + def bind_processor(self, dialect): if not self.as_uuid: @@ -210,6 +223,8 @@ class _PGEnum(ENUM): class _PGInterval(INTERVAL): + render_bind_cast = True + def get_dbapi_type(self, dbapi): return dbapi.INTERVAL @@ -219,48 +234,39 @@ class _PGInterval(INTERVAL): class _PGTimeStamp(sqltypes.DateTime): - def get_dbapi_type(self, dbapi): - if self.timezone: - # TIMESTAMPTZOID - return 1184 - else: - # TIMESTAMPOID - return 1114 + render_bind_cast = True + + +class _PGDate(sqltypes.Date): + render_bind_cast = True class _PGTime(sqltypes.Time): - def get_dbapi_type(self, dbapi): - return dbapi.TIME + render_bind_cast = True class _PGInteger(sqltypes.Integer): - def get_dbapi_type(self, dbapi): - return dbapi.INTEGER + render_bind_cast = True class _PGSmallInteger(sqltypes.SmallInteger): - def get_dbapi_type(self, dbapi): - return dbapi.INTEGER + render_bind_cast = True class _PGNullType(sqltypes.NullType): - def get_dbapi_type(self, dbapi): - return dbapi.NULLTYPE + pass class _PGBigInteger(sqltypes.BigInteger): - def get_dbapi_type(self, dbapi): - return dbapi.BIGINTEGER + render_bind_cast = True class _PGBoolean(sqltypes.Boolean): - def get_dbapi_type(self, dbapi): - return dbapi.BOOLEAN + render_bind_cast = True class _PGARRAY(PGARRAY): - def bind_expression(self, bindvalue): - return _ColonCast(bindvalue, self) + render_bind_cast = True _server_side_id = util.counter() @@ -362,7 +368,7 @@ class PGDialect_pg8000(PGDialect): preparer = PGIdentifierPreparer_pg8000 supports_server_side_cursors = True - use_setinputsizes = True + render_bind_cast = True # reversed as of pg8000 1.16.6. 1.16.5 and lower # are no longer compatible @@ -372,8 +378,9 @@ class PGDialect_pg8000(PGDialect): colspecs = util.update_copy( PGDialect.colspecs, { + sqltypes.String: _PGString, sqltypes.Numeric: _PGNumericNoBind, - sqltypes.Float: _PGNumeric, + sqltypes.Float: _PGFloat, sqltypes.JSON: _PGJSON, sqltypes.Boolean: _PGBoolean, sqltypes.NullType: _PGNullType, @@ -386,6 +393,8 @@ class PGDialect_pg8000(PGDialect): sqltypes.Interval: _PGInterval, INTERVAL: _PGInterval, sqltypes.DateTime: _PGTimeStamp, + sqltypes.DateTime: _PGTimeStamp, + sqltypes.Date: _PGDate, sqltypes.Time: _PGTime, sqltypes.Integer: _PGInteger, sqltypes.SmallInteger: _PGSmallInteger, @@ -517,20 +526,6 @@ class PGDialect_pg8000(PGDialect): cursor.execute("COMMIT") cursor.close() - def do_set_input_sizes(self, cursor, list_of_tuples, context): - if self.positional: - cursor.setinputsizes( - *[dbtype for key, dbtype, sqltype in list_of_tuples] - ) - else: - cursor.setinputsizes( - **{ - key: dbtype - for key, dbtype, sqltype in list_of_tuples - if dbtype - } - ) - def do_begin_twophase(self, connection, xid): connection.connection.tpc_begin((0, xid, "")) -- cgit v1.2.1