Qfyilyi

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

From the sqlite datatypes doc:

2.2. Date and Time Datatype

SQLite does not have a storage class set aside for storing dates
and/or times. Instead, the built-in Date And Time Functions of SQLite
are capable of storing dates and times as TEXT, REAL, or INTEGER
values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").


• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic
  
  Gregorian calendar.


• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these
formats and freely convert between formats using the built-in date and
time functions.

One option would be to convert var2 to a string before db insert (thus preserving the DATETIME datatype in the database) as described here. There are other options, and a search on this forum for "datetime64 sqlite" should provide other approaches.