How to convert and add a date while quering through to SQL via python

Question

Querying to sql connection through Python, but receiving below error. However the same code worked when connecting via excel > to azure sql

Code:

DF=pd.read_sql('SELECT CONVERT(VARCHAR(MAX), candidate,2) AS candidate,CONVERT(datetime,'01-'+Month,23) AS Month  FROM [workspace].[abctbldata]',conn)

DF

OUTPUT: ERROR:

SyntaxError: leading zeros in decimal integer literals are not permitted; use an 0o prefix for 
octal integers

@snakecharmerb I tried "01" also , but would receive another error, so unsure if this was the correct way.Tried CONVERT(datetime,"01-"+Month,23) Error after changing : Execution failed on sql: SELECT CONVERT(VARCHAR(MAX), candidate,2) AS candidate ,CONVERT(datetime,"01-"+Month,23) AS Month [workspace].[abctbldata] ('42S22', "[42S22] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid column name '01-'. (207) (SQLExecDirectW)") unable to rollback — technical, Mar 03 '23 at 09:13
Use parameter substitution as described in the linked duplicate - that's the best way to avoid quoting problems. The substitution method to use will depend on what `conn` is: `%s` if it's pure pyodbc, possibly `:some_name` if it's a sqlalchemy engine. — snakecharmerb, Mar 03 '23 at 09:48

score 0 · Answer 1 · answered Mar 03 '23 at 09:22

You need to use double quotes to enclose the entire SQL query string, and use single quotes around the '01-' string:

DF = pd.read_sql("SELECT CONVERT(VARCHAR(MAX), candidate, 2) AS candidate, CONVERT(datetime, '01-' + Month, 23) AS Month FROM [workspace].[abctbldata]", conn)

How to convert and add a date while quering through to SQL via python

1 Answers1