logilab.database package


logilab.database.fti module

class logilab.database.fti.FTIndexerMixIn[source]

Bases: object

The base full-text indexer mixin. To be mixed with advanced functionnality helper.

Provide an inefficient but generic indexing method which can be overridden.

cursor_index_object(uid, obj, cursor)[source]
cursor_reindex_object(uid, obj, cursor)[source]
cursor_unindex_object(uid, cursor)[source]
fti_need_distinct = True
fti_rank_order(tablename, querystr)[source]
fti_restriction_sql(tablename, querystr, jointo=None, not_=False)[source]
fti_sql_schema = '\n%s\n\nCREATE TABLE word (\n word_id INTEGER PRIMARY KEY NOT NULL,\n word VARCHAR(100) NOT NULL UNIQUE\n);\n\nCREATE TABLE appears(\n uid INTEGER,\n word_id INTEGER REFERENCES word ON DELETE CASCADE,\n pos INTEGER NOT NULL\n);\n\nCREATE INDEX appears_uid ON appears (uid);\nCREATE INDEX appears_word_id ON appears (word_id);\n'
fti_table = 'appears'
fti_uid_attr = 'uid'

execute a full text query and return a list of 2-uple (rating, uid)

index_object(uid, obj, cnx=None)[source]

index an object with the given uid the object should inherit from or be compatible with Indexable object

init_fti_extensions(cursor, owner=None)[source]

if necessary, install extensions at database creation time

reindex_object(uid, obj, cnx=None)[source]

index an object with the given uid the object should inherit from or be compatible with Indexable object


drop tables used by the full text index


return the sql definition of table()s used by the full text index

unindex_object(uid, cnx=None)[source]

unindex an object the object should inherit from or be compatible with Indexable object

exception logilab.database.fti.StopWord[source]

Bases: Exception

Raised to indicate that a stop word has been encountered.


Return the normalized form for a word.

The word given in argument should be unicode !

currently normalized word are :
_ in lower case _ without any accent

This function may raise StopWord if the word shouldn’t be indexed

stop words are :
_ single letter

Return a list of all non-overlapping matches of pattern in string.


Return a list of all non-overlapping matches of pattern in string.

logilab.database.ftiparser module

Yapps input grammar for indexer queries.

class logilab.database.ftiparser.IndexerQuery(scanner)[source]

Bases: yapps.runtime.Parser

class Context(parent, scanner, rule, args=())

Bases: object

Class to represent the parser’s call stack.

Every rule creates a Context that links to its parent rule. The contexts can be used for debugging.

all(Q, _parent=None)[source]
goal(Q, _parent=None)[source]
class logilab.database.ftiparser.IndexerQueryScanner(str, *args, **kw)[source]

Bases: yapps.runtime.Scanner

patterns = [("'$'", re.compile('$')), ('\\s+', re.compile('\\s+')), ('WORD', re.compile('\\w+')), ('STRING', re.compile('\'([^\\\'\\\\]|\\\\.)*\'|\\"([^\\\\\\"\\\\]|\\\\.)*\\"'))]
logilab.database.ftiparser.parse(rule, text)[source]

logilab.database.ftiquery module

Query objects for Generic Indexer.

class logilab.database.ftiquery.KeywordsQuery(words)[source]

Bases: object

a keywords query’ll look for uid matching all those words in any order

dict_query(cursor, uids=None)[source]

execute this query using the given cursor the query maybe restricted to a given list of uids

return a dict with uid as keys and rating as value

class logilab.database.ftiquery.PhraseQuery(tokens)[source]

Bases: object

a phrase query’ll look for uid matching all phrase’s tokens in the same order

dict_query(cursor, uids=None)[source]

execute this query using the given cursor the query maybe restricted to a given list of uids

return a dict with uid as keys and rating as value

class logilab.database.ftiquery.Query(normalize)[source]

Bases: object

a query is the object manipulated by the indexer the query parser’ll call add_word and add_phrase on this object accoring to the query string (see query.g for the query string’s grammar)


add a single phrase query


add a single word query


execute this query using the given cursor yield a list of 2-uple (rating, uid)


Return a list of all non-overlapping matches of pattern in string.

logilab.database.mysql module

logilab.database.postgres module

Postgres RDBMS support

Supported drivers, in order of preference: - psycopg2 - psycopg2ct

Full-text search based on the tsearch2 extension from the openfts project (see http://openfts.sourceforge.net/)

Warning: you will need to run the tsearch2.sql script with super user privileges on the database.


Return a list of all non-overlapping matches of pattern in string.

logilab.database.sqlgen module

Help to generate SQL strings usable by the Python DB-API.

class logilab.database.sqlgen.BaseTable(table_name, table_fields, primary_key=None)[source]

Bases: object

Another helper class to ease SQL table manipulation.

class logilab.database.sqlgen.SQLExpression(sqlstring, **kwargs)[source]

Bases: object

Use this class when you need direct SQL expression in statements generated by SQLGenerator. Arguments:

  • a sqlstring that defines the SQL expression to be used, e.g. ‘YEARS(%(date)s)’
  • kwargs that define the values to be substituted in the SQL expression, e.g. date=’2013/01/01’

E.g. the SQL expression SQLExpression(‘YEARS(%(date)s)’, date=’2013/01/01’) will yield:

‘…, age = YEARS(%(date)s), …’ in a SQL statement

and will modify accordingly the parameters:

{‘date’: ‘2013/01/01’, …}

class logilab.database.sqlgen.SQLGenerator[source]

Bases: object

Helper class to generate SQL strings to use with python’s DB-API.

adv_select(model, tables, params, joins=None)[source]
  • model – list of columns to select
  • tables – list of tables used in from
  • params – dictionary that will be used as in cursor.execute(sql, params)
  • joins – optional list of restriction statements to insert in the where clause. Usually used to perform joins.
>>> s = SQLGenerator()
>>> s.adv_select(['column'],[('test', 't')], {})
'SELECT column FROM test AS t'
>>> s.adv_select(['column'],[('test', 't')], {'nom':'dupont'})
'SELECT column FROM test AS t WHERE nom = %(nom)s'
delete(table, params, addon=None)[source]
  • table – name of the table
  • params – dictionary that will be used as in cursor.execute(sql,params)
>>> s = SQLGenerator()
>>> s.delete('test',{'nom':'dupont'})
'DELETE FROM test WHERE nom = %(nom)s'
>>> s.delete('test',{'nom':'dupont','prenom':'jean'})
'DELETE FROM test WHERE nom = %(nom)s AND prenom = %(prenom)s'
delete_many(table, params)[source]

Delete many using the IN clause

insert(table, params)[source]
  • table – name of the table
  • params – dictionary that will be used as in cursor.execute(sql,params)
>>> s = SQLGenerator()
>>> s.insert('test',{'nom':'dupont'})
'INSERT INTO test ( nom ) VALUES ( %(nom)s )'
>>> params = {'nom':'dupont', 'prenom':'jean',
...          'age': SQLExpression('YEARS(%(date)s)', date='2013/01/01')}
>>> s.insert('test', params)
'INSERT INTO test ( age, nom, prenom ) VALUES ( YEARS(%(date)s), %(nom)s, %(prenom)s )'
>>> params['date'] # params has been modified
select(table, params=None, selection=None)[source]
  • table – name of the table
  • params – dictionary that will be used as in cursor.execute(sql,params)
>>> s = SQLGenerator()
>>> s.select('test',{})
'SELECT * FROM test'
>>> s.select('test',{'nom':'dupont'})
'SELECT * FROM test WHERE nom = %(nom)s'
>>> s.select('test',{'nom':'dupont','prenom':'jean'})
'SELECT * FROM test WHERE nom = %(nom)s AND prenom = %(prenom)s'
Parameters:keys – list of keys
>>> s = SQLGenerator()
>>> s.set(['nom'])
'nom = %(nom)s'
>>> s.set(['nom','prenom'])
'nom = %(nom)s, prenom = %(prenom)s'
update(table, params, unique)[source]
  • table – name of the table
  • params – dictionary that will be used as in cursor.execute(sql,params)
>>> s = SQLGenerator()
>>> s.update('test', {'id':'001','nom':'dupont'}, ['id'])
'UPDATE test SET nom = %(nom)s WHERE id = %(id)s'
>>> s.update('test',{'id':'001','nom':'dupont','prenom':'jean'},['id'])
'UPDATE test SET nom = %(nom)s, prenom = %(prenom)s WHERE id = %(id)s'
where(keys, addon=None)[source]
  • keys – list of keys
  • addon – additional sql statement
>>> s = SQLGenerator()
>>> s.where(['nom'])
'nom = %(nom)s'
>>> s.where(['nom','prenom'])
'nom = %(nom)s AND prenom = %(prenom)s'
>>> s.where(['nom','prenom'], 'x.id = y.id')
'x.id = y.id AND nom = %(nom)s AND prenom = %(prenom)s'
logilab.database.sqlgen.name_fields(cursor, records)[source]

Take a cursor and a list of records fetched with that cursor, then return a list of dictionaries (one for each record) whose keys are column names and values are records’ values.

  • cursor – cursor used to execute the query
  • records – list returned by fetch*()
logilab.database.sqlgen.sql_repr(type, val)[source]

logilab.database.sqlite module

Sqlite RDBMS support

Supported driver: sqlite3

logilab.database.sqlite.register_sqlite_pyfunc(pyfunc, nb_params=None, funcname=None)[source]

logilab.database.sqlserver module

logilab.database.sqlserver2000 module

logilab.database.sqlserver2005 module

logilab.database.sqlserver2008 module

Module contents

Wrappers to get actually replaceable DBAPI2 compliant modules and database connection whatever the database and client lib used.

Currently support:

  • postgresql (pgdb, psycopg, psycopg2, pyPgSQL)
  • sqlite (pysqlite2, sqlite, sqlite3)

just use the get_connection function from this module to get a wrapped connection. If multiple drivers for a database are available, you can control which one you want to use using the set_prefered_driver function.

Additional helpers are also provided for advanced functionalities such as listing existing users or databases, creating database… Get the helper for your database using the get_db_helper function.

class logilab.database.ABS(name=None)[source]

Bases: logilab.database.FunctionDescr

rtype = 'Float'
class logilab.database.AT_TZ(TZDatetime, timezone) → Return a datetime at a given time zone.[source]

Bases: logilab.database.FunctionDescr

maxargs = 2
minargs = 2
supported_backends = ('postgres',)
class logilab.database.AVG(name=None)[source]

Bases: logilab.database.AggrFunctionDescr

rtype = 'Float'
class logilab.database.AggrFunctionDescr(name=None)[source]

Bases: logilab.database.FunctionDescr

aggregat = True
rtype = None
exception logilab.database.BadQuery[source]

Bases: Exception

class logilab.database.CAST(name=None)[source]

Bases: logilab.database.FunctionDescr

usage is CAST(datatype, expression)

sql-92 standard says (CAST <expr> as <type>)

as_sql(backend, args)[source]
maxargs = 2
minargs = 2
rtype = <object object>
supported_backends = ('postgres', 'sqlite')
class logilab.database.COUNT(name=None)[source]

Bases: logilab.database.AggrFunctionDescr

as_sql(backend, args)[source]
maxargs = 2
rtype = 'Int'
class logilab.database.COUNTDISTINCT(name=None)[source]

Bases: logilab.database.AggrFunctionDescr

as_sql(backend, args)[source]
rtype = 'Int'
class logilab.database.DATE(name=None)[source]

Bases: logilab.database.FunctionDescr

rtype = 'Date'
class logilab.database.DAY(name=None)[source]

Bases: logilab.database.ExtractDateField

field = 'DAY'
class logilab.database.DBAPIAdapter(native_module, pywrap=False)[source]

Bases: object

Base class for all DBAPI adapters


turn raw value returned by the db-api module into a python string

connect(host='', database='', user='', password='', port='', schema=None, extra_args=None)[source]

Wraps the native module connect method

process_cursor(cursor, encoding, binarywrap=None)[source]

return an iterator on results.

Each record is returned a list (not a tuple) and each element of the record is processed : - database strings are all unicode - database booleans are python boolean objects - if binarywrap is provided, it is used to wrap binary data

process_value(value, description, encoding='utf-8', binarywrap=None)[source]
returns_unicode = False
row_is_mutable = False
support_copy_from = False
class logilab.database.EPOCH(name=None)[source]

Bases: logilab.database.ExtractDateField

Return EPOCH timestamp from a datetime/date ; return number of seconds for an interval.

field = 'EPOCH'
class logilab.database.ExtractDateField(name=None)[source]

Bases: logilab.database.FunctionDescr

field = None
maxargs = 1
minargs = 1
rtype = 'Int'
class logilab.database.FunctionDescr(name=None)[source]

Bases: object

aggregat = False
as_sql(backend, args)[source]
classmethod check_nbargs(nbargs)[source]
maxargs = 1
minargs = 1
name_mapping = {}
rtype = None
supported_backends = <object object>
class logilab.database.HOUR(name=None)[source]

Bases: logilab.database.ExtractDateField

field = 'HOUR'
class logilab.database.IN(name=None)[source]

Bases: logilab.database.FunctionDescr

this is actually a ‘keyword’ function…

maxargs = None
class logilab.database.LENGTH(name=None)[source]

Bases: logilab.database.FunctionDescr

rtype = 'Int'
class logilab.database.LOWER(name=None)[source]

Bases: logilab.database.FunctionDescr

rtype = 'String'
class logilab.database.MAX(name=None)[source]

Bases: logilab.database.AggrFunctionDescr

class logilab.database.MIN(name=None)[source]

Bases: logilab.database.AggrFunctionDescr

class logilab.database.MINUTE(name=None)[source]

Bases: logilab.database.ExtractDateField

field = 'MINUTE'
class logilab.database.MONTH(name=None)[source]

Bases: logilab.database.ExtractDateField

field = 'MONTH'
exception logilab.database.NoAdapterFound(obj, objname=None, protocol='DBAPI')[source]

Bases: Exception

Raised when no Adapter to DBAPI was found

class logilab.database.PyConnection(cnx)[source]

Bases: logilab.database._SimpleConnectionWrapper

A simple connection wrapper in python, generating wrapper for cursors as well (useful for profiling)


Wraps cursor()

class logilab.database.PyCursor(cursor)[source]

Bases: object

A simple cursor wrapper in python (useful for profiling)


Wraps close()

execute(*args, **kwargs)[source]

Wraps execute()

executemany(*args, **kwargs)[source]

Wraps executemany()

fetchall(*args, **kwargs)[source]

Wraps fetchall()

fetchmany(*args, **kwargs)[source]

Wraps execute()

fetchone(*args, **kwargs)[source]

Wraps fetchone()

class logilab.database.RANDOM(name=None)[source]

Bases: logilab.database.FunctionDescr

maxargs = 0
minargs = 0
name_mapping = {'postgres': 'RANDOM'}
rtype = 'Float'
class logilab.database.SECOND(name=None)[source]

Bases: logilab.database.ExtractDateField

field = 'SECOND'
class logilab.database.SUBSTRING(name=None)[source]

Bases: logilab.database.FunctionDescr

maxargs = 3
minargs = 3
name_mapping = {'postgres': 'SUBSTR', 'sqlite': 'SUBSTR'}
rtype = 'String'
class logilab.database.SUM(name=None)[source]

Bases: logilab.database.AggrFunctionDescr

class logilab.database.UPPER(name=None)[source]

Bases: logilab.database.FunctionDescr

rtype = 'String'
exception logilab.database.UnknownDriver[source]

Bases: Exception

raised when a unknown driver is given to get connection

exception logilab.database.UnknownFunction[source]

Bases: logilab.database.BadQuery

exception logilab.database.UnsupportedFunction[source]

Bases: logilab.database.BadQuery

class logilab.database.WEEKDAY(name=None)[source]

Bases: logilab.database.FunctionDescr

Return the day of the week represented by the date.

Sunday == 1, Saturday = 7

(pick those values since it’s recommended by in the ODBC standard)

maxargs = 1
minargs = 1
rtype = 'Int'
class logilab.database.YEAR(name=None)[source]

Bases: logilab.database.ExtractDateField

field = 'YEAR'

alias of logilab.database.IN

logilab.database.get_connection(driver='postgres', host='', database='', user='', password='', port='', quiet=False, drivers={'postgres': ['psycopg2', 'psycopg2ct', 'psycopg2cffi'], 'sqlite': ['sqlite3']}, pywrap=False, schema=None, extra_args=None)[source]

return a db connection according to given arguments

extra_args is an optional string that is appended to the DSN


returns an advanced function helper for the given driver

logilab.database.get_dbapi_compliant_module(driver, prefered_drivers=None, quiet=False, pywrap=False)[source]

returns a fully dbapi compliant module


register the function funcdef on supported backends

logilab.database.set_prefered_driver(driver, module, _drivers={'postgres': ['psycopg2', 'psycopg2ct', 'psycopg2cffi'], 'sqlite': ['sqlite3']})[source]

sets the preferred driver module for driver driver is the name of the db engine (postgresql,…) module is the name of the module providing the connect function syntax is (params_func, post_process_func_or_None) _drivers is a optional dictionary of drivers