Inserting rows of varying lengths into SQLite3 Database with Python

userWEVk1 0
Viewed 42

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.
sqlite python
1 Answers

Your sublists are being unpacked, hence the error. Since Sqlite doesn't have a "list" type your best option is probably to convert your sublists into comma separated strings:

list_of_scene_char_strings = [','.join(sub) for sub in list_of_scene_char_list]
gimix0
Related