SQLite User Forum

Inserting rows of varying lengths into SQLite3 Database with Python
Login

Inserting rows of varying lengths into SQLite3 Database with Python

(1) By anonymous on 2022-09-07 15:27:04 [link] [source]

I have a list of lists and each nested list is of varying length with a minimum length of zero and an undetermined maximum length. I am trying to insert each of these lists into a single column in an SQLite3 table and am getting the error "Incorrect number of bindings supplied." How do I get this data into SQLite3?

# LIST OF LISTS TO INSERT INTO DATABASE
list_of_scene_char_lists = [['HARPER', 'RORY', 'THOMAS'], ['AUGUST', 'THOMAS']]

# FUNCTION: CREATE THE CHARACTERS TABLE  # DO ONLY ONCE
def create_characters_table():
    conn = sqlite3.connect(project_db)
    c = conn.cursor()
    c.execute("""CREATE TABLE characters (
        scene_characters text
        )""")
    conn.commit()
    conn.close()
#create_characters_table()  # DO ONLY ONCE

# FUNCTION: INSERT CHARACTERS INTO CHARACTERS TABLE  # DO ONLY ONCE
def auto_characters(list_of_scene_char_lists):
    conn = sqlite3.connect(project_db)
    c = conn.cursor()
    c.executemany("INSERT INTO characters VALUES (?)", (list_of_scene_char_lists))
    conn.commit()
    conn.close()
auto_characters(list_of_scene_char_lists)  # DO ONLY ONCE

# ERROR
c.executemany("INSERT INTO characters VALUES (?)", (list_of_scene_char_lists))
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.

(2) By David Raymond (dvdraymond) on 2022-09-07 16:51:09 in reply to 1 [link] [source]

If they're all going into a table with 1 field then it'd be something like

for subList in list_of_scene_char_lists:
    for item in subList:
        c.execute("INSERT INTO characters VALUES (?);", (item,))
conn.commit()

(3) By anonymous on 2022-09-07 18:12:40 in reply to 1 [link] [source]

SQLite knows nothing about lists. It only stores 64-bit integers, 64-bit floating-point numbers, texts, and blobs. If you want a single column to represent a whole list, you have to encode it somehow.

(4.1) By Simon Willison (simonw) on 2022-09-07 21:46:30 edited from 4.0 in reply to 1 [source]

If you encode those lists as JSON you can insert them into a SQLite TEXT column like the one you have here.

You can then run queries against them using SQLite's JSON functions: https://sqlite.org/json1.html

You code would end up looking something like this:

c.executemany("INSERT INTO characters VALUES (?)", [
    json.dumps(char_list) for char_list in list_of_scene_char_lists
])