Database

Database class

class Database

The Database object manages database connections using a connection pool. It is thread safe and can be shared between all threads in your application. The Database object allows working with the database directly using SQL, but most of the time you will work with entities and let Pony generate SQL statements for making the corresponding changes in the database. You can work with several databases at the same time, having a separate Database object for each database, but each entity always belongs to one database.

  • bind(provider, \args, **kwargs*)

  • bind(\args, **kwargs*)

    Bind entities to a database.

    • Parameters

      • provider (str) – the name of the database provider. The database provider is a module which resides in the pony.orm.dbproviders package. It knows how to work with a particular database. After the database provider name you should specify parameters which will be passed to the connect() method of the corresponding DBAPI driver. Pony comes with the following providers: “sqlite”, “postgres”, “mysql”, “oracle”, “cockroachdb”. This parameter can be used as a keyword argument as well.

      • args – parameters required by the database driver.

      • kwargs – parameters required by the database driver.

    During the bind() call, Pony tries to establish a test connection to the database. If the specified parameters are not correct or the database is not available, an exception will be raised. After the connection to the database was established, Pony retrieves the version of the database and returns the connection to the connection pool.

    The method can be called only once for a database object. All consequent calls of this method on the same database will raise the TypeError('Database object was already bound to ... provider') exception.

    1. db.bind('sqlite', ':memory:')
    2. db.bind('sqlite', 'filename', create_db=True)
    3. db.bind('postgres', user='', password='', host='', database='')
    4. db.bind('mysql', host='', user='', passwd='', db='')
    5. db.bind('oracle', 'user/password@dsn')
    6. db.bind('cockroach', user='', password='', host='',
    7.         database='', sslmode='disable')

    Also you can use keyword arguments for passing the parameters:

    1. db.bind(provider='sqlite', filename=':memory:')
    2. db.bind(provider='sqlite', filename='db.sqlite', create_db=True)
    3. db.bind(provider='postgres', user='', password='', host='', database='')
    4. db.bind(provider='mysql', host='', user='', passwd='', db='')
    5. db.bind(provider='oracle', user='', password='', dsn='')
    6. db.bind(provider='cockroach', user='', password='', host='',
    7.         database='', sslmode='disable')

    This allows keeping these parameters in a dict:

    1. db_params = dict(provider='postgres', host='...', port=...,
    2.                  user='...', password='...')
    3. db.bind(**db_params)

  • commit()

    Save all changes made within the current db_session() using the flush() method and commits the transaction to the database.

    You can call commit() more than once within the same db_session(). In this case the db_session() cache keeps the cached objects after commits. The cache will be cleaned up when the db_session() is over or if the transaction will be rolled back.

  • create_tables()

    Check the existing mapping and create tables for entities if they don’t exist. Also, Pony checks if foreign keys and indexes exist and create them if they are missing.

    This method can be useful if you need to create tables after they were deleted using the drop_all_tables() method. If you don’t delete tables, you probably don’t need this method, because Pony checks and creates tables during generate_mapping() call.

  • disconnect()

    Closes the database connection for the current thread if it was opened.

  • drop_all_tables(with_all_data=False)

    Drop all tables which are related to the current mapping.

    • Parameters

      with_all_data (bool) – False means Pony drops tables only if none of them contain any data. In case at least one of them is not empty, the method will raise the TableIsNotEmpty exception without dropping any table. In order to drop tables with data you should set with_all_data=True.

  • drop_table(table_name, if_exists=False, with_all_data=False)

    Drop the table_name table.

    If you need to delete a table which is mapped to an entity, you can use the class method drop_table() of an entity.

    • Parameters

      • table_name (str) – the name of the table to be deleted, case sensitive.

      • if_exists (bool) – when True, it will not raise the TableDoesNotExist exception if there is no such table in the database.

      • with_all_data (bool) – if the table is not empty the method will raise the TableIsNotEmpty exception.

  • on_connect(provider=None)

    Registers function that will be called each time new connection for given provider will establish. If provider not specified function will be called for every provider. The function should be registered before db.bind(…) call, also it should have 2 positional arguments:

    • Parameters

      • db (Database) – database object

      • connection (DBAPIConnection) – connection object

    1. db = Database()
    3. # entities declaration
    5. @db.on_connect(provider='sqlite')
    6. def sqlite_case_sensitivity(db, connection):
    7.     cursor = connection.cursor()
    8.     cursor.execute('PRAGMA case_sensitive_like = OFF')
    10. db.bind(**options)
    11. db.generate_mapping(create_tables=True)

    (New in version 0.7.6)

  • Entity

    This attribute represents the base class which should be inherited by all entities which are mapped to the particular database.

    Example:

    1. db = Database()
    3. class Person(db.Entity):
    4.     name = Required(str)
    5.     age = Required(int)

  • execute(sql, globals=None, locals=None)

    Execute SQL statement.

    Before executing the provided SQL, Pony flushes all changes made within the current db_session() using the flush() method.

    • Parameters

      • sql (str) – the SQL statement text.

      • globals (dict) –

      • locals (dict) – optional parameters which can contain dicts with variables and its values, used within the query.

      Returns

      a DBAPI cursor.

    Example:

    1. cursor = db.execute("""create table Person (
    2.              id integer primary key autoincrement,
    3.              name text,
    4.              age integer
    5.       )""")
    7. name, age = "Ben", 33
    8. cursor = db.execute("insert into Person (name, age) values ($name, $age)")

    See Raw SQL section for more info.

  • exists(sql, globals=None, locals=None)

    Check if the database has at least one row which satisfies the query.

    Before executing the provided SQL, Pony flushes all changes made within the current db_session() using the flush() method.

    • Parameters

      • sql (str) – the SQL statement text.

      • globals (dict) –

      • locals (dict) – optional parameters which can contain dicts with variables and its values, used within the query.

      Return type

      bool

    Example:

    1. name = 'John'
    2. if db.exists("select * from Person where name = $name"):
    3.     print "Person exists in the database"

  • flush()

    Save the changes accumulated in the db_session() cache to the database. You may never have a need to call this method manually, because it will be done on leaving the db_session() automatically.

    Pony always saves the changes accumulated in the cache automatically before executing the following methods: get(), exists(), execute(), commit(), select().

  • generate_mapping(check_tables=True, create_tables=False)

    Map declared entities to the corresponding tables in the database. Creates tables, foreign key references and indexes if necessary.

    • Parameters

      • check_tables (bool) – when True, Pony makes a simple check that the table names and attribute names in the database correspond to entities declaration. It doesn’t catch situations when the table has extra columns or when the type of particular column doesn’t match. Set it to False if you want to generate mapping and create tables for your entities later, using the method create_tables().

      • create_tables (bool) – create tables, foreign key references and indexes if they don’t exist. Pony generates the names of the database tables and columns automatically, but you can override this behavior if you want. See more details in the Mapping customization section.

  • get(sql, globals=None, locals=None)

    Select one row or just one value from the database.

    The get() method assumes that the query returns exactly one row. If the query returns nothing Pony raises RowNotFound exception. If the query returns more than one row, the exception MultipleRowsFound will be raised.

    Before executing the provided SQL, Pony flushes all changes made within the current db_session() using the flush() method.

    • Parameters

      • sql (str) – the SQL statement text.

      • globals (dict) –

      • locals (dict) – optional parameters which can contain dicts with variables and its values, used within the query.

      Returns

      a tuple or a value. If your request returns a lot of columns you can assign the resulting tuple of the get() method to a variable and work with it the same way as it is described in select() method.

    Example:

    1. id = 1
    2. age = db.get("select age from Person where id = $id")
    4. name, age = db.get("select name, age from Person where id = $id")

  • get_connection()

    Return the active database connection. It can be useful if you want to work with the DBAPI interface directly. This is the same connection which is used by the ORM itself. The connection will be reset and returned to the connection pool on leaving the db_session() context or when the database transaction rolls back. This connection can be used only within the db_session() scope where the connection was obtained.

    • Returns

      a DBAPI connection.

  • global_stats

    This attribute keeps the dictionary where the statistics for executed SQL queries is aggregated from all threads. The key of this dictionary is the SQL statement and the value is an object of the QueryStat class.

  • insert(table_name|entity, returning=None, \*kwargs*)

    Insert new rows into a table. This command bypasses the identity map cache and can be used in order to increase the performance when you need to create lots of objects and not going to read them in the same transaction. Also you can use the execute() method for this purpose. If you need to work with those objects in the same transaction it is better to create instances of entities and have Pony to save them in the database.

    • Parameters

      • table_name|entity (str) – the name of the table where the data will be inserted. The name is case-sensitive. Instead of the table_name you can use the entity class. In this case Pony will insert into the table associated with the entity.

      • returning (str) – the name of the column that holds the automatically generated primary key. If you want the insert() method to return the value which is generated by the database, you should specify the name of the primary key column.

      • kwargs (dict) – named parameters used within the query.

    Example:

    1. new_id = db.insert("Person", name="Ben", age=33, returning='id')

  • last_sql

    Read-only attribute which keeps the text of the last SQL statement. It can be useful for debugging.

  • local_stats

    This is a dictionary which keeps the SQL query statistics for the current thread. The key of this dictionary is the SQL statement and the value is an object of the QueryStat class.

  • merge_local_stats()

    Merge the statistics from the current thread into the global statistics. You can call this method at the end of the HTTP request processing.

    When you call this method, the value of local_stats will be merged to global_stats, and local_stats will be cleared.

    In a web application, you can call this method on finishing processing an HTTP request. This way the global_stats attribute will contain the statistics for the whole application.

  • rollback()

    Rolls back the current transaction and clears the db_session() cache.

  • select(sql, globals=None, locals=None)

    Execute the SQL statement in the database and returns a list of tuples.

    • Parameters

      • sql (str) – the SQL statement text.

      • globals (dict) –

      • locals (dict) – optional parameters which can contain dicts with variables and its values, used within the query.

      Returns

      a list of tuples.

    Example:

    1. result = select("select * from Person")

    If a query returns more than one column and the names of table columns are valid Python identifiers, then you can access them as attributes:

    1. for row in db.select("name, age from Person"):
    2.     print row.name, row.age