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@zope.org
https://mail.zope.org/mailman/listinfo/zope-db
