extract year from datetime in python sqlalchemy query-CodePudding

I have a datetime column that I would like to extract the year:

I tried the following:

pd.read_sql_query('SELECT FORMAT(visits.visit_date, 'YYYY') as year FROM visits', engine)

pd.read_sql_query('SELECT FORMAT(visits.visit_date, 'yyyy') as year FROM visits', engine)

pd.read_sql_query('SELECT extract(year, visits.visit_date) as year FROM visits', engine)

pd.read_sql_query('SELECT filter(extract('year', visits.visit_date) as year FROM visits', engine)

pd.read_sql_query('SELECT FORMAT(visits.visit_date, 'year') as year FROM visits', engine)

nothing seemed to work. visit_date is in a datetime format.

I feel something is missing or maybe formatting issues, and no luck in finding the right google.

I am getting SyntaxError: invalid syntax

CodePudding user response：

put the query in ''' query ''' (three quotes). The code is read and the single pair of quotes is read as a another within the query.

instead of single quotes

'SELECT strftime('%Y', datetime) from table'

use pair of triple quotes

'''SELECT strftime('%Y', datetime) from table'''