[Zope-DB] ZSQL Question - Insert multiple rows in one statement?

[M.-A. Lemburg]

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/