From 21022f9760e32cf54d59eaccc12cc9e2fea1d37a Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 10 Nov 2014 17:58:09 -0500 Subject: - in lieu of adding a new system of translating bound parameter names for psycopg2 and others, encourage users to take advantage of positional styles by documenting "paramstyle". A section is added to psycopg2 specifically as this is a pretty common spot for named parameters that may be unusually named. fixes #3246. --- lib/sqlalchemy/dialects/postgresql/psycopg2.py | 49 ++++++++++++++++++++++++++ 1 file changed, 49 insertions(+) (limited to 'lib/sqlalchemy/dialects/postgresql') diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py index 1a2a1ffe4..f67b2e3b0 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -159,6 +159,55 @@ defaults to ``utf-8``. SQLAlchemy's own unicode encode/decode functionality is steadily becoming obsolete as most DBAPIs now support unicode fully. +Bound Parameter Styles +---------------------- + +The default parameter style for the psycopg2 dialect is "pyformat", where +SQL is rendered using ``%(paramname)s`` style. This format has the limitation +that it does not accommodate the unusual case of parameter names that +actually contain percent or parenthesis symbols; as SQLAlchemy in many cases +generates bound parameter names based on the name of a column, the presence +of these characters in a column name can lead to problems. + +There are two solutions to the issue of a :class:`.schema.Column` that contains +one of these characters in its name. One is to specify the +:paramref:`.schema.Column.key` for columns that have such names:: + + measurement = Table('measurement', metadata, + Column('Size (meters)', Integer, key='size_meters') + ) + +Above, an INSERT statement such as ``measurement.insert()`` will use +``size_meters`` as the parameter name, and a SQL expression such as +``measurement.c.size_meters > 10`` will derive the bound parameter name +from the ``size_meters`` key as well. + +.. versionchanged:: 1.0.0 - SQL expressions will use :attr:`.Column.key` + as the source of naming when anonymous bound parameters are created + in SQL expressions; previously, this behavior only applied to + :meth:`.Table.insert` and :meth:`.Table.update` parameter names. + +The other solution is to use a positional format; psycopg2 allows use of the +"format" paramstyle, which can be passed to +:paramref:`.create_engine.paramstyle`:: + + engine = create_engine( + 'postgresql://scott:tiger@localhost:5432/test', paramstyle='format') + +With the above engine, instead of a statement like:: + + INSERT INTO measurement ("Size (meters)") VALUES (%(Size (meters))s) + {'Size (meters)': 1} + +we instead see:: + + INSERT INTO measurement ("Size (meters)") VALUES (%s) + (1, ) + +Where above, the dictionary style is converted into a tuple with positional +style. + + Transactions ------------ -- cgit v1.2.1 From 87bfcf91e9659893f17adf307090bc0a4a8a8f23 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 4 Dec 2014 12:01:19 -0500 Subject: - The :meth:`.PGDialect.has_table` method will now query against ``pg_catalog.pg_table_is_visible(c.oid)``, rather than testing for an exact schema match, when the schema name is None; this so that the method will also illustrate that temporary tables are present. Note that this is a behavioral change, as Postgresql allows a non-temporary table to silently overwrite an existing temporary table of the same name, so this changes the behavior of ``checkfirst`` in that unusual scenario. fixes #3264 --- lib/sqlalchemy/dialects/postgresql/base.py | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index baa640eaa..034ee9076 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1942,7 +1942,8 @@ class PGDialect(default.DefaultDialect): cursor = connection.execute( sql.text( "select relname from pg_class c join pg_namespace n on " - "n.oid=c.relnamespace where n.nspname=current_schema() " + "n.oid=c.relnamespace where " + "pg_catalog.pg_table_is_visible(c.oid) " "and relname=:name", bindparams=[ sql.bindparam('name', util.text_type(table_name), -- cgit v1.2.1 From 8038cfa0771ff860f48967a6800477ce8a508d65 Mon Sep 17 00:00:00 2001 From: Tony Locke Date: Sun, 24 Aug 2014 16:33:29 +0100 Subject: pg8000 client_encoding in create_engine() The pg8000 dialect now supports the setting of the PostgreSQL parameter client_encoding from create_engine(). --- lib/sqlalchemy/dialects/postgresql/pg8000.py | 61 ++++++++++++++++++++++++---- 1 file changed, 54 insertions(+), 7 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql') diff --git a/lib/sqlalchemy/dialects/postgresql/pg8000.py b/lib/sqlalchemy/dialects/postgresql/pg8000.py index 4ccc90208..a76787016 100644 --- a/lib/sqlalchemy/dialects/postgresql/pg8000.py +++ b/lib/sqlalchemy/dialects/postgresql/pg8000.py @@ -13,17 +13,30 @@ postgresql+pg8000://user:password@host:port/dbname[?key=value&key=value...] :url: https://pythonhosted.org/pg8000/ + +.. _pg8000_unicode: + Unicode ------- -When communicating with the server, pg8000 **always uses the server-side -character set**. SQLAlchemy has no ability to modify what character set -pg8000 chooses to use, and additionally SQLAlchemy does no unicode conversion -of any kind with the pg8000 backend. The origin of the client encoding setting -is ultimately the CLIENT_ENCODING setting in postgresql.conf. +pg8000 will encode / decode string values between it and the server using the +PostgreSQL ``client_encoding`` parameter; by default this is the value in +the ``postgresql.conf`` file, which often defaults to ``SQL_ASCII``. +Typically, this can be changed to ``utf-8``, as a more useful default:: + + #client_encoding = sql_ascii # actually, defaults to database + # encoding + client_encoding = utf8 + +The ``client_encoding`` can be overriden for a session by executing the SQL: -It is not necessary, though is also harmless, to pass the "encoding" parameter -to :func:`.create_engine` when using pg8000. +SET CLIENT_ENCODING TO 'utf8'; + +SQLAlchemy will execute this SQL on all new connections based on the value +passed to :func:`.create_engine` using the ``client_encoding`` parameter:: + + engine = create_engine( + "postgresql+pg8000://user:pass@host/dbname", client_encoding='utf8') .. _pg8000_isolation_level: @@ -133,6 +146,10 @@ class PGDialect_pg8000(PGDialect): } ) + def __init__(self, client_encoding=None, **kwargs): + PGDialect.__init__(self, **kwargs) + self.client_encoding = client_encoding + def initialize(self, connection): if self.dbapi and hasattr(self.dbapi, '__version__'): self._dbapi_version = tuple([ @@ -181,6 +198,16 @@ class PGDialect_pg8000(PGDialect): (level, self.name, ", ".join(self._isolation_lookup)) ) + def set_client_encoding(self, connection, client_encoding): + # adjust for ConnectionFairy possibly being present + if hasattr(connection, 'connection'): + connection = connection.connection + + cursor = connection.cursor() + cursor.execute("SET CLIENT_ENCODING TO '" + client_encoding + "'") + cursor.execute("COMMIT") + cursor.close() + def do_begin_twophase(self, connection, xid): connection.connection.tpc_begin((0, xid, '')) @@ -198,4 +225,24 @@ class PGDialect_pg8000(PGDialect): def do_recover_twophase(self, connection): return [row[1] for row in connection.connection.tpc_recover()] + def on_connect(self): + fns = [] + if self.client_encoding is not None: + def on_connect(conn): + self.set_client_encoding(conn, self.client_encoding) + fns.append(on_connect) + + if self.isolation_level is not None: + def on_connect(conn): + self.set_isolation_level(conn, self.isolation_level) + fns.append(on_connect) + + if len(fns) > 0: + def on_connect(conn): + for fn in fns: + fn(conn) + return on_connect + else: + return None + dialect = PGDialect_pg8000 -- cgit v1.2.1 From c93706fa3319663234e3ab886b65f055bf9ed5da Mon Sep 17 00:00:00 2001 From: Tony Locke Date: Sun, 24 Aug 2014 15:15:17 +0100 Subject: Make pg8000 version detection more robust pg8000 uses Versioneer, which means that development versions have version strings that don't fit into the dotted triple number format. Released versions will always fit the triple format though. --- lib/sqlalchemy/dialects/postgresql/pg8000.py | 17 +++++++++++------ 1 file changed, 11 insertions(+), 6 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql') diff --git a/lib/sqlalchemy/dialects/postgresql/pg8000.py b/lib/sqlalchemy/dialects/postgresql/pg8000.py index a76787016..17d83fa61 100644 --- a/lib/sqlalchemy/dialects/postgresql/pg8000.py +++ b/lib/sqlalchemy/dialects/postgresql/pg8000.py @@ -71,6 +71,7 @@ from ... import types as sqltypes from .base import ( PGDialect, PGCompiler, PGIdentifierPreparer, PGExecutionContext, _DECIMAL_TYPES, _FLOAT_TYPES, _INT_TYPES) +import re class _PGNumeric(sqltypes.Numeric): @@ -151,15 +152,19 @@ class PGDialect_pg8000(PGDialect): self.client_encoding = client_encoding def initialize(self, connection): - if self.dbapi and hasattr(self.dbapi, '__version__'): - self._dbapi_version = tuple([ - int(x) for x in - self.dbapi.__version__.split(".")]) - else: - self._dbapi_version = (99, 99, 99) self.supports_sane_multi_rowcount = self._dbapi_version >= (1, 9, 14) super(PGDialect_pg8000, self).initialize(connection) + @util.memoized_property + def _dbapi_version(self): + if self.dbapi and hasattr(self.dbapi, '__version__'): + return tuple( + [ + int(x) for x in re.findall( + r'(\d+)(?:[-\.]?|$)', self.dbapi.__version__)]) + else: + return (99, 99, 99) + @classmethod def dbapi(cls): return __import__('pg8000') -- cgit v1.2.1 From 17e03a0ea86cd92816b4002a203b2b0b2c1a538a Mon Sep 17 00:00:00 2001 From: Tony Locke Date: Sat, 3 Jan 2015 16:59:17 +0000 Subject: Changed pg8000 dialect to cope with native JSON For versions > 1.10.1 pg8000 returns de-serialized JSON objects rather than a string. SQL parameters are still strings though. --- lib/sqlalchemy/dialects/postgresql/pg8000.py | 13 ++++++++++++- 1 file changed, 12 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/postgresql') diff --git a/lib/sqlalchemy/dialects/postgresql/pg8000.py b/lib/sqlalchemy/dialects/postgresql/pg8000.py index 17d83fa61..4bb376a96 100644 --- a/lib/sqlalchemy/dialects/postgresql/pg8000.py +++ b/lib/sqlalchemy/dialects/postgresql/pg8000.py @@ -72,6 +72,7 @@ from .base import ( PGDialect, PGCompiler, PGIdentifierPreparer, PGExecutionContext, _DECIMAL_TYPES, _FLOAT_TYPES, _INT_TYPES) import re +from sqlalchemy.dialects.postgresql.json import JSON class _PGNumeric(sqltypes.Numeric): @@ -102,6 +103,15 @@ class _PGNumericNoBind(_PGNumeric): return None +class _PGJSON(JSON): + + def result_processor(self, dialect, coltype): + if dialect._dbapi_version > (1, 10, 1): + return None # Has native JSON + else: + return super(_PGJSON, self).result_processor(dialect, coltype) + + class PGExecutionContext_pg8000(PGExecutionContext): pass @@ -143,7 +153,8 @@ class PGDialect_pg8000(PGDialect): PGDialect.colspecs, { sqltypes.Numeric: _PGNumericNoBind, - sqltypes.Float: _PGNumeric + sqltypes.Float: _PGNumeric, + JSON: _PGJSON, } ) -- cgit v1.2.1