Problem converting varchar to datetime with SQLAlchemy execute

Question

I can successfully connect to SQL Server Management Studio from my jupyter notebook with this script :

from sqlalchemy import create_engine
import pyodbc 
import csv
import time
import urllib

params = urllib.parse.quote_plus('''DRIVER={SQL Server Native Client 11.0};
                                    SERVER=SV;
                                    DATABASE=DB;
                                    TRUSTED_CONNECTION=YES;''')

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

Just for an example, the following script perfectly works :

engine.execute("delete from table_name_X")

However, I failed to get the following script to work. For information, it works when I execute its adaptation in SQL Server Management Studio :

cde = 5
reportDate = df.loc[df.index[0],'Report Date'] # when you execute reportDate it returns 2019-11-15 00:00:00

req = "DELETE table_name_Y "
req+= "WHERE code = " + str(cde)
req+= " AND report_date = '" + str(reportDate.strftime('%Y-%m-%d')) + "'"

engine.execute(req)

According to the error message, there is a problem with the conversion of a varchar to a datetime, which created a value out of range. However, independently executed, the script str(reportDate.strftime('%Y-%m-%d')) works.

Could you please help me to understand why this previous script does not work ?

could you please print out req ? and add the error message too ! — Steven
– Steven, Commented Dec 12, 2019 at 10:51
"DELETE table_name_Y WHERE code = 5 AND report_date = '2019-11-15'" — Ewdlam
– Ewdlam, Commented Dec 12, 2019 at 10:53
reportDate = 2019-11-15 00:00:00 produces "SyntaxError: invalid syntax" — Gord Thompson
– Gord Thompson, Commented Dec 12, 2019 at 10:54
Using string formatting to pass values to SQL queries is error prone as you've found out and can lead to SQL injection. Use placeholders in the query instead and pass the arguments to execute() separately. That being said, you'll still probably have to first convert the pandas timestamp to a datetime. — Ilja Everilä
– Ilja Everilä, Commented Dec 12, 2019 at 12:16

Gord Thompson · Accepted Answer · 2021-04-05 14:08:07Z

2

As @Ilja mentions in the comments to the question, you really should not be using dynamic SQL to construct your statement. It is error-prone and potentially unsafe. If you use a proper parameterized query many of your issues will simply go away.

For what it's worth, this works for me:

import pandas as pd
import sqlalchemy as sa

# ...

with engine.begin() as conn:    
    # set up test environment
    conn.execute(sa.text("CREATE TABLE #table_name_Y (code int, report_date date)"))
    conn.execute(sa.text("INSERT INTO #table_name_Y (code, report_date) VALUES (5, '2019-11-15')"))
    # verify test environment
    result = conn.execute(sa.text("SELECT * FROM #table_name_Y")).fetchall()
    print(result)  # [(5, datetime.date(2019, 11, 15))]
    
    # test code
    df = pd.DataFrame([(5, datetime.datetime(2019, 11, 15),), ], columns=['code', 'Report Date'])
    cde = int(df.loc[df.index[0],'code'])
    print(type(cde))  # <class 'int'>
    reportDate = df.loc[df.index[0],'Report Date']
    print(type(reportDate))  # <class 'pandas._libs.tslibs.timestamps.Timestamp'>
    sql = sa.text("DELETE FROM #table_name_Y WHERE code = :p0 AND report_date = :p1")
    params = {'p0': cde, 'p1': reportDate}
    conn.execute(sql, params)
    
    # verify outcome
    result = conn.execute(sa.text("SELECT * FROM #table_name_Y")).fetchall()
    print(result)  # []

edited Apr 5, 2021 at 14:08

answered Dec 12, 2019 at 12:25

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ewdlam Over a year ago

Thanks for your help. Based on your answer, I tried to execute the following script : sql = sqlalchemy.text("DELETE FROM #table_name_Y WHERE code = :p0 AND report_date = :p1") params = {'p0': cde, 'p1': reportDate} engine.execute(sql, params) And I have the following error : (pyodbc.ProgrammingError) ('Invalid parameter type. param-index=0 param-type=numpy.int64', 'HY105') [SQL: 'DELETE FROM #table_name_Y WHERE code = ? AND report_date = ?'] [parameters: (5, '2019-11-15')] (Background on this error at: sqlalche.me/e/f405) Do you know why ?

Gord Thompson Over a year ago

... because the example code in your question is misleading. cde = 5 produces an int, and that's (apparently) not what it actually is. I have updated my answer.

Ewdlam Over a year ago

Thanks. I still have another issue, I am going to try to understand why

Ewdlam Over a year ago

It works ! Many thanks. I needed to remove the '#' in DELETE FROM #table_name_Y. Is there a specific reason why you used a # ?

Gord Thompson Over a year ago

In SQL Server, tables whose name starts with # are local temporary tables. Tables whose name starts with ## are global temporary tables.

Collectives™ on Stack Overflow

Problem converting varchar to datetime with SQLAlchemy execute

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related