Python lists to sqlite

Question

I have about 100 lists with all with various amounts of information in them, but the first line is a comment describing the contents and the following lines being the items. Here's an example:

examplelist = [
#name, description, status
('buick', 'SUV', 'broken'),
('dodge', 'truck', 'working')]

The issue is that not all the lists have the same amount of information, nor are they all related/relatable. Some may have 3 values per entry, some may have 6, some 10. What I'd like to do is develop a process that will add a table to the sqlite database, create the columns then populate them with the records that are in the lists.

This is what I have so far:

import sqlite3

def insertRecords(tblname, fields, List):
    try:
        sqliteConnection = sqlite3.connect('db_name.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

    make_tbl = """CREATE TABLE IF NOT EXISTS 
                          %s(id INTEGER PRIMARY KEY, 
                                 field1 TEXT NOT NULL, 
                                 field2 TEXT NOT NULL,
                                 field3 TEXT NOT NULL
                                 field4 TEXT NOT NULL
                                 field5 TEXT NOT NULL
                                 field6 TEXT NOT NULL)""" %tblname
    cursor.execute(make_tbl)

    sqlite_insert_query = """INSERT INTO %s
                      (id, field1, field2, field3, field4) 
                      VALUES (?, ?, ?, ?, ?);""" %tblname

    cursor.executemany(sqlite_insert_query, List)
    sqliteConnection.commit()

    cursor.close()

except sqlite3.Error as error:
    print("Failed to insert records into table", error)
finally:
    if (sqliteConnection):
        sqliteConnection.close()
        print("The SQLite connection is closed")


my_list = []

insertRecords(my_list)

My thought was to create a function that would allow me to input a table name, the fields, and the list. Where I get stuck is determining how to:

1. Assign the field names (using the values from the comment would be ideal, if that's even possible)
2. Only create as many fields as there are item descriptors

I don't need to worry about the field types - they are all text.

What would be the best way to accomplish the above?

question from:https://stackoverflow.com/questions/65907732/python-lists-to-sqlite

Answer 1

Just guessing that the problem you are trying to solve is that you don't know how many fields there will be any any given list of records.

To dynamically create the fields part of the CREATE TABLE statement:

• Figure out how many fields there are

   >>> examplelist = [
   ... #name, description, status
   ... ('buick', 'SUV', 'broken'),
   ... ('dodge', 'truck', 'working')]
  
   >>> ncols = len(examplelist[0])
  

• Then use string formatting to produce the string - this uses f-strings

   >>> field_statement = ','.join(f'field{n} TEXT NOT NULL' for n in range(ncols))
  
   >>> print(field_statement)
   field0 TEXT NOT NULL,field1 TEXT NOT NULL,field2 TEXT NOT NULL