def mk_update_sql(row, tbl, pkey_list, field_map = None):
r"""Generate UPDATE statement from dict data.
>>> mk_update_sql({'id': 0, 'id2': '2', 'data': 'str\\'}, 'Table', ['id', 'id2'])
'update only public."Table" set data = E\'str\\\\\' where id = \'0\' and id2 = \'2\';'
"""
if len(pkey_list) < 1:
raise Exception("update needs pkeys")
set_list = []
whe_list = []
pkmap = {}
for k in pkey_list:
pkmap[k] = 1
new_k = field_map and field_map[k] or k
col = quote_ident(new_k)
val = quote_literal(row[k])
whe_list.append("%s = %s" % (col, val))
if field_map:
for src, dst in field_map.iteritems():
if src not in pkmap:
col = quote_ident(dst)
val = quote_literal(row[src])
set_list.append("%s = %s" % (col, val))
else:
for col, val in row.iteritems():
if col not in pkmap:
col = quote_ident(col)
val = quote_literal(val)
set_list.append("%s = %s" % (col, val))
return "update only %s set %s where %s;" % (quote_fqident(tbl),
", ".join(set_list), " and ".join(whe_list))
def __init__(self, curs, table_name):
"""Initializes class by loading info about table_name from database."""
BaseStruct.__init__(self, curs, table_name)
self.table_name = table_name
# fill args
schema, name = fq_name_parts(table_name)
args = {
'schema': schema,
'table': name,
'fqname': self.fqname,
'fq2name': quote_literal(self.fqname),
'oid': get_table_oid(curs, table_name),
'pg_class_oid': get_table_oid(curs, 'pg_catalog.pg_class'),
}
# load table struct
self.col_list = self._load_elem(curs, self.name, args, TColumn)
self.object_list = [ TTable(table_name, self.col_list) ]
self.seq_list = []
# load seqs
for col in self.col_list:
if col.seqname:
owner = self.fqname + '.' + quote_ident(col.name)
seq_args = { 'fqname': col.seqname, 'owner': quote_literal(owner) }
self.seq_list += self._load_elem(curs, col.seqname, seq_args, TSeq)
self.object_list += self.seq_list
# load additional objects
to_load = [TConstraint, TIndex, TTrigger, TRule, TGrant, TOwner, TParent]
for eclass in to_load:
self.object_list += self._load_elem(curs, self.name, args, eclass)
def mk_update_sql(row, tbl, pkey_list, field_map=None):
r"""Generate UPDATE statement from dict data.
>>> mk_update_sql({'id': 0, 'id2': '2', 'data': 'str\\'}, 'Table', ['id', 'id2'])
'update only public."Table" set data = E\'str\\\\\' where id = \'0\' and id2 = \'2\';'
"""
if len(pkey_list) < 1:
raise Exception("update needs pkeys")
set_list = []
whe_list = []
pkmap = {}
for k in pkey_list:
pkmap[k] = 1
new_k = field_map and field_map[k] or k
col = quote_ident(new_k)
val = quote_literal(row[k])
whe_list.append("%s = %s" % (col, val))
if field_map:
for src, dst in field_map.iteritems():
if src not in pkmap:
col = quote_ident(dst)
val = quote_literal(row[src])
set_list.append("%s = %s" % (col, val))
else:
for col, val in row.iteritems():
if col not in pkmap:
col = quote_ident(col)
val = quote_literal(val)
set_list.append("%s = %s" % (col, val))
return "update only %s set %s where %s;" % (
quote_fqident(tbl), ", ".join(set_list), " and ".join(whe_list))
def mk_update_sql(row, tbl, pkey_list, field_map = None):
"""Generate UPDATE statement from dict data."""
if len(pkey_list) < 1:
raise Exception("update needs pkeys")
set_list = []
whe_list = []
pkmap = {}
for k in pkey_list:
pkmap[k] = 1
new_k = field_map and field_map[k] or k
col = quote_ident(new_k)
val = quote_literal(row[k])
whe_list.append("%s = %s" % (col, val))
if field_map:
for src, dst in field_map.iteritems():
if src not in pkmap:
col = quote_ident(dst)
val = quote_literal(row[src])
set_list.append("%s = %s" % (col, val))
else:
for col, val in row.iteritems():
if col not in pkmap:
col = quote_ident(col)
val = quote_literal(val)
set_list.append("%s = %s" % (col, val))
return "update %s set %s where %s;" % (quote_fqident(tbl),
", ".join(set_list), " and ".join(whe_list))
def __init__(self, curs, table_name):
"""Initializes class by loading info about table_name from database."""
BaseStruct.__init__(self, curs, table_name)
self.table_name = table_name
# fill args
schema, name = fq_name_parts(table_name)
args = {
'schema': schema,
'table': name,
'fqname': self.fqname,
'fq2name': quote_literal(self.fqname),
'oid': get_table_oid(curs, table_name),
'pg_class_oid': get_table_oid(curs, 'pg_catalog.pg_class'),
}
# load table struct
self.col_list = self._load_elem(curs, self.name, args, TColumn)
self.object_list = [ TTable(table_name, self.col_list) ]
self.seq_list = []
# load seqs
for col in self.col_list:
if col.seqname:
owner = self.fqname + '.' + quote_ident(col.name)
seq_args = { 'fqname': col.seqname, 'owner': quote_literal(owner) }
self.seq_list += self._load_elem(curs, col.seqname, seq_args, TSeq)
self.object_list += self.seq_list
# load additional objects
to_load = [TConstraint, TIndex, TTrigger, TRule, TGrant, TOwner, TParent]
for eclass in to_load:
self.object_list += self._load_elem(curs, self.name, args, eclass)
def _gen_list_insert(tbl, row, fields, qfields):
tmp = []
for i in range(len(fields)):
v = row[i]
tmp.append(quote_literal(v))
fmt = "insert into %s (%s) values (%s);"
return fmt % (tbl, ",".join(qfields), ",".join(tmp))
def _gen_dict_insert(tbl, row, fields, qfields):
tmp = []
for f in fields:
v = row.get(f)
tmp.append(quote_literal(v))
fmt = "insert into %s (%s) values (%s);"
return fmt % (tbl, ",".join(qfields), ",".join(tmp))
def _gen_dict_insert(tbl, row, fields, qfields):
tmp = []
for f in fields:
v = row.get(f)
tmp.append(quote_literal(v))
fmt = "insert into %s (%s) values (%s);"
return fmt % (tbl, ",".join(qfields), ",".join(tmp))
def _gen_list_insert(tbl, row, fields, qfields):
tmp = []
for i in range(len(fields)):
v = row[i]
tmp.append(quote_literal(v))
fmt = "insert into %s (%s) values (%s);"
return fmt % (tbl, ",".join(qfields), ",".join(tmp))
def mk_insert_sql(row, tbl, pkey_list = None, field_map = None):
"""Generate INSERT statement from dict data."""
col_list = []
val_list = []
if field_map:
for src, dst in field_map.iteritems():
col_list.append(quote_ident(dst))
val_list.append(quote_literal(row[src]))
else:
for c, v in row.iteritems():
col_list.append(quote_ident(c))
val_list.append(quote_literal(v))
col_str = ", ".join(col_list)
val_str = ", ".join(val_list)
return "insert into %s (%s) values (%s);" % (
quote_fqident(tbl), col_str, val_str)
def __str__(self):
if self.conf.param_type == PARAM_INLINE:
return quote_literal(self.value)
elif self.conf.param_type == PARAM_DBAPI:
return "%s"
elif self.conf.param_type == PARAM_PLPY:
return "$%d" % self.pos
else:
raise Exception("bad QArgConf.param_type")
def __str__(self):
if self.conf.param_type == PARAM_INLINE:
return quote_literal(self.value)
elif self.conf.param_type == PARAM_DBAPI:
return "%s"
elif self.conf.param_type == PARAM_PLPY:
return "$%d" % self.pos
else:
raise Exception("bad QArgConf.param_type")
def mk_insert_sql(row, tbl, pkey_list=None, field_map=None):
"""Generate INSERT statement from dict data.
>>> mk_insert_sql({'id': '1', 'data': None}, 'tbl')
"insert into public.tbl (data, id) values (null, '1');"
"""
col_list = []
val_list = []
if field_map:
for src, dst in field_map.iteritems():
col_list.append(quote_ident(dst))
val_list.append(quote_literal(row[src]))
else:
for c, v in row.iteritems():
col_list.append(quote_ident(c))
val_list.append(quote_literal(v))
col_str = ", ".join(col_list)
val_str = ", ".join(val_list)
return "insert into %s (%s) values (%s);" % (quote_fqident(tbl), col_str,
val_str)
def mk_delete_sql(row, tbl, pkey_list, field_map=None):
"""Generate DELETE statement from dict data."""
if len(pkey_list) < 1:
raise Exception("delete needs pkeys")
whe_list = []
for k in pkey_list:
new_k = field_map and field_map[k] or k
col = quote_ident(new_k)
val = quote_literal(row[k])
whe_list.append("%s = %s" % (col, val))
whe_str = " and ".join(whe_list)
return "delete from only %s where %s;" % (quote_fqident(tbl), whe_str)
def mk_delete_sql(row, tbl, pkey_list, field_map = None):
"""Generate DELETE statement from dict data."""
if len(pkey_list) < 1:
raise Exception("delete needs pkeys")
whe_list = []
for k in pkey_list:
new_k = field_map and field_map[k] or k
col = quote_ident(new_k)
val = quote_literal(row[k])
whe_list.append("%s = %s" % (col, val))
whe_str = " and ".join(whe_list)
return "delete from %s where %s;" % (quote_fqident(tbl), whe_str)
