Serialising a list into SQLite

Question

I am working with over 29 million elements, so thought a database would make more sense than an array.

Previously I was passing elements one at a time to the execute function, but I believe passing an array of 100,000 elements at a time to the executemany function would be more efficient.

I have shortened my 180 odd line code into this short test-case:

import sqlite3

if __name__ == '__main__':
    connection = sqlite3.connect('array.db')
    cursor = connection.cursor()
    cursor.execute("create table array (word text);")
    cursor.executemany("insert into array values (?)", [u'usa', u'sharp', u'rise', u'seen', u'in', u'cd', u'bootlegs', u'sharp', u'rise', u'seen', u'in', u'cd', u'bootlegs', u'los', u'angeles'])
    connection.commit()
    cursor.execute("select * from array;")
    print cursor.fetchall()

Output:

Traceback (most recent call last):
        cursor.executemany("insert into array values (?)", [u'usa', u'sharp', u'rise', u'seen', u'in', u'cd', u'bootlegs', u'sharp', u'rise', u'seen', u'in', u'cd', u'bootlegs', u'los', u'angeles'])
    sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.

What am I doing wrong?

mechanical_meat · Answer 1 · 2012-06-10T18:36:08.970

When using .executemany() you must provide a sequence of tuples (or lists).
So all you need to do is wrap each word into a tuple (or list).
Example:

cursor.executemany("insert into array values (?)", 
                   [(u'usa',), (u'sharp',), (u'rise',)])

(In case the above is not clear, the 2nd argument is now a list of one-element tuples.)

When you think about how .execute() works, this behavior makes sense since .execute() also requires that the parameters be in a tuple (or list).
So this does not work:

cursor.execute("insert into array values (?)", some_word)

But this does:

cursor.execute("insert into array values (?)", (some_word,))

score 1 · Accepted Answer · answered Jun 10 '12 at 18:30

Ahh, they need to be in their own elements:

import sqlite3

if __name__ == '__main__':
    connection = sqlite3.connect('array.db')
    cursor = connection.cursor()
    cursor.execute("create table array (word text);")
    cursor.executemany("insert into array values (?)", [[u'nabisco'],[u'usa'], [u'sharp'], [u'rise']])
    connection.commit()
    cursor.execute("select * from array;")
    print cursor.fetchall()

Serialising a list into SQLite

2 Answers2