How to insert a list of lists into a table? [Python]

Question

I have this list with column names:

csvfields = ['id', 'gen', 'age', 'mar', 'loc', 'inc', 'iscr', 'escr']

And data list with lists:

csvdata = [['51', 'F', '46', 'M', '0', '15100', '531', '555'], 
['52', 'M', '29', 'M', '2', '14200', '673', '633'], 
['53', 'M', '25', 'S', '0', '22200', '742', '998'], 
['54', 'M', '36', 'M', '2', '1000', '677', '646'], 
['55', 'F', '99', 'S', '0', '10600', '608', '998'], 
['56', 'F', '45', 'M', '2', '6100', '710', '743'], 
['57', 'M', '99', 'M', '2', '16500', '679', '646'], 
['58', 'F', '37', 'M', '0', '7400', '637', '683'], 
['59', 'M', '45', 'S', '0', '22800', '683', '998'], 
['60', 'M', '22', 'S', '0', '6400', '699', '998']]

I created a function that creates a table with the csvfields al columns:

def create_dbtb(dbname, tablename):
    conn = sqlite3.connect(dbname)
    curs = conn.cursor()
    columns =  ', '.join(csvfields)
    curs.execute('''CREATE TABLE IF NOT EXISTS {}({})'''.format(tablename, columns))
    conn.commit()
    conn.close()

But now I want to create another function that inserts the data in csvdata into the created table. I created the next function but it's just not working and I don't know how to fix this.

def fill_dbtb(self):
    conn = sqlite3.connect('data.db')
    curs = conn.cursor()
    columns = ', '.join(csvfields)
    data = ', '.join([row for row in csvdata])
    curs.execute('''INSERT INTO data {} VALUES ({})'''.format(columns, data))

Anyone who knows how to fix this?

BTW, this is the error I get after running the second function:

Traceback (most recent call last):
  File "<pyshell#1>", line 1, in <module>
    x.fill_dbtb()
  File "/Users/dirkkoomen/Desktop/Python cursus/inzendopgave 4/opgave 6/oefnen.py", line 48, in fill_dbtb
    curs.execute('''INSERT INTO data ({}) VALUES ({})'''.format(', '.join(self.csvfields), ', '.join([row for row in self.csvdata])))
TypeError: sequence item 0: expected str instance, list found

Possible duplicate of [How do I use prepared statements for inserting MULTIPLE records in SQlite using Python / Django?](https://stackoverflow.com/questions/5616895/how-do-i-use-prepared-statements-for-inserting-multiple-records-in-sqlite-using) — ChrisGPT was on strike, Jul 24 '18 at 16:29
Have you tried using list comprehension to iterate through the nested list and add it to the table — Clint, Jul 24 '18 at 16:29
@Clint a better method would just be to use `executemany` rather than looping in python. — roganjosh, Jul 24 '18 at 16:41
You should not be using `format()` to supply parameters to your query, this is open to SOL injection. You should look into parameterization to find the proper way to do this. — roganjosh, Jul 24 '18 at 16:44
You have asked numerous questions over the past few days that suggest you might be well served by finding a course that covers Python basics, input/output, and database interactions, rather than running to SO every time you get stuck. Have you read any of the python documentation on the topics that you're working on or worked through any python tutorials? — i alarmed alien, Jul 24 '18 at 16:53
@roganjosh yea you are correct, I was just trying to give him something quick and easy — Clint, Jul 24 '18 at 17:16

score 0 · Answer 1 · answered Jul 24 '18 at 19:35

Finally found a way to fix it, don't know if it's the best way?

def create_dbtb(dbname, tablename):
    conn = sqlite3.connect(dbname)
    curs = conn.cursor()
    columns =  ', '.join(csvfields)
    curs.execute('''DROP TABLE IF EXISTS {}'''.format(tablename))
    curs.execute('''CREATE TABLE {}({})'''.format(tablename, columns))
    conn.commit()
    conn.close()

def fill_dbtb():
    conn = sqlite3.connect('data.db')
    curs = conn.cursor()
    csvdata_new = [tuple(l) for l in csvdata]
    fields = tuple(csvfields)
    for item in csvdata_new:
        var_string = ', '.join('?'*len(item))
        curs.executemany('''INSERT INTO data {} VALUES ({})'''.format(fields, var_string), (item,))
    conn.commit()
    conn.close()

This works fine, let me know if there's a better way.

See @roganjosh's comment: "You should not be using format() to supply parameters to your query, this is open to SQL injection. You should look into parameterization to find the proper way to do this." — i alarmed alien, Jul 24 '18 at 21:33

How to insert a list of lists into a table? [Python]

1 Answers1