deluxenathan deluxenathan - 6 months ago 21
Python Question

Python Insert data from list into SQlite3

I am trying to insert a list with data into a .db file.

Below is the layout of the db file.

Rank is an INTEGER

Description is TEXT

db layout

I have the following Python code below and SQlite query,

I am getting the error:

Exception in Tkinter callback
Traceback (most recent call last):
File "C:\Python27\lib\lib-tk\Tkinter.py", line 1536, in __call__
return self.func(*args)
line 136, in DB_Entry
top_ten.execute('INSERT INTO Top_Ten VALUES(?,?)', [range(1,11),SMH_LADDER[:10]],)
InterfaceError: Error binding parameter 0 - probably unsupported type.


Below is the python code:

def DB_Entry():
# Create a connection to the database.
connection = connect(database = "top_ten.db")

# Get a cursor on the database. This allows you to execute SQL
top_ten = connection.cursor()

top_ten.execute('INSERT INTO Top_Ten VALUES(?,?)', [range(1,11),SMH_LADDER[:10]],)

# Commit the changes to the database
connection.commit()

# Close the cursor.
top_ten.close()

# Close the database connection.
connection.close()


I am trying to put the contents of SMH_LADDER[:10] which are strings, and a number from the range(1,11) into db, but cannot get past this error message!

Below is the format of the list SMH_LADDER[:10]

['String1', 'String2', 'String3', 'String4', 'String5', 'String6', 'String7', 'String8', 'String9', 'String10']

Any help would be appreciated!

Answer

You can't just insert two lists like that. You need to create a series of INSERT statements that inserts a pair from each list, one pair at a time. You can create the pairs with zip, then use executemany to do the inserts.

values = zip(range(1,11), SMH_LADDER[:10])
top_ten.executemany('INSERT INTO Top_Ten VALUES(?,?)', values)
Comments