From a31da95e002e34d639daef27d972eb364a1ebd13 Mon Sep 17 00:00:00 2001 From: Julian Mehnle Date: Thu, 21 Mar 2019 17:54:22 -0400 Subject: [PATCH] Support DNS-less connections for psycopg2 Added support for parameter-less connection URLs for the psycopg2 dialect, meaning, the URL can be passed to :func:`.create_engine` as ``"postgresql+psycopg2://"`` with no additional arguments to indicate an empty DSN passed to libpq, which indicates to connect to "localhost" with no username, password, or database given. Pull request courtesy Julian Mehnle. Fixes: #4562 Closes: #4563 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/4563 Pull-request-sha: 8a05c96944a0523b06e9772cfe1832e500a43641 Change-Id: Ib6fca3c3c9eebeaf590d7f7fb0bc8cd4b6e4a55a --- doc/build/changelog/unreleased_13/4562.rst | 10 +++++ .../dialects/postgresql/psycopg2.py | 41 +++++++++++++++++-- test/dialect/postgresql/test_dialect.py | 15 +++++++ 3 files changed, 62 insertions(+), 4 deletions(-) create mode 100644 doc/build/changelog/unreleased_13/4562.rst diff --git a/doc/build/changelog/unreleased_13/4562.rst b/doc/build/changelog/unreleased_13/4562.rst new file mode 100644 index 0000000000..bbf534ced7 --- /dev/null +++ b/doc/build/changelog/unreleased_13/4562.rst @@ -0,0 +1,10 @@ +.. change:: + :tags: feature, postgresql + :tickets: 4562 + + Added support for parameter-less connection URLs for the psycopg2 dialect, + meaning, the URL can be passed to :func:`.create_engine` as + ``"postgresql+psycopg2://"`` with no additional arguments to indicate an + empty DSN passed to libpq, which indicates to connect to "localhost" with + no username, password, or database given. Pull request courtesy Julian + Mehnle. \ No newline at end of file diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py index 8a15a85593..199c1c7764 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py @@ -83,6 +83,33 @@ using ``host`` as an additional keyword argument:: `PQconnectdbParams \ `_ +Empty DSN Connections / Environment Variable Connections +--------------------------------------------------------- + +The psycopg2 DBAPI can connect to PostgreSQL by passing an empty DSN to the +libpq client library, which by default indicates to connect to a localhost +PostgreSQL database that is open for "trust" connections. This behavior can be +further tailored using a particular set of environment variables which are +prefixed with ``PG_...``, which are consumed by ``libpq`` to take the place of +any or all elements of the connection string. + +For this form, the URL can be passed without any elements other than the +initial scheme:: + + engine = create_engine('postgresql+psycopg2://') + +In the above form, a blank "dsn" string is passed to the ``psycopg2.connect()`` +function which in turn represents an empty DSN passed to libpq. + +.. versionadded:: 1.3.2 support for parameter-less connections with psycopg2. + +.. seealso:: + + `Environment Variables\ + `_ - + PostgreSQL documentation on how to use ``PG_...`` + environment variables for connections. + .. _psycopg2_execution_options: Per-Statement/Connection Execution Options @@ -735,10 +762,16 @@ class PGDialect_psycopg2(PGDialect): def create_connect_args(self, url): opts = url.translate_connect_args(username="user") - if "port" in opts: - opts["port"] = int(opts["port"]) - opts.update(url.query) - return ([], opts) + if opts: + if "port" in opts: + opts["port"] = int(opts["port"]) + opts.update(url.query) + # send individual dbname, user, password, host, port + # parameters to psycopg2.connect() + return ([], opts) + else: + # send a blank string for "dsn" to psycopg2.connect() + return ([''], opts) def is_disconnect(self, e, connection, cursor): if isinstance(e, self.dbapi.Error): diff --git a/test/dialect/postgresql/test_dialect.py b/test/dialect/postgresql/test_dialect.py index cadcbdc1c0..8c19e31bb3 100644 --- a/test/dialect/postgresql/test_dialect.py +++ b/test/dialect/postgresql/test_dialect.py @@ -27,6 +27,7 @@ from sqlalchemy import testing from sqlalchemy import text from sqlalchemy import TypeDecorator from sqlalchemy.dialects.postgresql import base as postgresql +from sqlalchemy.dialects.postgresql import psycopg2 as psycopg2_dialect from sqlalchemy.engine import engine_from_config from sqlalchemy.engine import url from sqlalchemy.testing import engines @@ -114,6 +115,20 @@ class DialectTest(fixtures.TestBase): e = engine_from_config(config, _initialize=False) eq_(e.dialect.use_native_unicode, True) + def test_psycopg2_empty_connection_string(self): + dialect = psycopg2_dialect.dialect() + u = url.make_url("postgresql://") + cargs, cparams = dialect.create_connect_args(u) + eq_(cargs, ['']) + eq_(cparams, {}) + + def test_psycopg2_nonempty_connection_string(self): + dialect = psycopg2_dialect.dialect() + u = url.make_url("postgresql://host") + cargs, cparams = dialect.create_connect_args(u) + eq_(cargs, []) + eq_(cparams, {"host": "host"}) + class BatchInsertsTest(fixtures.TablesTest): __only_on__ = "postgresql+psycopg2" -- 2.47.2