[Zope-DB] optional arguments to zsql methods

[garry]

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.
>>However,
>>
>>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:
>>.....ZSQL.....
>>snip.....
>><dtml-if surnamefirst>
>>  <dtml-sqlvar  surnamefirst   type="string"> ,
>><dtml-else>
>>DEFAULT,
>></dtml-if>
>>
>>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, 
>values_you_want_to_insert)
>
>
>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.
Regards
Garry