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 databasedb
. Psycopg2, the default PostgreSQL driver, is used.mysql+pymysql://localhost/db
connects to thedb
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 pathdb.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.
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 basicEngine
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. WhenFalse
, try to prevent this by raising anDuplicateScheme
error.
-
class
sqlian.
DuplicateScheme
(scheme, klass)¶ Raised when
register()
-ing a database under an existing scheme.