How to add multiple Columns into Sqlite3 from a for loop in Python

Question

Admittedly I a still very new to both Python and Sqlite3, and I am attempting to add the contents of two lists into a database so that one list is in the first column and the second list shows up in the second column. To this point, I have been unsuccessful. I am defenitely making a fundamental error, and the error message that I get is this: "sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type."

my code is this:

import sqlite3
names = ['Tom', 'Dick', 'Harry']

ids = ['A452', 'B698', 'Kd9f']


conn = sqlite3.connect('testforinput.db')
c = conn.cursor()

c.execute("CREATE TABLE thetable(name TEXT, id TEXT)")

index = 0
for link in names:
    idofperson = ids[index]
    c.execute("INSERT INTO thetable(name, id)VALUES(?, ?)", ( [link], idofperson ))
    index+=1
conn.commit()

conn.close()

The error occurs because of the for loop specifically the "idofperson" variable

The desired outcome is that I would like to have two columns created in sql one being name and the other being id.

Any help would be greatly appreciated.

Artiel · Answer 1 · 2016-11-18T03:39:42.330

1

I think you just change

index =0
for link in names:
idofperson = ids[index]
c.execute("INSERT INTO thetable(name, id)VALUES(?, ?)", ( [link], idofperson ))

to this (use enumrate and change [list] to list, because you pass a list into a column need TEXT type):

for index, link in enumrable(names):
idofperson = ids[index]
c.execute("INSERT INTO thetable(name, id)VALUES(?, ?)", ( link, idofperson ))

edited Nov 18 '16 at 03:39

answered Nov 18 '16 at 03:18

Artiel

136
6

1

I'll try that now and let you know if it works. Thanks for the input – Kevin Nov 18 '16 at 03:21
1

Shoot! no luck! but ill do some further research on enumerate and see if that is the key to it all. Thanks again – Kevin Nov 18 '16 at 03:25
No, I error not there. Your error is type because you pass a 'list' parameter into a column need type TEXT – Artiel Nov 18 '16 at 03:29

score 0 · Answer 2 · answered Nov 18 '16 at 03:17

0

your variable index is not increasing.try using the enumerate on for loop. or just add index += 1 after execute

answered Nov 18 '16 at 03:17

Randy Arguelles

225
1
9

Thanks bro forgot about that, I added input+=1 after the execute statement but I still get the same error :( – Kevin Nov 18 '16 at 03:19
can you try to remove the extra parenthesis – Randy Arguelles Nov 18 '16 at 03:29
c.execute("INSERT INTO thetable(name, id)VALUES(?, ?)", [link], idofperson ) – Randy Arguelles Nov 18 '16 at 03:29

score 0 · Answer 3 · answered Feb 06 '18 at 08:34

the error is occurring because of the unsupported data type you are trying to push in, you can't store list as it is, you need to change to another supported data types, i like this solution ....it worked for me https://stackoverflow.com/a/18622264/6180263

score -1 · Answer 4 · answered Nov 18 '16 at 03:56

for your problem, try this:

import sqlite3

names = ['Tom', 'Dick', 'Harry']
ids = ['A452', 'B698', 'Kd9f']
data = zip(names, ids)
conn = sqlite3.connect('testforinput.db')
c = conn.cursor()
c.execute("CREATE TABLE thetable(name TEXT, id TEXT)")


for d in data:
    sql = "INSERT INTO thetable (name, id) VALUES ('%s', '%s'); " % d
    c.execute(sql)

conn.commit()
conn.close()

I suggest change data to a list of dict, like this [{'name':'Tom', 'id': 'A452'}, {'name':'dick', 'id':'B698'}..] and you can generate insert sql by data, this make the insert more flexible.

How to add multiple Columns into Sqlite3 from a for loop in Python

4 Answers4