[Zope-DB] optional arguments to zsql methods

[Charlie Clark]

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.