logilab.database package

Submodules

logilab.database.fti module

class logilab.database.fti.FTIndexerMixIn

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)

cursor_reindex_object(uid, obj, cursor)

cursor_unindex_object(uid, cursor)

fti_need_distinct = True

fti_rank_order(tablename, querystr)

fti_restriction_sql(tablename, querystr, jointo=None, not_=False)

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'

fulltext_search(query_string, cursor=None)

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

has_fti_table(cursor)

index_object(uid, obj, cnx=None)

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

init_fti(cursor)

init_fti_extensions(cursor, owner=None)

if necessary, install extensions at database creation time

reindex_object(uid, obj, cnx=None)

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

sql_drop_fti()

drop tables used by the full text index

sql_grant_user_on_fti(user)

sql_init_fti()

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

unindex_object(uid, cnx=None)

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

exception logilab.database.fti.StopWord

Bases: Exception

Raised to indicate that a stop word has been encountered.

logilab.database.fti.normalize(word)

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)

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)

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)

goal(Q, _parent=None)

class logilab.database.ftiparser.IndexerQueryScanner(str, *args, **kw)

Bases: yapps.runtime.Scanner

patterns = [("'$'", re.compile('$')), ('\\s+', re.compile('\\s+')), ('WORD', re.compile('\\w+')), ('STRING', re.compile('\'([^\\\'\\\\]|\\\\.)*\'|\\"([^\\\\\\"\\\\]|\\\\.)*\\"'))]

logilab.database.ftiparser.parse(rule, text)

logilab.database.ftiquery module

Query objects for Generic Indexer.

class logilab.database.ftiquery.KeywordsQuery(words)

Bases: object

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

dict_query(cursor, uids=None)

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)

Bases: object

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

dict_query(cursor, uids=None)

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)

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)

add a single phrase query

add_word(word)

add a single word query

execute(cursor)

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)

Bases: object

Another helper class to ease SQL table manipulation.

as_dict()

delete(cursor)

select(cursor)

update(cursor)

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

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

Bases: object

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

adv_select(model, tables, params, joins=None)

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)

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)

Delete many using the IN clause

insert(table, params)

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)

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)

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)

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)

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)

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)

logilab.database.sqlite module

Sqlite RDBMS support

Supported driver: sqlite3

logilab.database.sqlite.init_sqlite_connexion(cnx)

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

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)

Bases: logilab.database.FunctionDescr

rtype = 'Float'

class logilab.database.AT_TZ(TZDatetime, timezone) → Return a datetime at a given time zone.

Bases: logilab.database.FunctionDescr

as_sql_postgres(args)

maxargs = 2

minargs = 2

supported_backends = ('postgres',)

class logilab.database.AVG(name=None)

Bases: logilab.database.AggrFunctionDescr

rtype = 'Float'

class logilab.database.AggrFunctionDescr(name=None)

Bases: logilab.database.FunctionDescr

aggregat = True

rtype = None

exception logilab.database.BadQuery

Bases: Exception

class logilab.database.CAST(name=None)

Bases: logilab.database.FunctionDescr

usage is CAST(datatype, expression)

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

as_sql(backend, args)

maxargs = 2

minargs = 2

rtype = <object object>

supported_backends = ('postgres', 'sqlite')

class logilab.database.COUNT(name=None)

Bases: logilab.database.AggrFunctionDescr

as_sql(backend, args)

maxargs = 2

rtype = 'Int'

class logilab.database.COUNTDISTINCT(name=None)

Bases: logilab.database.AggrFunctionDescr

as_sql(backend, args)

rtype = 'Int'

class logilab.database.DATE(name=None)

Bases: logilab.database.FunctionDescr

rtype = 'Date'

class logilab.database.DAY(name=None)

Bases: logilab.database.ExtractDateField

field = 'DAY'

class logilab.database.DBAPIAdapter(native_module, pywrap=False)

Bases: object

Base class for all DBAPI adapters

UNKNOWN = None

binary_to_str(value)

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

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

Wraps the native module connect method

process_cursor(cursor, encoding, binarywrap=None)

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)

returns_unicode = False

row_is_mutable = False

support_copy_from = False

class logilab.database.EPOCH(name=None)

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)

Bases: logilab.database.FunctionDescr

as_sql_postgres(args)

field = None

maxargs = 1

minargs = 1

rtype = 'Int'

class logilab.database.FunctionDescr(name=None)

Bases: object

add_support(backend)

aggregat = False

as_sql(backend, args)

classmethod check_nbargs(nbargs)

maxargs = 1

minargs = 1

name_mapping = {}

rtype = None

supported_backends = <object object>

supports(backend)

class logilab.database.HOUR(name=None)

Bases: logilab.database.ExtractDateField

field = 'HOUR'

class logilab.database.IN(name=None)

Bases: logilab.database.FunctionDescr

this is actually a ‘keyword’ function…

maxargs = None

class logilab.database.LENGTH(name=None)

Bases: logilab.database.FunctionDescr

rtype = 'Int'

class logilab.database.LOWER(name=None)

Bases: logilab.database.FunctionDescr

rtype = 'String'

class logilab.database.MAX(name=None)

Bases: logilab.database.AggrFunctionDescr

class logilab.database.MIN(name=None)

Bases: logilab.database.AggrFunctionDescr

class logilab.database.MINUTE(name=None)

Bases: logilab.database.ExtractDateField

field = 'MINUTE'

class logilab.database.MONTH(name=None)

Bases: logilab.database.ExtractDateField

field = 'MONTH'

exception logilab.database.NoAdapterFound(obj, objname=None, protocol='DBAPI')

Bases: Exception

Raised when no Adapter to DBAPI was found

class logilab.database.PyConnection(cnx)

Bases: logilab.database._SimpleConnectionWrapper

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

cursor()

Wraps cursor()

class logilab.database.PyCursor(cursor)

Bases: object

A simple cursor wrapper in python (useful for profiling)

close()

Wraps close()

execute(*args, **kwargs)

Wraps execute()

executemany(*args, **kwargs)

Wraps executemany()

fetchall(*args, **kwargs)

Wraps fetchall()

fetchmany(*args, **kwargs)

Wraps execute()

fetchone(*args, **kwargs)

Wraps fetchone()

class logilab.database.RANDOM(name=None)

Bases: logilab.database.FunctionDescr

maxargs = 0

minargs = 0

name_mapping = {'postgres': 'RANDOM'}

rtype = 'Float'

class logilab.database.SECOND(name=None)

Bases: logilab.database.ExtractDateField

field = 'SECOND'

class logilab.database.SUBSTRING(name=None)

Bases: logilab.database.FunctionDescr

maxargs = 3

minargs = 3

name_mapping = {'postgres': 'SUBSTR', 'sqlite': 'SUBSTR'}

rtype = 'String'

class logilab.database.SUM(name=None)

Bases: logilab.database.AggrFunctionDescr

class logilab.database.UPPER(name=None)

Bases: logilab.database.FunctionDescr

rtype = 'String'

exception logilab.database.UnknownDriver

Bases: Exception

raised when a unknown driver is given to get connection

exception logilab.database.UnknownFunction

Bases: logilab.database.BadQuery

exception logilab.database.UnsupportedFunction

Bases: logilab.database.BadQuery

class logilab.database.WEEKDAY(name=None)

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)

maxargs = 1

minargs = 1

rtype = 'Int'

class logilab.database.YEAR(name=None)

Bases: logilab.database.ExtractDateField

field = 'YEAR'

logilab.database.convert_date(value)

logilab.database.convert_datetime(value)

logilab.database.convert_tzdatetime(value)

logilab.database.convert_tztime(value)

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)

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)

returns an advanced function helper for the given driver

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

returns a fully dbapi compliant module

logilab.database.register_function(funcdef)

register the function funcdef on supported backends

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

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