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)

Martijn Pieters · Accepted Answer · 2014-02-17 21:44:43Z

2

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).

edited Feb 17, 2014 at 21:44

answered Feb 17, 2014 at 21:35

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

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 Over a year ago

You need to commit as well. :-)

Collectives™ on Stack Overflow

multidimensional array to database in python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related