[Zope-DB] optional arguments to zsql methods

garry garry at schoolteachers.co.uk
Wed Feb 22 17:13:54 EST 2006

Charlie Clark wrote:

>On 2006-02-22 at 22:10:20 [+0100], garry <garry at schoolteachers.co.uk> wrote:
>>I am importing a large data set via Zope into a Postgres database. Data
>>items may or may not exist so I am using the optional argument in the
>>zsql methods. What I want is that if the data is missing the database
>>inserts default values.
>>if type="string", the absent data item is rendered as ''
>>if type="nb", the absent data item is rendered as 'Null'
>>In either case this does not allow default values to be inserted. I can
>>insert the defaults by doing this:
>><dtml-if surnamefirst>
>>  <dtml-sqlvar  surnamefirst   type="string"> ,
>>With 20 or so inserts in one query this gets messy and could be slow? if
>>this method has to be called many times on one import cycle.
>>My question is therefore is there a cleaner way of doing this or am I on
>>the right track?
>If you really are doing a simple import then you might be better of doing 
>this in a straight script which will allow you to extend your "head" and 
>params dynamically depending on what you are inserting.
>Here's a skeleton script
>db = db.connect()
>c = db.cursor()
>sql_head = "INSERT (%s) INTO %s "
>paras = ['name', 'address', 'etc']
>sql_tail = "VALUES (%s)"
>sql_paras = ",". join(['%s'] * len(paras))
>c.execute(sql_head % (paras) + sql_tail % sql_paras, 
>Otherwise you might want to look at something like SimpleTemplates as 
>replacement for ZSQL Methods as I agree too many <dtml-if s> can make a man 
>go mad. There is the possibility of using the <dmtl-group> stuff but I find 
>this worse than a bundle of ifs.
Thanks, this gives me an idea of how to proceed. I am transforming an 
xml file into a python script, so I may as well do the whole thing in 
python rather than using zsql.

More information about the Zope-DB mailing list