def _row_db(self, cur, condicion=True, cantidad=1000000):
'''Busca y escribe los data de la Base de data'''
table = sql.Table(self.name)
columns = [sql.Column(table, c['field']) for c in self._col]
#Crear la Condicion
if condicion is False:
condicion = sql.operators.Equal(1,2)
elif condicion is True:
condicion = sql.operators.And([])
elif bool(condicion) is True:
c = []
for col in columns:
if condicion.get(col.name, None) is None: continue
if isinstance(condicion.get(col.name), int):
c.append(sql.operators.Equal(col, condicion[col.name]))
if isinstance(condicion.get(col.name), str):
c.append(sql.operators.Like(col, '%{}%'.format(condicion[col.name])))
condicion = sql.operators.And(c)
elif bool(condicion) is False:
condicion = sql.operators.Equal(1,2)
statement = table.select(*columns)
statement.where = condicion
query, data = tuple(statement)
query = query.replace('"', '`')
log.debug('Ejecutando SQL: {}'.format((query, data)))
cur.execute(query, data)
con = cur.fetchmany(cantidad)
self._rows = con
log.debug('data Importados directamente {}: {}'.format(self.name, repr(self._rows)))
def __setup__(cls):
super(Formula, cls).__setup__()
t = cls.__table__()
cls._sql_constraints += [
('sheet_alias_uniq', Unique(t, t.sheet, sql.Column(t, 'alias')),
'There cannot be two formulas with the same alias in a sheet.')
]
def write(cls, *args):
table = cls.get_table()
formula_fields = [x.name for x in table.fields if x.formula]
table = cls.get_sql_table()
cursor = Transaction().connection.cursor()
has_formulas = False
all_records = []
actions = iter(args)
for records, values in zip(actions, actions):
all_records += records
fields = []
to_update = []
for key, value in values.items():
fields.append(sql.Column(table, key))
to_update.append(value)
if key in formula_fields:
has_formulas = True
query = table.update(fields,
to_update,
where=table.id.in_([x.id for x in records]))
cursor.execute(*query)
if not has_formulas and formula_fields:
cls.update_formulas(all_records)
def create(cls, vlist):
table = cls.get_table()
if table.singleton:
assert len(vlist) == 1
singleton = cls.get_singleton()
if singleton:
cls.write([singleton], vlist[0])
return [singleton]
sql_table = cls.get_sql_table()
cursor = Transaction().connection.cursor()
ids = []
for record in vlist:
fields = []
values = []
for key, value in record.items():
fields.append(sql.Column(sql_table, key))
values.append(value)
query = sql_table.insert(fields,
values=[values],
returning=[sql_table.id])
cursor.execute(*query)
ids.append(cursor.fetchone()[0])
records = cls.browse(ids)
cls.update_formulas(records)
return records
def copy_from(self, from_table):
Warning = Pool().get('res.user.warning')
fields = {x.name for x in self.fields}
from_fields = {x.name for x in from_table.fields}
missing = sorted(list(from_fields - fields))
existing = fields & from_fields
fields = {}
for field in self.fields:
if field.name in existing:
fields[field.name] = field.type
different_types = []
for field in from_table.fields:
if field.name in existing:
if (FIELD_TYPE_TRYTON[field.type] !=
FIELD_TYPE_TRYTON[fields[field.name]]):
different_types.append(
"%s (%s -> %s)" %
(field.name, field.type, fields[field.name]))
existing.remove(field.name)
if missing or different_types:
message = ['- %s' % x for x in (missing + different_types)]
key = 'shine_copy_from_warning.%s.%s' % (self.name, from_table.id),
if Warning.check(key):
raise UserWarning(
key,
gettext('shine.copy_from_warning',
fields='\n'.join(message),
from_table=from_table.rec_name,
table=self.rec_name))
if not existing:
return
existing = sorted(list(existing))
table = sql.Table(from_table.name)
subquery = table.select()
subquery.columns = [sql.Column(table, x) for x in existing]
table = sql.Table(self.name)
query = table.insert([sql.Column(table, x) for x in existing],
subquery)
cursor = Transaction().connection.cursor()
cursor.execute(*query)
def compute_sheet(self):
cursor = Transaction().connection.cursor()
table = sql.Table(self.data_table_name)
cursor.execute(*table.delete())
#fields = dict([(x.alias, x) for x in self.formulas])
direct_fields = [x.alias for x in self.formulas if not x.expression]
formula_fields = [x.alias for x in self.formulas if x.expression]
sql_fields = [
sql.Column(table, x) for x in direct_fields + formula_fields
]
parser = formulas.Parser()
formula_fields = [(x, parser.ast(x.expression)[1].compile()
if x.expression.startswith('=') else '')
for x in self.formulas if x.expression]
insert_values = []
checker = TimeoutChecker(self.timeout, self.timeout_exception)
if not formula_fields:
# If there are no formula_fields we can make the loop faster as
# we don't use OrderedDict and don't evaluate formulas
for records in self.dataset.get_data():
checker.check()
for record in records:
insert_values.append(
[getattr(record, x) for x in direct_fields])
else:
for records in self.dataset.get_data():
checker.check()
for record in records:
values = OrderedDict()
if direct_fields:
values.update(
OrderedDict([(x, getattr(record, x))
for x in direct_fields]))
if formula_fields:
for field, ast in formula_fields:
if field.expression.startswith('='):
inputs = []
for input_ in ast.inputs.keys():
# TODO: Check if input_ exists and raise
# proper user error Indeed, we should check
# de formulas when we move to active state
inputs.append(values[input_.lower()])
value = ast(inputs)[0]
else:
value = field.expression
ftype = FIELD_TYPE_PYTHON[field.type]
values[field] = ftype(value)
insert_values.append(list(values.values()))
if insert_values:
cursor.execute(*table.insert(sql_fields, insert_values))
def update_db(self, columns=None, keys=[]):
'''Realisa un UPDATE de todas la columns usando automaticamente las keys PRI para la clausula WHERE,
las keys no tienen que estar dentro de la columns a update,
se pueden usar columns propias como keys a responsabilidad del usuario,
regresa ROW_COUNT() por conveniencia'''
#Busca las keys PRI
keys = [i['field'] for i in self._col if (i['key'] == 'PRI')] + list(keys)
assert keys, "La table no tiene ninguna column PRI o column que usar como key"
cur = self.conection.cursor()
table = sql.Table(self.name)
keys = [sql.Column(table, l) for l in keys]
cols = [col['field'] for col in self._col if col['type'] != "timestamp"]
columns = [sql.Column(table, c) for c in (cols if columns is None else columns)]
for fil in self.values:
data = {'':None,}
values = [fil[col.name] if not fil[col.name] in data else data[fil[col.name]] for col in columns]
condicion = sql.operators.And([sql.operators.Equal(l, fil[l.name]) for l in keys])
statement = table.update(columns = columns, values=values, where=condicion)
query, data = tuple(statement)
query = query.replace('"', '`')
log.debug('Ejecutando SQL: {}'.format((query, data)))
cur.execute(query, data)
return cur.rowcount
def insert_db(self, columns=None):
'''Exporta los data de la table a la base de data, regresa lasrowid por conveniencia'''
cur = self.conection.cursor()
table = sql.Table(self.name)
cols = [col['field'] for col in self._col]
columns = [sql.Column(table, c) for c in (cols if columns is None else columns)]
data = {'':None,}
values = [[fil[col.name] if not fil[col.name] in data else data[fil[col.name]] for col in columns] for fil in self.values]
statement = table.insert(columns, values)
query, data = tuple(statement)
query = query.replace('"', '`')
log.debug('Ejecutando SQL: {}'.format((query, data)))
cur.execute(query, data)
return cur.lastrowid
def remove_db(self, keys=[]):
'''Realisa un DELETE usando automaticamente las keys PRI para la clausula WHERE,
se pueden usar columns propias como keys a responsabilidad del usuario,
regresa ROW_COUNT() por conveniencia'''
#Busca las keys PRI
keys = [i['field'] for i in self._col if (i['key'] == 'PRI')] + list(keys)
assert keys, "La table no tiene ninguna column PRI o column que usar como key"
cur = self.conection.cursor()
table = sql.Table(self.name)
keys = [sql.Column(table, l) for l in keys]
for fil in self.values:
condicion = sql.operators.And([sql.operators.Equal(l, fil[l.name]) for l in keys])
statement = table.delete(where=condicion)
query, data = tuple(statement)
query = query.replace('"', '`')
log.debug('Ejecutando SQL: {}'.format((query, data)))
cur.execute(query, data)
return cur.rowcount