Mailing List Archive: optional arguments to zsql methods

optional arguments to zsql methods

Feb 22, 2006, 1:10 PM

Post #1 of 3 (2176 views)

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?
Regards
Garry

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: optional arguments to zsql methods [ In reply to ]

charlie at egenix

Feb 22, 2006, 1:33 PM

Post #2 of 3 (2036 views)

Permalink

On 2006-02-22 at 22:10:20 [+0100], garry <garry@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.
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: optional arguments to zsql methods [ In reply to ]

garry at schoolteachers

Feb 22, 2006, 2:13 PM

Post #3 of 3 (2040 views)

Permalink

Charlie Clark wrote:

>On 2006-02-22 at 22:10:20 [+0100], garry <garry@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
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db