0

I have .ipynb script which create 3 variables which i try to use for SQL function already created on MS SQL server. Structure of function is like this:

[YS_Cheque_check] (@createddate date, @FILID int, @SUMCHEQ float)

So i created code for this task which look like this:

# import lib

import pandas as pd
import pyodbc 
import os
import csv
import codecs
from datetime import datetime


date = "2022-10-25"
filid = 447
amount = 1206.84
# connection to DB
conn_to_100 = pyodbc.connect('Driver={SQL Server};'
                        'Server=name\department;'
                        'Trusted_Connection=yes;')

print("Success")

sql_query = f''' 
USE DB_name
select * from YS_cheque_check ({date}, {filid},{amount})
'''
df = pd.read_sql_query(sql_query, conn_to_100)

But after compilation i got an error:

`Success #that`s mean i have connection to db
---------------------------------------------------------------------------
DataError                                 Traceback (most recent call last)
C:\ProgramData\Anaconda3\envs\rec\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   2055         try:
-> 2056             cur.execute(*args, **kwargs)
   2057             return cur

DataError: ('22018', '[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: int is incompatible with date (206) (SQLExecDirectW)')

The above exception was the direct cause of the following exception:

DatabaseError                             Traceback (most recent call last)
<ipython-input-56-37f3be5e614f> in <module>
      9 select * from YS_cheque_check ({date}, {filid},{amount})
     10 '''
---> 11 df = pd.read_sql_query(sql_query, conn_to_100)

C:\ProgramData\Anaconda3\envs\rec\lib\site-packages\pandas\io\sql.py in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype)
    441         parse_dates=parse_dates,
    442         chunksize=chunksize,
--> 443         dtype=dtype,
    444     )
    445 

C:\ProgramData\Anaconda3\envs\rec\lib\site-packages\pandas\io\sql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize, dtype)
   2114 
   2115         args = _convert_params(sql, params)
-> 2116         cursor = self.execute(*args)
   2117         columns = [col_desc[0] for col_desc in cursor.description]
   2118 

C:\ProgramData\Anaconda3\envs\rec\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   2066 
   2067             ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}")
-> 2068             raise ex from exc
   2069 
   2070     @staticmethod

DatabaseError: Execution failed on sql ' 
USE DBName
select * from YS_cheque_check (2022-10-25, 447,1206.84)
': ('22018', '[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: int is incompatible with date (206) (SQLExecDirectW)')
`

I'm expecting that mu code will run SQL function and I will get right output.

Jeki4an
  • 19
  • 7
  • 2
    **Parametrise** don't inject. PyODBC uses parameter markers (`?`). – Thom A Mar 02 '23 at 10:17
  • Does this answer your question? [pyodbc - How to perform a select statement using a variable for a parameter \[duplicate\]](https://stackoverflow.com/questions/9518148/pyodbc-how-to-perform-a-select-statement-using-a-variable-for-a-parameter) – Thom A Mar 02 '23 at 10:18
  • could u please explain? – Jeki4an Mar 02 '23 at 10:18
  • oh, okay i don`t know about this. it will only work with cursor.execute? or i also in my code? – Jeki4an Mar 02 '23 at 10:22
  • No, it doesn't have to be `cursor.execute`, that's just the first example I found with a quick search. – Thom A Mar 02 '23 at 10:26
  • 1
    A simple fix is probably: select * from YS_cheque_check ('{date}', {filid},{amount}) you have to quote your date because otherwise it just becomes an int (2022-10-25 is something like 1987) – siggemannen Mar 02 '23 at 10:54
  • Thanks a lot, finally get right answer :) i did this with sql_query but problem was in parenthesis, unbelievable – Jeki4an Mar 07 '23 at 08:02

0 Answers0