CODEWITHSUNDEEP
pythonsqlsql-serversqlalchemypyodbc
Ewdlam
Ewdlam

Reputation: 935

Problem converting varchar to datetime with SQLAlchemy execute

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 ?

Upvotes: 2

Views: 663

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

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)  # []

Upvotes: 2

Related Questions