def get_editable_data(cls, data, update=True):
if update:
editable_data = dict(zip_values(cls.EDITABLE_FIELDS, data, empty_fields=1))
else:
data.update(dt_created=datetime.utcnow())
editable_data = dict(zip_values(cls.CREATE_FIELDS, data, empty_fields=1))
return editable_data
def get_editable_data(cls, data, update=True):
if update:
editable_data = dict(
zip_values(cls.EDITABLE_FIELDS, data, empty_fields=1))
else:
data.update(dt_created=datetime.utcnow())
editable_data = dict(
zip_values(cls.CREATE_FIELDS, data, empty_fields=1))
return editable_data
def get_insert_query(tbl, insert_data, create_columns=None, returning=u'id', q_id=None):
"""
:type insert_data: dict
:return: valid SQL request query
:rtype: unicode
"""
columns = create_columns or ALL_TABLES[tbl].keys()
column_values = zip_values(columns, insert_data)
fields = u", ".join([v[0] for v in column_values])
values = []
for value in column_values:
store = ALL_TABLES[tbl][value[0]].store
if not store:
values.append(value[1])
continue
if value[1] == 'NULL':
values.append(None)
continue
values.append(store(value[1]))
values = u"'" + u"', '".join([v for v in values]) + u"'" if len(values) > 1 else u"'{}'".format(values[0])
values = values.replace(u'%', u'%%')
returning = u'RETURNING {r_id}'.format(r_id=returning) if returning else u''
sql_string = u'INSERT INTO {table_name} ({fields}) VALUES ({values}) {ret}'.format(table_name=tbl,
fields=fields,
values=values,
ret=returning)
if q_id:
sql_string = u'SELECT execute_query({q_id}, {q})'.format(q_id=q_id, q=sql_string)
return sql_string
def get_update_query(tbl, update_params, where_params=None, editable_columns=None, returning=u'id'):
if where_params is None:
where_params = {}
columns = editable_columns or ALL_TABLES[tbl].keys()
columns = list(set(update_params.keys()).intersection(set(columns)))
column_values = dict(zip_values(columns, update_params, empty_fields=1))
sql_string = u"UPDATE {table_name} SET".format(table_name=tbl)
for i, k in enumerate(column_values.keys()):
value = update_params[k]
store = ALL_TABLES[tbl][k].store
v = value if not store else store(value)
if isinstance(v, str):
v = v.decode('utf-8').encode('utf-8')
else:
v = v.encode('utf-8')
if v != 'NULL':
sql_string = "{prefix} {title}=E'{value}'".format(prefix=sql_string, title=k, value=v)
else:
sql_string = "{prefix} {title}={value}".format(prefix=sql_string, title=k, value=v)
if i < len(column_values.keys()) - 1:
sql_string += ','
sql_string = "{prefix} WHERE".format(prefix=sql_string)
for i, k in enumerate(where_params.keys()):
sql_string = "{prefix} {title}='{value}'".format(prefix=sql_string, title=k, value=where_params[k])
if i < len(where_params.keys()) - 1:
sql_string += u' AND '
returning = u'RETURNING {r_id}'.format(r_id=returning) if returning else u''
sql_string += returning
return sql_string
def get_update_query(tbl,
update_params,
where_params=None,
editable_columns=None,
returning=u'id'):
if where_params is None:
where_params = {}
columns = editable_columns or ALL_TABLES[tbl].keys()
columns = list(set(update_params.keys()).intersection(set(columns)))
column_values = dict(zip_values(columns, update_params, empty_fields=1))
sql_string = u"UPDATE {table_name} SET".format(table_name=tbl)
for i, k in enumerate(column_values.keys()):
value = update_params[k]
store = ALL_TABLES[tbl][k].store
v = value if not store else store(value)
if isinstance(v, str):
v = v.decode('utf-8').encode('utf-8')
else:
v = v.encode('utf-8')
if v != 'NULL':
sql_string = "{prefix} {title}=E'{value}'".format(
prefix=sql_string, title=k, value=v)
else:
sql_string = "{prefix} {title}={value}".format(prefix=sql_string,
title=k,
value=v)
if i < len(column_values.keys()) - 1:
sql_string += ','
sql_string = "{prefix} WHERE".format(prefix=sql_string)
for i, k in enumerate(where_params.keys()):
sql_string = "{prefix} {title}='{value}'".format(prefix=sql_string,
title=k,
value=where_params[k])
if i < len(where_params.keys()) - 1:
sql_string += u' AND '
returning = u'RETURNING {r_id}'.format(
r_id=returning) if returning else u''
sql_string += returning
return sql_string
def get_insert_query(tbl,
insert_data,
create_columns=None,
returning=u'id',
q_id=None):
"""
:type insert_data: dict
:return: valid SQL request query
:rtype: unicode
"""
columns = create_columns or ALL_TABLES[tbl].keys()
column_values = zip_values(columns, insert_data)
fields = u", ".join([v[0] for v in column_values])
values = []
for value in column_values:
store = ALL_TABLES[tbl][value[0]].store
if not store:
values.append(value[1])
continue
if value[1] == 'NULL':
values.append(None)
continue
values.append(store(value[1]))
values = u"'" + u"', '".join(
[v for v in values]) + u"'" if len(values) > 1 else u"'{}'".format(
values[0])
values = values.replace(u'%', u'%%')
returning = u'RETURNING {r_id}'.format(
r_id=returning) if returning else u''
sql_string = u'INSERT INTO {table_name} ({fields}) VALUES ({values}) {ret}'.format(
table_name=tbl, fields=fields, values=values, ret=returning)
if q_id:
sql_string = u'SELECT execute_query({q_id}, {q})'.format(q_id=q_id,
q=sql_string)
return sql_string
