"Robert (Jamie) Munro" <[EMAIL PROTECTED]> 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 update_zsql.manage_edit('','database_name',arguments,printed) 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 _______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )