I have a pandas dataframe with the following data types
var1 object
var2 datetime64[ns]
var3 object
var4 object
var5 int64
var6 float64
my schema in the sqlite3 data base is
CREATE TABLE IF NOT EXISTS "table_name" (
"var1" TEXT,
"var2" DATETIME,
"var3" TEXT,
"var4" TEXT,
"var5" INT,
"var6" REAL
);
my query in python looks lite this
query = 'insert into first_north4 (var1, var2, var3, var4, var5, var6) values (?, ?, ?, ?, ?, ?)'
values = [tuple(x) for x in df.values]
cur.executemany(query, values)
When executing the query I get this error msg
sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.
This is the datetime that fails, I can't figure out why
I found a working solution for me (regarding date object), hopefully this will help somebody else in the future. Fully working example below in python3
import pandas as pd
import datetime as dt
import sqlite3
# est conn (creates db if not exist)
db = 'db_test.db'
conn=sqlite3.connect(db,
detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cur = conn.cursor()
# create table
query='CREATE TABLE IF NOT EXISTS test(i INT, f REAL, d DATE)'
cur.execute(query)
# some specific datatypes to dataframe
i = [4 ,2 ,44]
f=[1.23,123.2,2.2222]
d = [dt.date.today(),dt.date.today(),dt.date.today()]
df = pd.DataFrame(data=[i,f,d],index = ['i','f','d']).T
print(df)
print(df.dtypes)
print(type(df['i'].values[0]))
print(type(df['f'].values[0]))
print(type(df['d'].values[0]))
# insert
query = 'insert into test (i, f, d) values (? ,?, ?)'
values = [tuple(x) for x in df.values]
print(values)
cur.executemany(query, values)
conn.commit()
# test types when querying the db
query = cur.execute('SELECT * from test')
cols = [column[0] for column in query.description]
data = pd.DataFrame.from_records(data=query.fetchall(), columns=cols)
print(data)
print(data.dtypes)
print(type(data['i'].values[0]))
print(type(data['f'].values[0]))
print(type(data['d'].values[0]))
# close conn
cur.close()
conn.close()