[Zope] Generic SQL insert

Michael Shulman shulman at mathcamp.org
Sat Apr 15 15:35:40 EDT 2006

"Robert (Jamie) Munro" <jamie at textmatters.com> wrote:
> I'd much rather have a dictionary of fields and values, and just throw
> it at the DB, not having to make those queries for every table. I have
> acheived it like so:

I have achieved a similar result, although for update methods in my
case, in what I think is a slightly better way (although probably
people on this list will tell me I'm wrong).  Rather than having a
single generic ZSQL method, I have one ZSQL method for each table, but
intsead of writing them all by hand I have a single script which
generates/regenerates them from inspection of the columns in the
database, like so:

# select some arbitrary record from the table into table_data, then:
fields = table_data.data_dictionary()
arguments = ""
comma = ""
print "update %s set" % table

types = {'s':"nb", 'd':"nb", 'i':"int", 'n':"nb"}
for field in fields.keys():
   arguments = arguments + field + " "
   type = types[fields[field]["type"]]
   print "   %s%s = <dtml-if expr=\"%s is
None\">null<dtml-else><dtml-sqlvar %s type=\"%s\" optional></dtml-if>"
% (comma, field, field, field, type)
   comma = ","

print " where %s = <dtml-sqlvar %s type=\"int\">" % (id_field, id_field)

# update_zsql is the ZSQL "update" method I am creating/updating

This way, all the columns are assigned the correct type, but the
calling script doesn't have to handle types or casts, since all the
types are assigned by inspecting the actual database.  Also, this way
the update/insert will fail if any fields are missing.

Actually, given how object-oriented Zope is in general, I was
surprised to find that its database interface (ZSQL methods) still
requires the user to write SQL code essentially by hand, rather than
using an object-relational mapper like SQLObject.  There appears to be
a  toolkit called sqlos for using SQLObject with Zope 3, but I haven't
yet tried it out.

> mydict = {"field1":"value1" , "field2":"value2" ,...}
> (fields,values)=zip(*myDict.items())
> context.genericInsert(table='table name',fields=fields,values=values)
> Where generic insert is the following ZSQL method:
> insert into "<dtml-var table>"
>  (<dtml-in expr="fields">"<dtml-var sequence-item>"<dtml-if
> sequence-end><dtml-else>,</dtml-if></dtml-in>)
>  values (<dtml-in expr="values"><dtml-sqlvar sequence-item
> type=string><dtml-if sequence-end><dtml-else>,</dtml-if></dtml-in>);
> with parameters:
> * table - table name
> * fields - list of fieldnames
> * values - list of values in the same order
> What do other people think of this? Is it a really bad idea?
> Robert Munro

More information about the Zope mailing list