Error in python showing : Error binding parameter 0 - probably unsupported type

Question

I cant to figure out what I am missing in this code.

import pandas as pd
import sqlite3

conn = sqlite3.connect('test.sqlite')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS testing')
cur.execute('CREATE TABLE testing (Name TEXT, Tutorial TEXT, Datetime Text, Duration TEXT)')

url='source.csv'
data = pd.read_csv(url,sep=",") # use sep="," for coma separation. 
print(data)
 
cur.execute('INSERT INTO testing (Name, Tutorial, Datetime, Duration) VALUES (?, ?, ? ,? )',
            (data['Name'], data['Tutorial'], data['Datetime'], data['Duration'] ))

conn.commit()

Answer 1

It is because you are passing in a a pandas Series object instead of a list of tuples as parameter. cursor.execute expects a list of tuples as parameter. You have to first convert the data frame into a list of tuples. You can use the itertuples function:

data_tuples = list(data.itertuples(index=False, name=None))
cur.execute('INSERT INTO testing (Name, Tutorial, Datetime, Duration) VALUES (?, ?, ? ,? )', data_tuples)

Or you can simply use the DataFrame.to_sql() function:

data.to_sql('testing', conn)

Answer 2

Using the Pandas library to load the CSV file is overkill and the cause of your issue. csv is part of the standard library and is better suited to your use case.

import csv
import sqlite3
import os

DB_FILE = os.path.join(os.getcwd(), "db_filename.db")
CSV_FILE = os.path.join(os.getcwd(), "source.csv")

con = sqlite3.connect(DB_FILE)
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS testing;")
cur.execute("CREATE TABLE testing (Name TEXT, Tutorial TEXT, Datetime TEXT, Duration TEXT);")

with open(CSV_FILE,"r") as f:
    csv_data = csv.DictReader(f)
    for_db = [(r["Name"], r["Tutorial"], r["Datetime"], r["Duration"]) for r in csv_data]

cur.executemany("INSERT INTO testing (Name, Tutorial, Datetime, Duration) VALUES (?, ?, ?, ?);", for_db)
con.commit()
con.close()