def write(self, id, values_d):
""" Update records on the external system """
_logger.debug(
'method write, sql %s id %s, values %s',
self._sql, id, values_d)
if not values_d:
return 0
# check if schema exists to avoid injection
schema_exists = self._exec_sql("select 1 from sys.schemas where name=%s", (self.schema,))
if not schema_exists:
raise pymssql.InternalError("The schema %s does not exist" % self.schema)
# get id fieldnames and values
id_d = dict(zip(self._id, id))
# fix same field on set and on where, change set fields
qset_map_d = {}
for k, v in values_d.items():
if k in id_d:
while True:
k9 = '%s%i' % (k, random.randint(0, 999))
if k9 not in values_d and k9 not in id_d:
qset_map_d[k] = (k9, v)
break
else:
qset_map_d[k] = (k, v)
# get the set data
qset_l = []
for k, (k9, v) in qset_map_d.items():
qset_l.append('%(field)s = %%(%(field9)s)s' % dict(field=k, field9=k9))
qset = "%s" % (', '.join(qset_l),)
# prepare the sql with base strucrture
sql = self._sql_update % dict(schema=self.schema, qset=qset)
# prepare params
params = dict(id_d)
for k, (k9, v) in qset_map_d.items():
params[k9] = v
conn = self.conn()
cr = conn.cursor()
cr.execute(sql, params)
count = cr.rowcount
if count == 0:
raise Exception(_("Impossible to update external record with ID '%s': "
"Register not found on Backend") % (id_d,))
elif count > 1:
conn.rollback()
raise pymssql.IntegrityError("Unexpected error: Returned more the one row with ID: %s" % (id_d,))
conn.commit()
cr.close()
conn.close()
return count
def _exec_query(self, filters=None, fields=None, as_dict=True):
# check if schema exists to avoid injection
schema_exists = self._exec_sql(
"select 1 from sys.schemas where name=%s", (self.schema, ))
if not schema_exists:
raise pymssql.InternalError("The schema %s does not exist" %
self.schema)
# prepare the sql and execute
sql = self._sql % dict(schema=self.schema)
values = []
if filters or fields:
sql_l = ["with t as (%s)" % sql]
fields_l = fields or ['*']
if fields:
if self._id:
for f in self._id:
if f not in fields_l:
fields_l.append(f)
sql_l.append("select %s from t" % (', '.join(fields_l), ))
if filters:
where = []
for k, v in filters.items():
if isinstance(v, (tuple, list)):
op, pars = v
if op == '=':
where.append('%s = %%s' % k)
values.append(pars)
elif op == 'in':
where.append('%s in %%s' % k)
values.append(pars)
elif op == 'between':
where.append('%s between %%s and %%s' % k)
values += list(pars)
else:
raise Exception("Operator %s not implemented" % op)
else:
where.append('%s = %%s' % k)
values.append(v)
sql_l.append("where %s" % (' and '.join(where), ))
sql = ' '.join(sql_l)
res = self._exec_sql(sql, tuple(values), as_dict=as_dict)
if self._id and set(self._id).issubset(set(filters)):
self._check_uniq(res)
return res
def _exec_query(self, filters=[], fields=None, as_dict=True):
# check if schema exists to avoid injection
schema_exists = self._exec_sql("select 1 from sys.schemas where name=%s", (self.schema,))
if not schema_exists:
raise pymssql.InternalError("The schema %s does not exist" % self.schema)
# prepare the sql and execute
sql = self._sql % dict(schema=self.schema)
values = []
if filters or fields:
sql_l = ["with t as (%s)" % sql]
fields_l = fields or ['*']
if fields:
if self._id:
for f in self._id:
if f not in fields_l:
fields_l.append(f)
sql_l.append("select %s from t" % (', '.join(fields_l),))
if filters:
where = []
for k, operator, v in filters:
if v is None:
if operator == '=':
operator = 'is'
elif operator == '!=':
operator = 'is not'
else:
raise Exception("Operator '%s' is not implemented on NULL values" % operator)
where.append('%s %s %%s' % (k, operator))
values.append(v)
sql_l.append("where %s" % (' and '.join(where),))
sql = ' '.join(sql_l)
res = self._exec_sql(sql, tuple(values), as_dict=as_dict)
filter_keys_s = {e[0] for e in filters}
if self._id and set(self._id).issubset(filter_keys_s):
self._check_uniq(res)
return res
def create(self, values_d):
""" Create a record on the external system """
_logger.debug(
'method create, model %s, attributes %s',
self._name, values_d)
if not values_d:
return 0
# check if schema exists to avoid injection
schema_exists = self._exec_sql("select 1 from sys.schemas where name=%s", (self.schema,))
if not schema_exists:
raise pymssql.InternalError("The schema %s does not exist" % self.schema)
# build the sql parts
fields, params, phvalues = [], [], []
for k, v in values_d.items():
fields.append(k)
params.append(v)
if v is None or isinstance(v, str):
phvalues.append('%s')
elif isinstance(v, (int, float)):
phvalues.append('%d')
else:
raise NotImplementedError("Type %s" % type(v))
# build retvalues
retvalues = ['inserted.%s' % x for x in self._id]
# prepare the sql with base structure
sql = self._sql_insert % dict(schema=self.schema,
fields=', '.join(fields),
phvalues=', '.join(phvalues),
retvalues=', '.join(retvalues))
# executem la insercio
res = self._exec_sql(sql, tuple(params), commit=True)
if not res:
raise Exception(_("Unexpected!! Nothing created: %s") % (values_d,))
elif len(res) > 1:
raise Exception("Unexpected!!: Returned more the one row:%s - %s" % (res, values_d,))
return res[0]
def _exec_query(self, filters=None, fields=None, as_dict=True):
# check if schema exists to avoid injection
schema_exists = self._exec_sql(
"select 1 from sys.schemas where name=%s", (self.schema, ))
if not schema_exists:
raise pymssql.InternalError("The schema %s does not exist" %
self.schema)
# prepare the sql and execute
sql = self._sql % dict(schema=self.schema)
values = []
if filters or fields:
sql_l = ["with t as (%s)" % sql]
fields_l = fields or ['*']
if fields:
if self._id:
for f in self._id:
if f not in fields_l:
fields_l.append(f)
sql_l.append("select %s from t" % (', '.join(fields_l), ))
if filters:
where = []
for k, v in filters.items():
where.append('%s = %%s' % k)
values.append(v)
sql_l.append("where %s" % (' and '.join(where), ))
sql = ' '.join(sql_l)
res = self._exec_sql(sql, tuple(values), as_dict=as_dict)
if self._id and set(self._id).issubset(set(filters)):
self._check_uniq(res)
return res