[Zope-DB] optional arguments to zsql methods
charlie at egenix.com
Wed Feb 22 16:33:56 EST 2006
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.
More information about the Zope-DB