CODEWITHSUNDEEP
pythonsqlitesqlalchemy
dooms
dooms

Reputation: 1645

sqlalchemy date functions missing

I'm using sqlalchemy with sqlite and python.

dataset.to_sql('data', disk_engine, if_exists='replace')
def query(string):
    return pd.read_sql_query(string, disk_engine)

Dataset is a pandas dataframe loaded from a csv file.

 date = "select SUBSTR(Date,1) from data LIMIT 1"
 print query(date)

                SUBSTR(Date,1)
0  2014-08-08 00:00:00.000000

So the SUBSTR() method does work, but when I try this :

date = "select YEAR(Date) from data LIMIT 1"
print query(date)

OperationalError: (sqlite3.OperationalError) no such function: YEAR [SQL: 'select YEAR(Date) from data LIMIT 1']

I get this error.

I didn't import SUBSTR so why the SUBSTR() method is recognized and not any Date functions ?

Upvotes: 1

Views: 1662

Answers (1)

Busturdust
Busturdust

Reputation: 2485

I don't believe Year() Is a function in sqlite.

You should use strftime Instead.

Something like select strftime('%Y',date) from data limit 1

W3 Resource on strftime. http://www.w3resource.com/sqlite/sqlite-strftime.php

Upvotes: 2

Related Questions