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

On Friday 30 May 2008 7:54 pm, Rich Shepard wrote:
>    In my application's database there is a table named Data which can have
> up to 33 columns. These values are displayed using a wx.grid.Grid widget
> (looks like a spreadsheet). When I want to save the values entered in the
> grid, I use this method:
>
>    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):
>        rowList = []
>        for c in range(self.nCols):
>          rowList.append(self.dataGrid.GetCellValue(r,c))
>        self.appData.cur.execute(stmt)
>      self.appData.cur.commit()
>
>    When the grid widget is first initiated, there are values for only the
> first three columns. As more data are entered, there will be more columns
> with values to be saved, but not necessarily the same number of columns for
> each row.
>
>    Testing this method results in the following error:
>
>    File "/data1/eikos/dataPage.py", line 168, in OnSave
>      self.appData.cur.execute(stmt)
> pysqlite2.dbapi2.ProgrammingError: Incorrect number of bindings supplied.
> The current statement uses 33, and there are 0 supplied.
There are no values supplied in the statement below:
self.appData.cur.execute(stmt)
Should be something along lines of self.appData.cur.execute(stmt,rowList).
This will get you past the no bindings error. For the variable length 
statement try using named paramstyle. This uses a dictionary to hold the 
bindings. See:
http://oss.itsystementwicklung.de/download/pysqlite/doc/usage-guide.html#python-database-api-2-0-compliance

>
>    Now there should be 3 values for each row at this stage. How can I write
> the SQL stmt so that the row/column cells with values are written to the
> database table? For example, the first row in rowList is: [u'Aesthetics',
> u'', u'AmbientLight'] and these values represent comp, subcomp (blank in
> this row), and var.
>
> Rich

-- 
Adrian Klaver
[email protected]