Supported databases

SQLite

Using SQLite database is the easiest way to work with Pony because there is no need to install a database system separately - the SQLite database system is included in the Python distribution. It is a perfect choice for beginners who want to experiment with Pony in the interactive shell. In order to bind the Database object a SQLite database you can do the following:

db.bind(provider='sqlite', filename='db.sqlite', create_db=False)

db.bind(provider, filename, create_db=False, timeout=5.0)

• Parameters

  • provider (str) – Should be ‘sqlite’ for the SQLite database.

  • filename (str) – The name of the file where SQLite will store the data. The filename can be absolute or relative. If you specify a relative path, that path is appended to the directory path of the Python file where this database was created (and not to the current working directory). This is because sometimes a programmer doesn’t have the control over the current working directory (e.g. in mod_wsgi application). This approach allows the programmer to create applications which consist of independent modules, where each module can work with a separate database. When working in the interactive shell, Pony requires that you to always specify the absolute path of the storage file.

  • create_db (bool) – True means that Pony will try to create the database if such filename doesn’t exists. If such filename exists, Pony will use this file.

  • timeout (float) – The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default is 5.0 (five seconds). (New in version 0.7.3)

Normally SQLite database is stored in a file on disk, but it also can be stored entirely in memory. This is a convenient way to create a SQLite database when playing with Pony in the interactive shell, but you should remember, that the entire in-memory database will be lost on program exit. Also you should not work with the same in-memory SQLite database simultaneously from several threads because in this case all threads share the same connection due to SQLite limitation.

In order to bind with an in-memory database you should specify :memory: instead of the filename:

db.bind(provider='sqlite', filename=':memory:')

There is no need in the parameter create_db when creating an in-memory database.

Note

By default SQLite doesn’t check foreign key constraints. Pony always enables the foreign key support by sending the command PRAGMA foreign_keys = ON; starting with the release 0.4.9.

PostgreSQL

Pony uses psycopg2 driver in order to work with PostgreSQL. In order to bind the Database object to PostgreSQL use the following line:

db.bind(provider='postgres', user='', password='', host='', database='')

All the parameters that follow the Pony database provider name will be passed to the psycopg2.connect() method. Check the psycopg2.connect documentation in order to learn what other parameters you can pass to this method.

MySQL

db.bind(provider='mysql', host='', user='', passwd='', db='')

Pony tries to use the MySQLdb driver for working with MySQL. If this module cannot be imported, Pony tries to use pymysql. See the MySQLdb and pymysql documentation for more information about these drivers.

Oracle

db.bind(provider='oracle', user='', password='', dsn='')

Pony uses the cx_Oracle driver for connecting to Oracle databases. More information about the parameters which you can use for creating a connection to Oracle database can be found here.

CockroachDB

Pony uses psycopg2 driver in order to work with CockroachDB. In order to bind the Database object to CockroachDB use the following line:

db.bind(provider='cockroach', user='', password='', host='', database='',
        sslmode='disable')

All the parameters that follow the Pony database provider name will be passed to the CockroachDB driver.

If you want to use a secure connection to the CockroachDB, you have to specify additional parameters:

db.bind(provider='cockroach', user='', password='', host='', database='',
        port=26257, sslmode='require', sslrootcert='certs/ca.crt',
        sslkey='certs/client.maxroach.key', sslcert='certs/client.maxroach.crt')