Engines

class dbapix.engine.Engine

Database connection manager.

This is the base class, and is extended for each of the individual drivers we support. You don’t create engines directly, but rather through create_engine().

In general, you create the engine with whatever args/kwargs you would pass to the connect function of the underlying driver, e.g.:

engine = create_engine('sqlite', 'path/to/database.sqlite')

engine = create_engine('postgres',
    host='database.example.com',
    database='mydatabase',
    password='mypassword',
)

class dbapix.engine.SocketEngine(connect_kwargs=None, tunnel=None, **kwargs)

Database connection manager for socket-based database connections.

This is the base class for the socket-based database drivers, e.g. those for Postgres and MySQL. The primary extension is support of SSH tunnels via the tunnel kwarg, and implemented by the sshtunnel project.

Parameters

• connect_kwargs – The kwargs for the driver’s connect function.

• tunnel – May be an existing sshtunnel.SSHTunnelForwarder, or a dict of the kwargs to contruct one.

• kwargs – Alternative method to provide kwargs for the driver’s connect function.

We provide a few conveniences in the tunnel kwargs:

• ssh_address_or_host will be constructed out of what is availible of address, host, and port, e.g.:

  create_engine('postgres', tunnel=dict(host='sshhost.example.com'), ...)
  create_engine('postgres', tunnel=dict(address=('sshhost.example.com', 10022)), ...)
  

• remote_bind_address will be constructed out of remote_bind_port and the database’s default port, e.g.:

  create_engine('postgres', tunnel=dict(remote_bind_host='remote.example.com', ...), ...)
  

• The driver’s host and port will be automatically forced to 127.0.0.1 and the (random) port that the tunnel is listening on.

We do not specify where your private SSH key is, and paramiko does not automatically pick it up. You may have to do something like:

create_engine('postgres', tunnel=dict(ssh_pkey=os.path.expanduser('~/.ssh/id_rsa'), ...), ...)

Automatic Connection

Connections are managed via a connection pool in the engine. These methods are the recommended way to access those connections, as they are context managers that will automatically return the connection to the pool.

All kwargs are passed to Engine.get_connection().

Engine.connect(**kwargs)

Get a context-managed Connection.

with engine.connect() as con:
    # Use the connection, and then it will be auto-returned.
    cur = con.cursor()
    cur.execute('SELECT * FROM foo')

Engine.cursor(**kwargs)

Get a context-managed Cursor (if you don’t need the connection).

with engine.cursor() as cur:
    # Use the cursor, and then it will be auto-returned.
    cur.execute('SELECT * FROM foo')

Engine.execute(query, params=None)

Execute a context-managed query (if you don’t need the connection).

with engine.execute('SELECT 1') as cur:
    # Use the result, and then it will be auto-returned.
    row = next(cur)

See also

Cursor.execute() for parameters.

Manual Connections

You can also manually checkout and return connections from the pool:

con = engine.get_connection()
try:
    # Use the connection.
    cur = con.cursor()
    cur.execute('SELECT 1')
finally:
    # Return it.
    engine.put_connection(con)

Warning

You must either return the connection via Engine.put_connection() or close the connection via Connection.close() to avoid resource leaks (and deadlocks in some cases).

Engine.get_connection(timeout=None, **kwargs)

Get an idle connection from the pool, or create a new one if nessesary.

The connection should be returned via Engine.put_connection() or closed via Connection.close().

Parameters

• timeout (float) – Timeout for new connections. Default of None implies no timeout.

• **kwargs – Passed to Connection.reset_session().

Engine.put_connection(con, close=False, warn_status=True)

Return a connection to the pool.

Parameters

• close (bool) – Should the connection be closed? If false, the connection may still be closed due to the pool being too large, or reasons defined by the connection’s state.

• warn_status (bool) – Should a warning log be emitted if the connection is in a non-idle status.

Helpers

classmethod Engine.quote_identifier(name)

Escape a name for valid use as an identifier.

E.g. for SQLite:

>>> engine.quote_identifier('hello world')
'"hello world"'

classmethod Engine.adapt_type(name)

Convert a generic type name into this engine’s version.

E.g. for SQLite:

>>> engine.adapt_type('SERIAL PRIMARY KEY')
'INTEGER PRIMARY KEY'

This is case insensitive, and passes through unknown type untouched:

>>> engine.adapt_type('unknown')
'unknown'