Database Conenctions

SQLian provide an interface to connect to relational database backends through the database API. The main way to do this is by passing a database URL to the connect() function, but it is also possible to explicitly create Database instances for various backends.

The Main Interface

sqlian.connect(url)

Create a database connection.

The database URL takes the form:

scheme://username:password@host:port/database

Multiple parts of the form can be omitted if not present. Common examples:

  • postgresql:///db connects to the local PostgreSQL instance via Un*x sockets without authentication, to the database db. Psycopg2, the default PostgreSQL driver, is used.
  • mysql+pymysql://localhost/db connects to the db database in the MySQL instance at localhost, using no authentication. The PyMySQL driver is used.
  • sqlite:///db.sqlite3 connects to the SQLite3 file at relative path db.sqlite3. The third slash is to seperate path from the host. Use four slashes if you need to specify an absolute path. The default driver (built-in sqlite3) is used.
Parameters:url – URL of the database to connect to.
Returns:A Database instance with open connection.
class sqlian.UnrecognizableScheme(scheme)

Raised when connect() fails to recognize a scheme.

class sqlian.Database(**kwargs)

A database connection.

This class provides a wrapper to a DB-API 2.0 Connection instance, offering additional SQL-building methods alongside with the standard API.

Keyord arguments passed to the constructor are used to create the underlying Connection instance. The implementor is responsible for converting them for the underlying DB-API 2.0 interface.

Instances of this class implement the context manager interface. The instance itself is assigned to the as expression, and the connection is closed when the context manager exists, committing done automatically if there are no exceptions.

Parameters:
  • host – Network location of the database.
  • port – Network port to access the database.
  • database – Name of the database.
  • username – Username to connect to the database.
  • password – Password to connect to the database.
  • options – Database options as a string-string mapping.
close()

Close the connection.

This method exists to conform to DB-API 2.0.

commit()

Commit any pending transaction to the database.

This method exists to conform to DB-API 2.0.

connect(dbapi, **kwargs)

Connect to the database.

This is called by create_connection() to create the connection. Keyword arguments to this method are passed directly from the class constructor.

You should override this method to convert parameter names, call connect() on the passed DB-API 2.0 interface, and return the connection instance.

connection

The underlying connection object. This property is read-only.

create_connection(**kwargs)

Creates a connection.

If you’re implementing a wrapper not conforming to DB-API 2.0, you should implement this method to override the default behavior, which depends on the API.

Keyword arguments to this method are passed directly from the class constructor.

Returns:A DB-API 2.0 Connection object.
cursor()

Return a new Cursor Object using the connection.

This method exists to conform to DB-API 2.0.

delete(*args, **kwargs)

Build and execute a DELETE statement.

execute_statement(statement_builder, args, kwargs)

Build a statement, and execute it on the connection.

This method provides implementation of statement construction and execution. Call this method like this when you implement a statement builder in custom database subclasses:

def select(self, *args, **kwargs):
    return self.execute_statement(self.engine.select, args, kwargs)

You generally don’t need to call this method directly as a user, but use one of the wrapper functions like the above instead.

Return type:RecordCollection
insert(*args, **kwargs)

Build and execute an INSERT statement.

is_open()

Whether the connection is open.

Return type:bool
rollback()

Rollback pending transaction.

This method exists to conform to DB-API 2.0. Behavior of calling this method on a database not supporting transactions is undefined.

select(*args, **kwargs)

Build and execute a SELECT statement.

update(*args, **kwargs)

Build and execute an UPDATE statement.

Connecting to Unsupported Databases

To connect to a database not yet supported by SQLian, you need to implement your own Database subclass, and optionally register it to SQLian if you want to use it with connect().

Implement a Database

Most functionalities are available by subclassing Database, but you need to declare and do a few things to interface with the underlying DB-API 2.0 module:

  • Declare dbapi2_module_name on the class. This should be a dotted import path to the DB-API 2.0 module, i.e. the thing you put after the import keyword.
  • Declare engine_class on the class. This should be Engine or its subclass. Use the basic Engine if your database support the standard SQL syntax, and you don’t need vendor-specific commands (e.g. MySQL’s REPLACE). See documenttion on engines for detailed explaination on how to build custom engine classes.
  • Override Database.connect() to instruct the class how to call connect on the underlying DB-API module.

Register the Database (Optional)

Use register() to let connect() recognize your database’s URL scheme.

sqlian.register(scheme, klass, replaces_existing=False)

Register a database type to be usable by connect().

After registering a database class, it will be instantiatable via connect() by specifying the appropriate scheme.

Parameters:
  • scheme – The scheme to register this class under.
  • klass – The database class to register.
  • replaces_existing – If True, replaces the existing if there is already a database registered under this scheme. When False, try to prevent this by raising an DuplicateScheme error.
class sqlian.DuplicateScheme(scheme, klass)

Raised when register()-ing a database under an existing scheme.