logilab.database package

Submodules

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)

has_fti_table(cursor)[source]
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(cursor)[source]
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

sql_drop_fti()[source]

drop tables used by the full text index

sql_grant_user_on_fti(user)[source]
sql_init_fti()[source]

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.

logilab.database.fti.normalize(word)[source]

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
logilab.database.fti.normalize_words(rawwords)[source]
logilab.database.fti.tokenize()

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

logilab.database.fti.tokenize_query()

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_phrase(phrase)[source]

add a single phrase query

add_word(word)[source]

add a single word query

execute(cursor)[source]

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

logilab.database.ftiquery.tokenize()

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.

logilab.database.postgres.tokenize_query()

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.

as_dict()[source]
delete(cursor)[source]
select(cursor)[source]
update(cursor)[source]
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]
Parameters:
  • 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]
Parameters:
  • 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]
Parameters:
  • 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
'2013/01/01'
select(table, params=None, selection=None)[source]
Parameters:
  • 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'
set(keys)[source]
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]
Parameters:
  • 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]
Parameters:
  • 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.

Parameters:
  • 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.init_sqlite_connexion(cnx)[source]
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

as_sql_postgres(args)[source]
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

UNKNOWN = None
binary_to_str(value)[source]

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

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

Bases: object

add_support(backend)[source]
aggregat = False
as_sql(backend, args)[source]
classmethod check_nbargs(nbargs)[source]
maxargs = 1
minargs = 1
name_mapping = {}
rtype = None
supported_backends = <object object>
supports(backend)[source]
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)

cursor()[source]

Wraps cursor()

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

Bases: object

A simple cursor wrapper in python (useful for profiling)

close()[source]

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)

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

Bases: logilab.database.ExtractDateField

field = 'YEAR'
logilab.database.convert_date(value)[source]
logilab.database.convert_datetime(value)[source]
logilab.database.convert_tzdatetime(value)[source]
logilab.database.convert_tztime(value)[source]
logilab.database.func_class

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

logilab.database.get_db_helper(driver)[source]

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

logilab.database.register_function(funcdef)[source]

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