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