multidimensional array to database in python

Question

I am trying to figure out a way to put a two dimensional array (using python) into an sqlite database. My array:

['hello', 'hello', 'hello'], ['hello', 'hello', 'hello']

What I want is for each tuple of 'hello's would a new row with each 'hello' being its own attribute. I'm not sure what I'm trying to do is even possible (I hope it is). I tried following a few other posts but I keep getting the error:

sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Does anyone know how to insert a multidimensional array into a sqlite database? Any help would be appreciated. Here is my code:

import sqlite3
array2d = [['hello' for x in xrange(3)] for x in xrange(3)] 
var_string = ', '.join('?' * len(array2d))
conn = sqlite3.connect('sample.db')
c = conn.cursor()
c.execute('''CREATE TABLE sample (Name TEXT, Line_1 TEXT, Line_2 TEXT)''')
query_string = 'INSERT INTO sample VALUES (%s);' % var_string
c.execute(query_string, array2d)

Answer 1

Use the cursor.executemany() method to insert a sequence of rows in one go:

query_string = 'INSERT INTO sample VALUES (?, ?, ?)'
c.executemany(query_string, array2d)
conn.commit()

Don't forget to conn.commit() the transaction.

I've not bothered with formatting the SQL parameters here, for demonstration purposes; you don't really want to do so here anyway, as your number of columns is fixed at well (from the CREATE TABLE definition).

• David: Thanks for response. I got rid of the error message but I when look into sample.db using SQLite Database Browser the data fields are empty.
• Martijn Pieters: You need to commit as well. :-)