lp.services.database.sqlbase : API documentation

Class	StupidCache	A Storm cache that never evicts objects except on clear().
Class	LaunchpadStyle	A SQLObject style for launchpad.
Class	SQLBase	Base class emulating SQLObject for legacy database classes.
Function	clear_current_connection_cache	Clear SQLObject's object cache. SQLObject compatibility - DEPRECATED.
Function	get_transaction_timestamp	Get the timestamp for the current transaction on `store`.
Function	quote	Quote a variable ready for inclusion into an SQL statement.
Function	quote_like	Quote a variable ready for inclusion in a SQL statement's LIKE clause
Function	sqlvalues	Return a tuple of converted sql values for each value in some_tuple.
Function	quote_identifier	Quote an identifier, such as a table name.
Function	convert_storm_clause_to_string	Convert a Storm expression into a plain string.
Function	flush_database_updates	Flushes all pending database updates.
Function	flush_database_caches	Flush all database caches.
Function	block_implicit_flushes	A decorator that blocks implicit flushes on the main store.
Function	reset_store	Function decorator that resets the main store.
Function	connect	Return a fresh DB-API connection to the MAIN MASTER database.
Function	connect_string	Return a PostgreSQL connection string.
Class	cursor	A DB-API cursor-like object for the Storm connection.
Function	session_store	Return a store connected to the session DB.
Function	_get_sqlobject_store	Return the store used by the SQLObject compatibility layer.

def _get_sqlobject_store():

Return the store used by the SQLObject compatibility layer.

def clear_current_connection_cache():

Clear SQLObject's object cache. SQLObject compatibility - DEPRECATED.

def get_transaction_timestamp(store):

Get the timestamp for the current transaction on store.

def quote(x):

Quote a variable ready for inclusion into an SQL statement. Note that you should use quote_like to create a LIKE comparison.

Basic SQL quoting works

>>> quote(1)
'1'
>>> quote(1.0)
'1.0'
>>> quote("hello")
"E'hello'"
>>> quote("'hello'")
"E'''hello'''"
>>> quote(r"\'hello")
"E'\\\\''hello'"

Note that we need to receive a Unicode string back, because our query will be a Unicode string (the entire query will be encoded before sending across the wire to the database).

>>> quote(u"\N{TRADE MARK SIGN}")
u"E'\u2122'"

Timezone handling is not implemented, since all timestamps should be UTC anyway.

>>> from datetime import datetime, date, time
>>> quote(datetime(2003, 12, 4, 13, 45, 50))
"'2003-12-04 13:45:50'"
>>> quote(date(2003, 12, 4))
"'2003-12-04'"
>>> quote(time(13, 45, 50))
"'13:45:50'"

This function special cases datetime objects, due to a bug that has since been fixed in SQLOS (it installed an SQLObject converter that stripped the time component from the value). By itself, the sqlrepr function has the following output:

>>> sqlrepr(datetime(2003, 12, 4, 13, 45, 50), 'postgres')
"'2003-12-04T13:45:50'"

This function also special cases set objects, which SQLObject's sqlrepr() doesn't know how to handle.

>>> quote(set([1,2,3]))
'(1, 2, 3)'

>>> quote(frozenset([1,2,3]))
'(1, 2, 3)'

def quote_like(x):

Quote a variable ready for inclusion in a SQL statement's LIKE clause

XXX: StuartBishop 2004-11-24: Including the single quotes was a stupid decision.

To correctly generate a SELECT using a LIKE comparision, we need to make use of the SQL string concatination operator '||' and the quote_like method to ensure that any characters with special meaning to the LIKE operator are correctly escaped.

>>> "SELECT * FROM mytable WHERE mycol LIKE '%%' || %s || '%%'" \
...     % quote_like('%')
"SELECT * FROM mytable WHERE mycol LIKE '%' || E'\\\\%' || '%'"

Note that we need 2 backslashes to quote, as per the docs on the LIKE operator. This is because, unless overridden, the LIKE operator uses the same escape character as the SQL parser.

>>> quote_like('100%')
"E'100\\\\%'"
>>> quote_like('foobar_alpha1')
"E'foobar\\\\_alpha1'"
>>> quote_like('hello')
"E'hello'"

Only strings are supported by this method.

>>> quote_like(1)
Traceback (most recent call last):
    [...]
TypeError: Not a string (<type 'int'>)

def sqlvalues(*values, **kwvalues):

Return a tuple of converted sql values for each value in some_tuple.

This safely quotes strings, or gives representations of dbschema items, for example.

Use it when constructing a string for use in a SELECT. Always use %s as the replacement marker.

('SELECT foo from Foo where bar = %s and baz = %s'

% sqlvalues(BugTaskSeverity.CRITICAL, 'foo'))

>>> sqlvalues()
Traceback (most recent call last):
...
TypeError: Use either positional or keyword values with sqlvalue.
>>> sqlvalues(1)
('1',)
>>> sqlvalues(1, "bad ' string")
('1', "E'bad '' string'")

You can also use it when using dict-style substitution.

>>> sqlvalues(foo=23)
{'foo': '23'}

However, you cannot mix the styles.

>>> sqlvalues(14, foo=23)
Traceback (most recent call last):
...
TypeError: Use either positional or keyword values with sqlvalue.

def quote_identifier(identifier):

Quote an identifier, such as a table name.

In SQL, identifiers are quoted using " rather than ' which is reserved for strings.

>>> print quoteIdentifier('hello')
"hello"
>>> print quoteIdentifier("'")
"'"
>>> print quoteIdentifier('"')
""""
>>> print quoteIdentifier("\\")
"\"
>>> print quoteIdentifier('\\"')
"\"""

def convert_storm_clause_to_string(storm_clause):

Convert a Storm expression into a plain string.

A helper function allowing to use a Storm expressions in old-style code which builds for example WHERE expressions as plain strings.

>>> from lp.bugs.model.bug import Bug
>>> from lp.bugs.model.bugtask import BugTask
>>> from lp.bugs.interfaces.bugtask import BugTaskImportance
>>> from storm.expr import And, Or

>>> print convert_storm_clause_to_string(BugTask)
BugTask

>>> print convert_storm_clause_to_string(BugTask.id == 16)
BugTask.id = 16

>>> print convert_storm_clause_to_string(
...     BugTask.importance == BugTaskImportance.UNKNOWN)
BugTask.importance = 999

>>> print convert_storm_clause_to_string(Bug.title == "foo'bar'")
Bug.title = E'foo''bar'''

>>> print convert_storm_clause_to_string(
...     Or(BugTask.importance == BugTaskImportance.UNKNOWN,
...        BugTask.importance == BugTaskImportance.HIGH))
BugTask.importance = 999 OR BugTask.importance = 40

>>> print convert_storm_clause_to_string(
...    And(Bug.title == 'foo', BugTask.bug == Bug.id,
...        Or(BugTask.importance == BugTaskImportance.UNKNOWN,
...           BugTask.importance == BugTaskImportance.HIGH)))
Bug.title = E'foo' AND BugTask.bug = Bug.id AND
(BugTask.importance = 999 OR BugTask.importance = 40)

Parameters storm_clause A Storm expression

def flush_database_updates():

Flushes all pending database updates.

When SQLObject's _lazyUpdate flag is set, then it's possible to have changes written to objects that aren't flushed to the database, leading to inconsistencies when doing e.g.:

# Assuming the Beer table already has a 'Victoria Bitter' row...
assert Beer.select("name LIKE 'Vic%'").count() == 1  # This will pass
beer = Beer.byName('Victoria Bitter')
beer.name = 'VB'
assert Beer.select("name LIKE 'Vic%'").count() == 0  # This will fail

To avoid this problem, use this function:

# Assuming the Beer table already has a 'Victoria Bitter' row...
assert Beer.select("name LIKE 'Vic%'").count() == 1  # This will pass
beer = Beer.byName('Victoria Bitter')
beer.name = 'VB'
flush_database_updates()
assert Beer.select("name LIKE 'Vic%'").count() == 0  # This will pass

def flush_database_caches():

Flush all database caches.

SQLObject caches field values from the database in SQLObject instances. If SQL statements are issued that change the state of the database behind SQLObject's back, these cached values will be invalid.

This function iterates through all the objects in the SQLObject connection's cache, and synchronises them with the database. This ensures that they all reflect the values in the database.

def block_implicit_flushes(func):

A decorator that blocks implicit flushes on the main store.

def reset_store(func):

Function decorator that resets the main store.

def connect(user=None, dbname=None, isolation=ISOLATION_LEVEL_DEFAULT):

Return a fresh DB-API connection to the MAIN MASTER database.

Can be used without first setting up the Component Architecture, unlike the usual stores.

Default database name is the one specified in the main configuration file.

l.s.d.sqlbase : module documentation