diff options
| -rw-r--r-- | doc/build/core/engines.rst | 99 | ||||
| -rw-r--r-- | 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 9a5e6eb1f..2404414d1 100644 --- a/doc/build/core/engines.rst +++ b/doc/build/core/engines.rst @@ -224,54 +224,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 d2b68220e..1f0010374 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 ------------------- |