Re: [Zope] Generic SQL insert
I agree. I am just starting to work with zalchemy in Zope3 and looking to try and make it work with Five so I have it for Zope2. Regards, David Chris Withers wrote: Robert (Jamie) Munro wrote: Whenever I'm using SQL databases in zope, I always seem to have to make a ZSQL instance for inserting into every table in my database, and they are all nearly the same - they just have a list of all the fields in the database in the parameters, then they say: insert into [table] ([list of fields]) values ([list of s]) You're probably better off looking into an ORM like SQLAlchemy... Chris ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] Generic SQL insert
Robert (Jamie) Munro wrote: Whenever I'm using SQL databases in zope, I always seem to have to make a ZSQL instance for inserting into every table in my database, and they are all nearly the same - they just have a list of all the fields in the database in the parameters, then they say: insert into [table] ([list of fields]) values ([list of s]) You're probably better off looking into an ORM like SQLAlchemy... Chris -- Simplistix - Content Management, Zope & Python Consulting - http://www.simplistix.co.uk ___ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
[Zope] Generic SQL insert & Rakun XML Application Platform
Hi,Have you tried Rakun Project for this problem. It supports MySQL, PostgreSQL and SQLite for now. But it can handle all kind of SQL databases.Please take a look at its web site:
http://www.rakun.org/For all kind of questions about it, you can contact me>Content-Type: text/plain; charset="US-ASCII">>Great idea. Not to be recommended in general.
>>This works because every field is textual, and you are>sql-quoting by using type=string.>>Here are the problems:>1) if someone reads this and does not use the type=string>tag, or equivalent, they will be wide open to sql injection.
>2) OR, they can pass a list of type with each variable.>3) If you have to handle casts, then you will have to pass>a list of cast-types, as well.>>So, you have essentially moved the problem from making at
>least one insertion call per table to a single insertion method>that requires the creation of two, three, or four lists. This does>not self-evidently require less work.>>You can no longer inspect the method to see if it is correct.
>You have to look to each call-point to determine what is actually>being used. Just as bad, your application goes happily on its way if you>are missing (non-key) variables.>>Keep zsql methods a simple as possible. Use as few tricks as
>possible. Your goal is self-evident correctness, not the minimization>of typing.>jim penny[EMAIL PROTECTED] wrote on 04/13/2006 02:23:22 PM:
> Whenever I'm using SQL databases in zope, I always seem to have to make> a ZSQL instance for inserting into every table in my database, and they> are all nearly the same - they just have a list of all the fields in the
> database in the parameters, then they say:>> insert into [table] ([list of fields]) values ([list of s])>> I'd much rather have a dictionary of fields and values, and just throw
> it at the DB, not having to make those queries for every table. I have> acheived it like so:>> mydict = {"field1":"value1" , "field2":"value2" ,...}
> (fields,values)=zip(*myDict.items())> context.genericInsert(table='table name',fields=fields,values=values)>> Where generic insert is the following ZSQL method:> insert into ""
> (""> sequence-end>,)> values (> type=string>,);>> with parameters:> * table - table name> * fields - list of fieldnames> * values - list of values in the same order
>> What do other people think of this? Is it a really bad idea?>> Robert Munro> ___> Zope maillist - Zope@zope.org
> http://mail.zope.org/mailman/listinfo/zope> ** No cross posts or HTML encoding! **> (Related lists ->
http://mail.zope.org/mailman/listinfo/zope-announce> http://mail.zope.org/mailman/listinfo/zope-dev )>
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] Generic SQL insert
"Robert (Jamie) Munro" <[EMAIL PROTECTED]> wrote:
> I'd much rather have a dictionary of fields and values, and just throw
> it at the DB, not having to make those queries for every table. I have
> acheived it like so:
I have achieved a similar result, although for update methods in my
case, in what I think is a slightly better way (although probably
people on this list will tell me I'm wrong). Rather than having a
single generic ZSQL method, I have one ZSQL method for each table, but
intsead of writing them all by hand I have a single script which
generates/regenerates them from inspection of the columns in the
database, like so:
# select some arbitrary record from the table into table_data, then:
fields = table_data.data_dictionary()
arguments = ""
comma = ""
print "update %s set" % table
types = {'s':"nb", 'd':"nb", 'i':"int", 'n':"nb"}
for field in fields.keys():
arguments = arguments + field + " "
type = types[fields[field]["type"]]
print " %s%s = null"
% (comma, field, field, field, type)
comma = ","
print " where %s = " % (id_field, id_field)
# update_zsql is the ZSQL "update" method I am creating/updating
update_zsql.manage_edit('','database_name',arguments,printed)
This way, all the columns are assigned the correct type, but the
calling script doesn't have to handle types or casts, since all the
types are assigned by inspecting the actual database. Also, this way
the update/insert will fail if any fields are missing.
Actually, given how object-oriented Zope is in general, I was
surprised to find that its database interface (ZSQL methods) still
requires the user to write SQL code essentially by hand, rather than
using an object-relational mapper like SQLObject. There appears to be
a toolkit called sqlos for using SQLObject with Zope 3, but I haven't
yet tried it out.
>
> mydict = {"field1":"value1" , "field2":"value2" ,...}
> (fields,values)=zip(*myDict.items())
> context.genericInsert(table='table name',fields=fields,values=values)
>
> Where generic insert is the following ZSQL method:
> insert into ""
> ("" sequence-end>,)
> values ( type=string>,);
>
> with parameters:
> * table - table name
> * fields - list of fieldnames
> * values - list of values in the same order
>
> What do other people think of this? Is it a really bad idea?
>
> Robert Munro
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] Generic SQL insert
Great idea. Not to be recommended in general.
This works because every field is textual, and you are
sql-quoting by using type=string.
Here are the problems:
1) if someone reads this and does not use the type=string
tag, or equivalent, they will be wide open to sql injection.
2) OR, they can pass a list of type with each variable.
3) If you have to handle casts, then you will have to pass
a list of cast-types, as well.
So, you have essentially moved the problem from making at
least one insertion call per table to a single insertion method
that requires the creation of two, three, or four lists. This does
not self-evidently require less work.
You can no longer inspect the method to see if it is correct.
You have to look to each call-point to determine what is actually
being used. Just as bad, your application goes happily on its way if you
are missing (non-key) variables.
Keep zsql methods a simple as possible. Use as few tricks as
possible. Your goal is self-evident correctness, not the minimization
of typing.
jim penny
[EMAIL PROTECTED] wrote on 04/13/2006 02:23:22 PM:
> Whenever I'm using SQL databases in zope, I always seem to have to make
> a ZSQL instance for inserting into every table in my database, and they
> are all nearly the same - they just have a list of all the fields in the
> database in the parameters, then they say:
>
> insert into [table] ([list of fields]) values ([list of s])
>
> I'd much rather have a dictionary of fields and values, and just throw
> it at the DB, not having to make those queries for every table. I have
> acheived it like so:
>
> mydict = {"field1":"value1" , "field2":"value2" ,...}
> (fields,values)=zip(*myDict.items())
> context.genericInsert(table='table name',fields=fields,values=values)
>
> Where generic insert is the following ZSQL method:
> insert into ""
> ("" sequence-end>,)
> values ( type=string>,);
>
> with parameters:
> * table - table name
> * fields - list of fieldnames
> * values - list of values in the same order
>
> What do other people think of this? Is it a really bad idea?
>
> Robert Munro
> ___
> Zope maillist - Zope@zope.org
> http://mail.zope.org/mailman/listinfo/zope
> ** No cross posts or HTML encoding! **
> (Related lists -
> http://mail.zope.org/mailman/listinfo/zope-announce
> http://mail.zope.org/mailman/listinfo/zope-dev )
>
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
[Zope] Generic SQL insert
Whenever I'm using SQL databases in zope, I always seem to have to make
a ZSQL instance for inserting into every table in my database, and they
are all nearly the same - they just have a list of all the fields in the
database in the parameters, then they say:
insert into [table] ([list of fields]) values ([list of s])
I'd much rather have a dictionary of fields and values, and just throw
it at the DB, not having to make those queries for every table. I have
acheived it like so:
mydict = {"field1":"value1" , "field2":"value2" ,...}
(fields,values)=zip(*myDict.items())
context.genericInsert(table='table name',fields=fields,values=values)
Where generic insert is the following ZSQL method:
insert into ""
("",)
values (,);
with parameters:
* table - table name
* fields - list of fieldnames
* values - list of values in the same order
What do other people think of this? Is it a really bad idea?
Robert Munro
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
