insert datetime in sqlite3 database error
up vote
0
down vote
favorite
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
python sqlite3 sqldatatypes
asked Nov 22 at 13:56
Sebastian Edman
34
add a comment |
up vote
0
down vote
favorite
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
python sqlite3 sqldatatypes
asked Nov 22 at 13:56
Sebastian Edman
34
1
Where isvaluesbeing defined in your Python code?
– Tim Biegeleisen
Nov 22 at 13:57
Please show a single sublist ofvaluesas a starting point
– roganjosh
Nov 22 at 14:01
As requested. As a note, the code is fully functional. For instance if i just let all variables in the dataframe be strings it works.
– Sebastian Edman
Nov 23 at 12:43
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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
python sqlite3 sqldatatypes
asked Nov 22 at 13:56
Sebastian Edman
34
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
python sqlite3 sqldatatypes
python sqlite3 sqldatatypes
asked Nov 22 at 13:56
Sebastian Edman
34
asked Nov 22 at 13:56
Sebastian Edman
34
edited Nov 22 at 14:20
asked Nov 22 at 13:56
Sebastian Edman
34
asked Nov 22 at 13:56
Sebastian Edman
34
asked Nov 22 at 13:56
Sebastian Edman
34
34
1
Where isvaluesbeing defined in your Python code?
– Tim Biegeleisen
Nov 22 at 13:57
Please show a single sublist ofvaluesas a starting point
– roganjosh
Nov 22 at 14:01
As requested. As a note, the code is fully functional. For instance if i just let all variables in the dataframe be strings it works.
– Sebastian Edman
Nov 23 at 12:43
add a comment |
1
Where isvaluesbeing defined in your Python code?
– Tim Biegeleisen
Nov 22 at 13:57
Please show a single sublist ofvaluesas a starting point
– roganjosh
Nov 22 at 14:01
As requested. As a note, the code is fully functional. For instance if i just let all variables in the dataframe be strings it works.
– Sebastian Edman
Nov 23 at 12:43
1
1
Where is
values being defined in your Python code?– Tim Biegeleisen
Nov 22 at 13:57
Where is
values being defined in your Python code?– Tim Biegeleisen
Nov 22 at 13:57
Please show a single sublist of
values as a starting point– roganjosh
Nov 22 at 14:01
Please show a single sublist of
values as a starting point– roganjosh
Nov 22 at 14:01
As requested. As a note, the code is fully functional. For instance if i just let all variables in the dataframe be strings it works.
– Sebastian Edman
Nov 23 at 12:43
As requested. As a note, the code is fully functional. For instance if i just let all variables in the dataframe be strings it works.
– Sebastian Edman
Nov 23 at 12:43
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
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.
answered Nov 24 at 3:13
DinoCoderSaurus
55128
Thank you for your answer, unfortunately I can't upvote yes (new user). But i will come back and do it when I have enough rep. I solved the problem by just feeding the database with text and to the casting afterwards when querying the data
– Sebastian Edman
Nov 28 at 18:50
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
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.
answered Nov 24 at 3:13
DinoCoderSaurus
55128
Thank you for your answer, unfortunately I can't upvote yes (new user). But i will come back and do it when I have enough rep. I solved the problem by just feeding the database with text and to the casting afterwards when querying the data
– Sebastian Edman
Nov 28 at 18:50
add a comment |
up vote
0
down vote
accepted
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.
answered Nov 24 at 3:13
DinoCoderSaurus
55128
Thank you for your answer, unfortunately I can't upvote yes (new user). But i will come back and do it when I have enough rep. I solved the problem by just feeding the database with text and to the casting afterwards when querying the data
– Sebastian Edman
Nov 28 at 18:50
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
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.
answered Nov 24 at 3:13
DinoCoderSaurus
55128
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.
answered Nov 24 at 3:13
DinoCoderSaurus
55128
answered Nov 24 at 3:13
DinoCoderSaurus
55128
answered Nov 24 at 3:13
DinoCoderSaurus
55128
answered Nov 24 at 3:13
DinoCoderSaurus
55128
55128
Thank you for your answer, unfortunately I can't upvote yes (new user). But i will come back and do it when I have enough rep. I solved the problem by just feeding the database with text and to the casting afterwards when querying the data
– Sebastian Edman
Nov 28 at 18:50
add a comment |
Thank you for your answer, unfortunately I can't upvote yes (new user). But i will come back and do it when I have enough rep. I solved the problem by just feeding the database with text and to the casting afterwards when querying the data
– Sebastian Edman
Nov 28 at 18:50
Thank you for your answer, unfortunately I can't upvote yes (new user). But i will come back and do it when I have enough rep. I solved the problem by just feeding the database with text and to the casting afterwards when querying the data
– Sebastian Edman
Nov 28 at 18:50
Thank you for your answer, unfortunately I can't upvote yes (new user). But i will come back and do it when I have enough rep. I solved the problem by just feeding the database with text and to the casting afterwards when querying the data
– Sebastian Edman
Nov 28 at 18:50
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53432560%2finsert-datetime-in-sqlite3-database-error%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Where is
valuesbeing defined in your Python code?– Tim Biegeleisen
Nov 22 at 13:57
Please show a single sublist of
valuesas a starting point– roganjosh
Nov 22 at 14:01
As requested. As a note, the code is fully functional. For instance if i just let all variables in the dataframe be strings it works.
– Sebastian Edman
Nov 23 at 12:43