Re: [pysqlite] How To Insert A Variable Number of Values

On Fri, 30 May 2008, Jeff Peck wrote:

>   I can help you solve this problem. I'd recommend that you create a
> method on your grid class that will return a tuple for a given row. You
> can probably implement this in a more clever manner, but it would roughly
> look like this:

Jeff/Adrian/Roger, et al.:

   All of you had excellent suggestions that helped me solve the problem. In
addition to leaving off the list of values from each row of the grid, I
needed to accommodate the empty cells. And I knew that would have a generic,
scalable solution rather than one line for each column in the table.

   Using winpdb I saw how the empty cells are seen, so I could properly
identify them. I realized that regardless of whether I used names in a
dictionary or question marks, I had to address the issue of empty cells
where the SQL statement expected to see something for each value in the
table.

   The working method is this:

   def OnSave(self, event):
     stmt = """INSERT or REPLACE into Data (comp, subcomp, var, curr1, curr2,
 					   curr3, curr4, curr5, curr6, curr7,
                                            curr8, curr9, curr10, curr11,
                                            curr12, noact, alt2, alt3, alt4,
                                            alt5, alt6, alt7, alt8, alt9, alt10,
                                            alt11, alt12, alt13, alt14, alt15,
                                            alt16, alt17, alt18) values (?,?,?,
 					   ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                                            ?,?,?,?,?,?,?,?,?,?,?,?,?)"""
     for r in range(self.nRows):
       row = []
       for c in range(33):
         if self.dataGrid.GetCellValue(r,c) == None:
           row.append('')
         else:
           row.append(self.dataGrid.GetCellValue(r,c))
       self.appData.cur.execute(stmt,row)
     self.appData.con.commit()

   This works just fine.

Thank you all very much,

Rich

-- 
Richard B. Shepard, Ph.D.               |  Integrity            Credibility
Applied Ecosystem Services, Inc.        |            Innovation
<http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863