From c8c52a3436f1a10599a651b8b0f55ca26cc05773 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 6 Jul 2020 11:11:54 -0400 Subject: [PATCH] Consolidate connection customization documentation 1. move all discussion of how to customize connection to the engines.rst section. have the section in pooling.rst point to engines. 2. ensure all of the common use cases for connection modification are listed out in order of level of modification. the use case of modifying an existing connection is separate from that of modifying how connect is called; ensure that poolevents.connect is referred to just as prominently as dialectevents.do_connect. 3. completely replace any discussion of create_engine.creator, as this hook does not offer anything beyond what do_connect() does. "creator" is more about using a Pool object directly without an Engine, which is no longer a documented use case. Change-Id: Ibe366d2a6e63eb420a6136fdc71ce0fb545edf8f (cherry picked from commit b5c4a4cbe632e7dfa243121377a4a8d73b4da1ab) --- doc/build/core/engines.rst | 99 +++++++++++++++++++++++++++----------- doc/build/core/pooling.rst | 20 +------- 2 files changed, 73 insertions(+), 46 deletions(-) diff --git a/doc/build/core/engines.rst b/doc/build/core/engines.rst index 1b0a4c967c..19158a4c00 100644 --- a/doc/build/core/engines.rst +++ b/doc/build/core/engines.rst @@ -222,54 +222,97 @@ For more information on connection pooling, see :ref:`pooling_toplevel`. .. _custom_dbapi_args: -Custom DBAPI connect() arguments -================================ +Custom DBAPI connect() arguments / on-connect routines +======================================================= -Custom arguments used when issuing the ``connect()`` call to the underlying -DBAPI may be issued in three distinct ways. String-based arguments can be -passed directly from the URL string as query arguments: +For cases where special connection methods are needed, in the vast majority +of cases, it is most appropriate to use one of several hooks at the +:func:`_sa.create_engine` level in order to customize this process. These +are described in the following sub-sections. -.. sourcecode:: python+sql +Special Keyword Arguments Passed to dbapi.connect() +--------------------------------------------------- - db = create_engine('postgresql://scott:tiger@localhost/test?argument1=foo&argument2=bar') +For special arguments that must be passed to the DBAPI for which the +SQLAlchemy dialect does not parse from the query string correctly, +the :paramref:`_sa.create_engine.connect_args` dictionary can be used. +This is often when special sub-structures or objects must be passed to +the DBAPI, or sometimes it's just that a particular flag must be sent as +the ``True`` symbol and the SQLAlchemy dialect is not aware of this keyword +argument. Below illustrates the use of a psycopg2 "connection factory" +that replaces the underlying implementation the connection:: -If SQLAlchemy's database connector is aware of a particular query argument, it -may convert its type from string to its proper type. -:func:`~sqlalchemy.create_engine` also takes an argument ``connect_args`` which is an additional dictionary that will be passed to ``connect()``. This can be used when arguments of a type other than string are required, and SQLAlchemy's database connector has no type conversion logic present for that parameter: + engine = create_engine( + "postgresql://user:pass@hostname/dbname", + connect_args={"connection_factory": MyConnectionFactory} + ) -.. sourcecode:: python+sql - db = create_engine('postgresql://scott:tiger@localhost/test', connect_args = {'argument1':17, 'argument2':'bar'}) +Controlling how parameters are passed to the DBAPI connect() function +--------------------------------------------------------------------- -The two methods that are the most customizable include using the -:paramref:`_sa.create_engine.creator` parameter, which specifies a callable that returns a -DBAPI connection: +At the next level, we can customize how the DBAPI ``connect()`` function +itself is called using the :meth:`.DialectEvents.do_connect` event hook. +This hook is passed the full ``*args, **kwargs`` that the dialect would +send to ``connect()``. These collections can then be modified in place +to alter how they are used:: -.. sourcecode:: python+sql + from sqlalchemy import event - def connect(): - return psycopg.connect(user='scott', host='localhost') + engine = create_engine("postgresql://user:pass@hostname/dbname") - db = create_engine('postgresql://', creator=connect) + @event.listens_for(engine, "do_connect") + def receive_do_connect(dialect, conn_rec, cargs, cparams): + cparams['connection_factory'] = MyConnectionFactory -Alternatively, the :meth:`_events.DialectEvents.do_connect` hook may be -used on an existing engine which allows full replacement of the connection -approach, given connection arguments:: +Modifying the DBAPI connection after connect, or running commands after connect +------------------------------------------------------------------------------- +For a DBAPI connection that SQLAlchemy creates without issue, but where we +would like to modify the completed connection before it's actually used, such +as for setting special flags or running certain commands, the +:meth:`.PoolEvents.connect` event hook is the most appropriate hook. This +hook is called for every new connection created, before it is used by +SQLAlchemy:: from sqlalchemy import event - db = create_engine('postgresql://scott:tiger@localhost/test') + engine = create_engine( + "postgresql://user:pass@hostname/dbname" + ) - @event.listens_for(db, "do_connect") - def receive_do_connect(dialect, conn_rec, cargs, cparams): - # cargs and cparams can be modified in place... - cparams['password'] = 'new password' + @event.listens_for(engine, "connect") + def connect(dbapi_connection, connection_record): + cursor = dbapi_connection.cursor() + cursor.execute("SET some session variables") + cursor.close() + + +Fully Replacing the DBAPI ``connect()`` function +------------------------------------------------ - # alternatively, return the new DBAPI connection +Finally, the :meth:`.DialectEvents.do_connect` event hook can also allow us to take +over the connection process entirely by establishing the connection +and returning it:: + + from sqlalchemy import event + + engine = create_engine( + "postgresql://user:pass@hostname/dbname" + ) + + @event.listens_for(engine, "do_connect") + def receive_do_connect(dialect, conn_rec, cargs, cparams): + # return the new DBAPI connection with whatever we'd like to + # do return psycopg2.connect(*cargs, **cparams) +The :meth:`.DialectEvents.do_connect` hook supersedes the previous +:paramref:`_sa.create_engine.creator` hook, which remains available. +:meth:`.DialectEvents.do_connect` has the distinct advantage that the +complete arguments parsed from the URL are also passed to the user-defined +function which is not the case with :paramref:`_sa.create_engine.creator`. .. _dbengine_logging: diff --git a/doc/build/core/pooling.rst b/doc/build/core/pooling.rst index 2c33c567c4..441121fe20 100644 --- a/doc/build/core/pooling.rst +++ b/doc/build/core/pooling.rst @@ -80,26 +80,10 @@ Disabling pooling using :class:`.NullPool`:: Using a Custom Connection Function ---------------------------------- -All :class:`_pool.Pool` classes accept an argument ``creator`` which is -a callable that creates a new connection. :func:`_sa.create_engine` -accepts this function to pass onto the pool via an argument of -the same name:: +See the section :ref:`custom_dbapi_args` for a rundown of the various +connection customization routines. - import sqlalchemy.pool as pool - import psycopg2 - - def getconn(): - c = psycopg2.connect(username='ed', host='127.0.0.1', dbname='test') - # do things with 'c' to set up - return c - - engine = create_engine('postgresql+psycopg2://', creator=getconn) -For most "initialize on connection" routines, it's more convenient -to use the :class:`_events.PoolEvents` event hooks, so that the usual URL argument to -:func:`_sa.create_engine` is still usable. ``creator`` is there as -a last resort for when a DBAPI has some form of ``connect`` -that is not at all supported by SQLAlchemy. Constructing a Pool ------------------- -- 2.47.3