Package logilab :: Package common :: Module sqlgen :: Class SQLGenerator

Class SQLGenerator


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

Instance Methods

adv_select(self, model, tables, params, joins=None)
:param model: list of columns to select :param tables: list of tables used in from :param params: dictionary that will be used as in cursor.execute(sql, params) :param joins: optional list of restriction statements to insert in the where clause.

source code

delete(self, table, params)
:param table: name of the table :param params: dictionary that will be used as in cursor.execute(sql,params)

source code

insert(self, table, params)
:param table: name of the table :param params: dictionary that will be used as in cursor.execute(sql,params)

source code

select(self, table, params)
:param table: name of the table :param params: dictionary that will be used as in cursor.execute(sql,params)

source code

set(self, keys)
:param keys: list of keys

source code

update(self, table, params, unique)
:param table: name of the table :param params: dictionary that will be used as in cursor.execute(sql,params)

source code

where(self, keys, addon=None)
:param keys: list of keys

source code

Method Details

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

source code


:param model:  list of columns to select
:param tables: list of tables used in from
:param params: dictionary that will be used as in cursor.execute(sql, params)
:param 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(self, table, params)

source code


:param table: name of the table
:param 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'

insert(self, table, params)

source code


:param table: name of the table
:param 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 )'
>>> s.insert('test',{'nom':'dupont','prenom':'jean'})
'INSERT INTO test ( nom, prenom ) VALUES ( %(nom)s, %(prenom)s )'

select(self, table, params)

source code


:param table: name of the table
:param 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(self, keys)

source code


:param keys: list of keys

>>> s = SQLGenerator()
>>> s.set(['nom'])
'nom = %(nom)s'
>>> s.set(['nom','prenom'])
'nom = %(nom)s, prenom = %(prenom)s'

update(self, table, params, unique)

source code


:param table: name of the table
:param 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(self, keys, addon=None)

source code


:param keys: list of keys

>>> 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'