Mailing List Archive: Zope: DB

ZSQL Question - Insert multiple rows in one statement?

Index | Next | Previous | View Threaded

mark at phillipsmarketing

Aug 24, 2010, 8:03 AM

Post #1 of 11 (2795 views)
Permalink

ZSQL Question - Insert multiple rows in one statement?

When I retrieve multiple values from a selection box, I need to create a
loop somewhere to insert the multiple values (rows) into a table. Is there a
way to do this within a ZSQL statement, or is it best to do the looping in a
Python script?

My specific example....

table 1 (Players) has information about players (i.e. team members for a
sports team). There is a primary key - playerID
table 2 (Seasons) has information about each season - primary key is
seasonID
table 3 (PlayerSeasons) has two columns - playerID and seasonID.

The form to crud a player has a combobox where one can select multiple
seasons for a player. When I do an add or update, I have to add one or more
rows to the PlayerSeasons table. Does this loop have to be in a Python
script, or can it be implemented in ZSQL? I looked at the 'multiple'
keyword, but all the examples indicate that it apples to sql tests, such as
testing against a set of values. I can't seem to find any examples where one
is inserting multiple rows into a table.

Thanks!

Mark

lists at zopyx

Aug 24, 2010, 8:06 AM

Post #2 of 11 (2730 views)
Permalink

Re: ZSQL Question - Insert multiple rows in one statement? [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I would assume that you can use DTML-IN for looping and generating
multiple SQL statements of the same kind. You have to ensure that
the statements having a proper delimiter (there was something in DTML?!
DTML-SQLDELIMITER?...no idea, you need to checks the docs of this
ancient technology).

- -aj

Mark Phillips wrote:
> When I retrieve multiple values from a selection box, I need to create a
> loop somewhere to insert the multiple values (rows) into a table. Is
> there a way to do this within a ZSQL statement, or is it best to do the
> looping in a Python script?
>
> My specific example....
>
> table 1 (Players) has information about players (i.e. team members for a
> sports team). There is a primary key - playerID
> table 2 (Seasons) has information about each season - primary key is
> seasonID
> table 3 (PlayerSeasons) has two columns - playerID and seasonID.
>
> The form to crud a player has a combobox where one can select multiple
> seasons for a player. When I do an add or update, I have to add one or
> more rows to the PlayerSeasons table. Does this loop have to be in a
> Python script, or can it be implemented in ZSQL? I looked at the
> 'multiple' keyword, but all the examples indicate that it apples to sql
> tests, such as testing against a set of values. I can't seem to find any
> examples where one is inserting multiple rows into a table.
>
> Thanks!
>
> Mark
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB [at] zope
> https://mail.zope.org/mailman/listinfo/zope-db

- --
ZOPYX Limited | zopyx group
Charlottenstr. 37/1 | The full-service network for Zope & Plone
D-72070 Tübingen | Produce & Publish
www.zopyx.com | www.produce-and-publish.com
- ------------------------------------------------------------------------
E-Publishing, Python, Zope & Plone development, Consulting

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQGUBAEBAgAGBQJMc9/tAAoJEADcfz7u4AZjlPwLwNiLG+kZUrlZ+BuFEWKL42eE
bmzllHixdEq6qy2gHP3pbf5AmICS66JJGfqaI6Gv7JqxY6XM1N35oeXK7PzUk+gb
PYPtIwN62HMQzrvYXB6JHyFEkyMuOd9MFyyHMgh24JqC6xtMBbYI3+yjiXJor1QP
Xd56qoxWmhHZVnC2YhddpR3DLlAx/qebi2mk+C15g2C+LkVzz0J2rHb5FNB/Izdt
uJmknn9pDjBewSQhtPIsX/rj7R4SUtJUZ78H8Isn2yoEftsG4ONtpzT3O9ICXF4R
Y4V/iV9KepUoxU5dpH9YDTl00YA6UVvyafhDufkPzq5dnKUQL+QzPXFcmC9tSRux
OIDiTOLZwYVS0a5hT/OUtYB1p+JGhITxLKxspXH4vJ5po8IJR7CmxO0FOooqGeOc
muaWclDrxPIsyWEAEZg6+ahyydWgFCePuMLCrEEvhZRpx4DxvhisXRVB5V/h6Z7L
OSSYDljCM/mu1mKIPRUuHqBlc0mB4qI=
=1o0Q
-----END PGP SIGNATURE-----

Attachments:

lists.vcf (0.31 KB)

s.gottfried at srz

Aug 24, 2010, 8:55 AM

Post #3 of 11 (2730 views)
Permalink

Re: ZSQL Question - Insert multiple rows in one statement? [In reply to]

Hi Mark,

it is best to do the looping in python. consider the python script as
the proper place to process the form values taken from the request
variable. No problem to invoke multiple zsql method calls from within
this script. I even remember that all invoked zsql methods in one
requests belong to a transaction. If one fails, a rollback is done. Your
ZSQL connection has to be configured properly to use transactions and
your server has to support them as well.

Just in case you do not already found this - latest zope book and the
chapter about SQL connectivity
http://docs.zope.org/zope2/zope2book/RelationalDatabases.html

Am 24.08.2010 17:03, schrieb Mark Phillips:
> When I retrieve multiple values from a selection box, I need to create
> a loop somewhere to insert the multiple values (rows) into a table. Is
> there a way to do this within a ZSQL statement, or is it best to do
> the looping in a Python script?
>
> My specific example....
>
> table 1 (Players) has information about players (i.e. team members for
> a sports team). There is a primary key - playerID
> table 2 (Seasons) has information about each season - primary key is
> seasonID
> table 3 (PlayerSeasons) has two columns - playerID and seasonID.
>
> The form to crud a player has a combobox where one can select multiple
> seasons for a player. When I do an add or update, I have to add one or
> more rows to the PlayerSeasons table. Does this loop have to be in a
> Python script, or can it be implemented in ZSQL? I looked at the
> 'multiple' keyword, but all the examples indicate that it apples to
> sql tests, such as testing against a set of values. I can't seem to
> find any examples where one is inserting multiple rows into a table.
>
> Thanks!
>
> Mark
>
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB [at] zope
> https://mail.zope.org/mailman/listinfo/zope-db
>
>
>
> __________ Hinweis von ESET NOD32 Antivirus, Signaturdatenbank-Version 5393 (20100824) __________
>
> E-Mail wurde geprüft mit ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>

__________ Hinweis von ESET NOD32 Antivirus, Signaturdatenbank-Version 5393 (20100824) __________

E-Mail wurde geprüft mit ESET NOD32 Antivirus.

http://www.eset.com

garry at schoolteachers

Aug 24, 2010, 9:16 AM

Post #4 of 11 (2747 views)
Permalink

Re: ZSQL Question - Insert multiple rows in one statement? [In reply to]

Andreas Jung wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> I would assume that you can use DTML-IN for looping and generating
> multiple SQL statements of the same kind. You have to ensure that
> the statements having a proper delimiter (there was something in DTML?!
> DTML-SQLDELIMITER?...no idea, you need to checks the docs of this
> ancient technology).

Andreas is correct but the delimiter is <dtml-sql_delimiter>.
Probably better to do this in python script, but here are some code
snippets to help you:
In the form:

<select name="seasons:list" multiple="multiple">

In the ZSQL method:

<dtml-in seasons>
<dtml-var sql_delimiter>

insert statement goes here, don't forget the ; at the end.

</dtml-in>

Hope this is of some help, others may want to chip in with other
suggestions, such as doing the same in python. Anyway good luck.
Garry
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
https://mail.zope.org/mailman/listinfo/zope-db

garry at schoolteachers

Aug 24, 2010, 9:48 AM

Post #5 of 11 (2739 views)
Permalink

Re: ZSQL Question - Insert multiple rows in one statement? [In reply to]

Garry Saddington wrote:
> Andreas Jung wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> I would assume that you can use DTML-IN for looping and generating
>> multiple SQL statements of the same kind. You have to ensure that
>> the statements having a proper delimiter (there was something in DTML?!
>> DTML-SQLDELIMITER?...no idea, you need to checks the docs of this
>> ancient technology).
>
oops:
<dtml-var sql_delimiter>
code in snippets is correct though.
sorry
Garry

> Andreas is correct but the delimiter is <dtml-sql_delimiter>.
> Probably better to do this in python script, but here are some code
> snippets to help you:
> In the form:
>
> <select name="seasons:list" multiple="multiple">
>
> In the ZSQL method:
>
> <dtml-in seasons>
> <dtml-var sql_delimiter>
>
> insert statement goes here, don't forget the ; at the end.
>
> </dtml-in>
>
> Hope this is of some help, others may want to chip in with other
> suggestions, such as doing the same in python. Anyway good luck.
> Garry
> _______________________________________________
> Zope-DB mailing list
> Zope-DB [at] zope
> https://mail.zope.org/mailman/listinfo/zope-db
>

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
https://mail.zope.org/mailman/listinfo/zope-db

mark at phillipsmarketing

Aug 24, 2010, 1:32 PM

Post #6 of 11 (2728 views)
Permalink

Re: ZSQL Question - Insert multiple rows in one statement? [In reply to]

Thanks everyone for their help. I decided to do the looping in the Python
Controller Script instead of dtml...much easier and faster to put it
together!

Mark

On Tue, Aug 24, 2010 at 9:48 AM, Garry Saddington <
garry [at] schoolteachers> wrote:

> Garry Saddington wrote:
> > Andreas Jung wrote:
> >> -----BEGIN PGP SIGNED MESSAGE-----
> >> Hash: SHA1
> >>
> >> I would assume that you can use DTML-IN for looping and generating
> >> multiple SQL statements of the same kind. You have to ensure that
> >> the statements having a proper delimiter (there was something in DTML?!
> >> DTML-SQLDELIMITER?...no idea, you need to checks the docs of this
> >> ancient technology).
> >
> oops:
> <dtml-var sql_delimiter>
> code in snippets is correct though.
> sorry
> Garry
>
> > Andreas is correct but the delimiter is <dtml-sql_delimiter>.
> > Probably better to do this in python script, but here are some code
> > snippets to help you:
> > In the form:
> >
> > <select name="seasons:list" multiple="multiple">
> >
> > In the ZSQL method:
> >
> > <dtml-in seasons>
> > <dtml-var sql_delimiter>
> >
> > insert statement goes here, don't forget the ; at the end.
> >
> > </dtml-in>
> >
> > Hope this is of some help, others may want to chip in with other
> > suggestions, such as doing the same in python. Anyway good luck.
> > Garry
> > _______________________________________________
> > Zope-DB mailing list
> > Zope-DB [at] zope
> > https://mail.zope.org/mailman/listinfo/zope-db
> >
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB [at] zope
> https://mail.zope.org/mailman/listinfo/zope-db
>

lukesh at seznam

Aug 24, 2010, 3:04 PM

Post #7 of 11 (2728 views)
Permalink

Re: ZSQL Question - Insert multiple rows in one statement? [In reply to]

----- Puvodní zpráva -----
Od: "Andreas Jung" <lists [at] zopyx>

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> I would assume that you can use DTML-IN for looping and generating
> multiple SQL statements of the same kind. You have to ensure that
> the statements having a proper delimiter (there was something in DTML?!
> DTML-SQLDELIMITER?...no idea, you need to checks the docs of this
> ancient technology).

DTML is mature, not ancient. For ZSQL is DTML the best one. For HTML, css,
javascripts too. Only for large XML trees the ZPT should be better.

Did you try to make many small dynamic javascript in one page with ZPT?
Yeah, python scripts calling, instead of placing small dtml code into
javascript in page...

ZPT is more academic platform, than for everyday usage.

> Mark Phillips wrote:
>> When I retrieve multiple values from a selection box, I need to create a
>> loop somewhere to insert the multiple values (rows) into a table. Is
>> there a way to do this within a ZSQL statement, or is it best to do the
>> looping in a Python script?

use this:

query1
<dtml-var sql_delimiter>
query2
<dtml-var sql_delimiter>
query3

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
https://mail.zope.org/mailman/listinfo/zope-db

shane at hathawaymix

Aug 24, 2010, 5:52 PM

Post #8 of 11 (2734 views)
Permalink

Re: ZSQL Question - Insert multiple rows in one statement? [In reply to]

On 08/24/2010 04:04 PM, Jaroslav Lukesh wrote:
> ZPT is more academic platform, than for everyday usage.

Wow, I have never heard that opinion before. I have had quite the
opposite experience: I find ZPT more productive and less error-prone
than all other web templating languages. I have a project that pulls in
several templating languages, due to dependencies, and ZPT is easily the
best and most practical of them all.

I should clarify that I use Chameleon and BFG, so expressions in
templates are in Python, and BFG's view machinery lets me put all
non-trivial template code in normal Python modules.

I suppose I would agree with the statement "Zope 2 page templates are
academic", since the full practical value of ZPT was not realized until
it was reimplemented a few times. (See Kid, Genshi, Chameleon, and
possibly others.)

Anyway, sorry for posting this to the wrong list.

Shane
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
https://mail.zope.org/mailman/listinfo/zope-db

lukesh at seznam

Aug 24, 2010, 11:13 PM

Post #9 of 11 (2733 views)
Permalink

Re: ZSQL Question - Insert multiple rows in one statement? [In reply to]

----- Puvodní zpráva -----
Od: "Shane Hathaway" <shane [at] hathawaymix>

> On 08/24/2010 04:04 PM, Jaroslav Lukesh wrote:
>> ZPT is more academic platform, than for everyday usage.
>
> Wow, I have never heard that opinion before. I have had quite the
> opposite experience: I find ZPT more productive and less error-prone
> than all other web templating languages. I have a project that pulls in
> several templating languages, due to dependencies, and ZPT is easily the
> best and most practical of them all.
>
> I should clarify that I use Chameleon and BFG, so expressions in
> templates are in Python, and BFG's view machinery lets me put all
> non-trivial template code in normal Python modules.
>
> I suppose I would agree with the statement "Zope 2 page templates are
> academic", since the full practical value of ZPT was not realized until
> it was reimplemented a few times. (See Kid, Genshi, Chameleon, and
> possibly others.)
>
> Anyway, sorry for posting this to the wrong list.
>
> Shane

Thanks for clarify, you are true, I use Zope 2 only. If Zope3 ZPT is so
good, then it is good news.

Regards, JL.

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
https://mail.zope.org/mailman/listinfo/zope-db

lists at zopyx

Aug 24, 2010, 11:19 PM

Post #10 of 11 (2735 views)
Permalink

Re: ZSQL Question - Insert multiple rows in one statement? [In reply to]

2010/8/25 Jaroslav Lukesh <lukesh [at] seznam>

>
> ----- Puvodní zpráva ----- Od: "Andreas Jung" <lists [at] zopyx>
>
>
>
> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> I would assume that you can use DTML-IN for looping and generating
>> multiple SQL statements of the same kind. You have to ensure that
>> the statements having a proper delimiter (there was something in DTML?!
>> DTML-SQLDELIMITER?...no idea, you need to checks the docs of this
>> ancient technology).
>>
>
> DTML is mature, not ancient. For ZSQL is DTML the best one.

For ZSQL methods perhaps - fortunately we moved on and we are having
higher-level
solutions like ORMs for implementing SQL connectivity in a more clean way.
Of course, use SQL and ZSQL methods if you need to...but from the
prospective
of software architecture we have better solutions in our toolbox.

>
>
> Did you try to make many small dynamic javascript in one page with ZPT?
> Yeah, python scripts calling, instead of placing small dtml code into
> javascript in page...
>
> ZPT is more academic platform, than for everyday usage.

Truly nonsense. ZPT is the adopted solution in the Zope universe for doing
templating - for a variety of reasons
(e.g. support for unicode, ZPT is maintained - DTML has not seen any changes
and fixes lately).

-aj

>

mal at egenix

Aug 26, 2010, 6:13 AM

Post #11 of 11 (2729 views)
Permalink

Re: ZSQL Question - Insert multiple rows in one statement? [In reply to]

Mark Phillips wrote:
> When I retrieve multiple values from a selection box, I need to create a
> loop somewhere to insert the multiple values (rows) into a table. Is there a
> way to do this within a ZSQL statement, or is it best to do the looping in a
> Python script?
>
> My specific example....
>
> table 1 (Players) has information about players (i.e. team members for a
> sports team). There is a primary key - playerID
> table 2 (Seasons) has information about each season - primary key is
> seasonID
> table 3 (PlayerSeasons) has two columns - playerID and seasonID.
>
> The form to crud a player has a combobox where one can select multiple
> seasons for a player. When I do an add or update, I have to add one or more
> rows to the PlayerSeasons table. Does this loop have to be in a Python
> script, or can it be implemented in ZSQL? I looked at the 'multiple'
> keyword, but all the examples indicate that it apples to sql tests, such as
> testing against a set of values. I can't seem to find any examples where one
> is inserting multiple rows into a table.

Other's have already shown how this can be done using DTML
and ZSQLMethods.

Here's a variant that will work with our mxODBC Zope DA
(it uses add-ons that we implemented to make processing
more efficient) and using an external method:

def bulk_insert():
request = self.REQUEST
response = request.response

# Get rows to insert from request (let's assume this returns
# a list of 2-tuples (playerID, seasonID))
data_rows = request....

# Get Zope connection object (assumes this is called sqlserver)
sqlserver = self.sqlserver

# Get a database connection (by calling the object)
connection = self.sqlserver()

# Insert data in one go
connection.executemany('insert into PlayerSeasons '
' (playerID, seasonID)'
' values (?,?)',
data_rows)

# Return a success message
return 'Done.'

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Aug 26 2010)
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::

eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
http://www.egenix.com/company/contact/
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
https://mail.zope.org/mailman/listinfo/zope-db

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads