CODEWITHSUNDEEP
python-3.xpandassqlalchemy
sharp
sharp

Reputation: 2158

sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite Date type only accepts Python date objects as input

I have pandas data frame that contains Month and Year values in a yyyy-mm format. I am using pd.to_sql to set the data type value to sent it to .db file.

I keep getting error: sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite Date type only accepts Python date objects as input.

Is there a way to set 'Date' Data type for 'MonthYear' (yyyy-mm) column? Or it should be set in a VARCHAR? I tried changing it to different types pandas's datetime data type, none of them seem to work.

I don't have any issues with 'full_date', it assigns it properly. Data type for 'full_date' is datetime64[ns] in pandas.

MonthYear full_date
2015-03   2012-03-11
2015-04   2013-08-19
2010-12   2012-06-29
2012-01   2018-01-01


df.to_sql('MY_TABLE', con=some_connection,
dtype={'MonthYear':sqlalchemy.types.Date(),
       'full_date':sqlalchemy.types.Date()})

Upvotes: 0

Views: 472

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169274

My opinion is that you shouldn't store unnecessarily the extra column in your database when you can derive it from the 'full_date' column.

One issue you'll run into is that SQLite doesn't have a DATE type. So, you need to parse the dates upon extraction with your query. Full example:

import datetime as dt
import numpy as np
import pandas as pd
import sqlite3

# I'm using datetime64[ns] because that's what you say you have
df = pd.DataFrame({'full_date': [np.datetime64('2012-03-11')]})

con = sqlite3.connect(":memory:") 
df.to_sql("MY_TABLE", con, index=False) 

new_df = pd.read_sql_query("SELECT * FROM MY_TABLE;", con, 
                           parse_dates={'full_date':'%Y-%m-%d'})

Result:

In [111]: new_df['YearMonth'] = new_df['full_date'].dt.strftime('%Y-%m')

In [112]: new_df
Out[112]: 
   full_date YearMonth
0 2012-03-11   2012-03

Upvotes: 1

Related Questions