netsa.sql — SQL Database Access¶
Overview¶
The normal flow of code that works with databases using the netsa.sql API looks like this:
from netsa.sql import *
select_stuff = db_query("""
select a, b, c
from test_table
where a + b <= :threshold
limit 10
""")
conn = db_connect("nsql-sqlite:/var/tmp/test_db.sqlite")
for (a, b, c) in conn.execute(select_stuff, threshold=5):
print ("a: %d, b: %d, c: %d, a + b: %d" % (a, b, c, a+b))
# Alternatively:
for (a, b, c) in select_stuff(conn, threshold=5):
print ("a: %d, b: %d, c: %d, a + b: %d" % (a, b, c, a+b))
First, the required queries are created as instances of the db_query class. Some developers prefer to have a separate module containing all of the queries grouped together. Others prefer to keep the queries close to where they are used.
When the database is to be used, a connection is opened using db_connect. The query is executed using db_connection.execute, or by calling the query directly. The result of that call is then iterated over and the data processed.
Connections and result sets are automatically closed when garbage collected. If you need to make sure that they are collected as early as possible, make sure the values are not kept around in the environment (for example, by assigning None to the variable containing them when your work is complete, if the variable won’t be leaving scope for a while.)
Exceptions¶
- exception netsa.sql.sql_exception(message : str)¶
Specific exceptions generated by netsa.sql derive from this.
- exception netsa.sql.sql_no_driver_exception(message : str)¶
This exception is raised when no driver is installed that can handle a URL opened via db_connect.
- exception netsa.sql.sql_invalid_uri_exception(message : str)¶
This exception is raised when the URI passed to db_connect cannot be parsed.
Connecting¶
- netsa.sql.db_connect(uri[, user : str, password : str]) → db_connection¶
Given a database URI and an optional user and password, attempts to connect to the specified database and return a db_connection subclass instance.
If a user and password are given in this call as well as in the URI, the values given in this call override the values given in the URI.
Database URIs have the form:
<scheme>://<user>:<password>@hostname:port/<path>;<param>=<value>;...?<query>#<fragment>
Various pieces can be left out in various ways. Typically, the following form is used for databases with network addresses:
<scheme>://[user[:password]@]hostname[:port]/<dbname>[;<parameters>]
While the following form is used for databases without network addresses, or sometimes for connections to databases on the local host:
<scheme>:<dbname>[;user=<user>][;password=<password>][;<params>]
The user and password may always be given either in the network location or in the params. Values given in the db_connect call override either of those, and values given in the network location take priority over those given in the params.
Refer to a specific database driver for details on what URI scheme to use, and what other params or URI pieces may be meaningful.
Connections and Result Sets¶
- class netsa.sql.db_connection(driver : db_driver, variants : str list)¶
An open database connection, returned by db_connect.
- clone() → db_connection¶
Returns a fresh open db_connection open to the same database with the same options as this connection.
- execute(query_or_sql : db_query or str[, <param_name>=<param_value>, ...]) → db_result¶
Executes the given SQL query (either a SQL string or a query compiled with db_query) with the provided variable bindings for side effects. Returns a db_result result set if the query returns a result set, an int with the number of rows affected if available, or None otherwise.
- commit()¶
Commits the current database transaction in progress. Note that if a db_connection closes without commit being called, the transaction will automatically be rolled back.
- rollback()¶
Rolls back the current database transaction in progress. Note that if a db_connection closes without commit being called, the transaction will automatically be rolled back.
- get_variants() → str seq¶
Returns which variant tags are associated with this connection.
- class netsa.sql.db_result(connection : db_connection, query : db_query, params : dict)¶
A database result set, which may be iterated over.
- get_connection() → db_connection¶
Returns the db_connection which produced this result set.
- get_query() → db_query¶
Returns the db_query which was executed to produce this result set. (Note that if a string query is given to db_connection.execute, it will automatically be wrapped in a db_query, so this is always a db_query.)
- __iter__() → iter¶
Returns an iterator over the rows of this result set. Each row returned is a tuple with one item for each column. If there is only one column in the result set, a tuple of one column is returned. (e.g. (5,), not just 5 if there is a single column with the value five in it.)
It is an error to attempt to iterate over a result set more than once, or multiple times at once.
Compiled Queries¶
- class netsa.sql.db_query(sql : str[, <variant> : str, ...])¶
A db_query represents a “compiled” database query, which will be used one or more times to make requests.
Whenever a query is executed using the db_connection.execute method, it may be provided as either a string or as a db_query object. If an object is used, it can represent a larger variety of possible behaviors. For example, it might give both a “default” SQL to run for the query, but also several specific versions meant to work with or around features of specific RDBMS products. For example:
test_query = db_query( """ select * from blah """, postgres=""" select * from pg_blah """, oracle=""" select rownum, * from ora_blah """)
A db_query object is a callable object. If called on a connection, it will execute itself on that connection. Specifically:
test_query(conn, ...)
has the same effect as:
conn.execute(test_query, ...)
- __call__(self, _conn : db_connection[, <param_name>=<param_value>, ...]) → db_result¶
Execute this db_query on the given db_connection with parameters.
Note that the following methods are primarily of interest to driver implementors.
- get_variant_sql(accepted_variants : str seq) → str¶
Given a list of accepted variant tags, returns the most appropriate SQL for this query. Specifically, this returns the first variant SQL given in the query which is acceptable, or the default SQL if none is acceptable.
- get_variant_qmark_params(accepted_variants : str seq, params : dict) → str, seq¶
Like get_variant_format_parms, but for the DB API 2.0 ‘format’ paramstyle (i.e. %s placeholders). This also escapes any percent signs originally present in the query.
- get_variant_numeric_params(accepted_variants : str seq, params : dict) → str, seq¶
Like get_variant_format_params, but for the DB API 2.0 ‘numeric’ paramstyle (i.e. :<n> placeholders).
- get_variant_named_params(accepted_variants : str seq, params : dict) → str, dict¶
Like get_variant_format_params, but for the DB API 2.0 ‘named’ paramstyle (i.e. :<name> placeholders). Note that this paramstyle is the native style required by the netsa.sql API.
- get_variant_format_params(accepted_variants : str seq, params : dict) → str, seq¶
Converts the SQL and params of this query to a form appropriate for databases that use the DB API 2.0 ‘format’ paramstyle (i.e. %s placeholders). Given a list of accepted variants and a dict of params, this returns the appropriate SQL with param placeholders converted to ‘format’ style, and a list of params suitable for filling those placeholders.
- get_variant_pyformat_params(accepted_variants : str seq, params : dict) → str, dict¶
Like get_variant_format_params, but for the DB API 2.0 ‘pyformat’ paramstyle (i.e. %(<name>)s placeholders). This also escapes any percent signs originally present in the query.
Implementing a New Driver¶
In order to implement a new database driver, you should create a new module that implements a subclass of db_driver, then calls register_driver with an instance of that subclass in order to register the new driver.
Your db_driver subclass will, of course, return subclasses of db_connection and db_result specific to your database as well. It should never be necessary to subclass db_query—that class is meant to be a database-neutral representation of a “compiled” query.
For most drivers, one of the get_variant_... methods of db_query should provide the query in a form that the underlying database can easily digest.
- class netsa.sql.db_driver¶
A database driver, which holds the responsibility of deciding which database URLs it will attempt to open, and returning db_connection objects when a connection is successfully opened.
- can_handle(uri_scheme : str) → bool¶
Returns True if this db_driver believes it can handle this database URI scheme.
- connect(uri : str, user: str or None, password : str or None) → db_connection¶
Returns None if this db_driver cannot handle this database URI, or a db_connection subclass instance connected to the database if it can. The user and password parameters passed in via this call override any values from the URI.
Experimental Connection Pooling¶
This version of netsa.sql contains experimental support for connection pooling. Connections in a pool will be created before they’re needed and kept available for re-use. Note that since this API is still in the early stages of development, it is very likely to change between versions of netsa-python.
- netsa.sql.db_create_pool(uri[, user : str, password : str], ...) → db_pool¶
Given a database URI, an optional user and password, and additional parameters, creates a driver-specific connection pool. Returns a db_pool from which connections can be obtained.
If a user and password (or other parameter) is given in this call as well as in the URI, the values given in this call override the values given in the URI.
See db_connect for details on database URIs.
- class netsa.sql.db_pool¶
A pool of database connections for a single specific connection specification and pool configuration. See db_create_pool.
- connect() → db_connection¶
Returns a db_connection subclass instance from the pool, open on the database specified when the pool was created.
- class netsa.sql.db_driver
- create_pool(uri, user : str or None, password : str or None, ...) → db_pool¶
Returns None if this db_driver does not support pooled connections or cannot handle this database URI, or a db_pool subclass instance which can be used to obtain connections from a pool. The user and password parameters and any other parameters passed in via this call override any values from the URI.
Why Not DB API 2.0?¶
If you have experience with Python database APIs, you may be wondering why we have chosen to implement a new API rather than simply using the standard DB API 2.0.
In short, the problem is that the standard database API isn’t really an API, but more a set of guidelines. For example, each database driver may use a different mechanism for providing query parameters. As another example, each API may also have different behaviors in the presence of threads.
Specifically, the sqlite module uses the ‘pyformat’ param style, which allows named parameters to queries which are passed as a dict, using Python-style formats. The sqlite3 module, on the other hand, uses the ‘qmark’ param style, where ? is used as a place-holder in queries, and the parameters are positional and passed in as a sequence.
We’ve done work to make sure that it’s simple to implement netsa.sql-style drivers over the top of DB API 2.0-style drivers. In fact, all of the currently deployed drivers are of this variety. The only work that has to be done for such a driver is to start with one of the existing drivers, determine which paramstyle is being used, do any protection against threading issues that might be necessary, and turn the connection URI into a form that the driver you’re using can handle.
Once that’s done, you still have the issue that different databases may require different SQL to operate—but that’s a lot easier to handle than “some databases use named parameters and some use positional”. And, the variant system makes it easy to put different compatibility versions of the same query together.
