Passing parameters to SQL Server using pandas

[Ilangos]

Hello Python forum,

I'm new to python world. Please bear with me if my question sounds silly.

My problem statement : Passing parameter to SQL server using pandas.

Note: Have imported all the necessary library for pandas,datetime,pyodbc in my code

Sql_query = """ SELECT Top 10
TRIM([Insured Name]) AS [Insured Name] From
tblPremiumRegisterReport Where
cast(dtInforceDate as date) between cast(@dtFrom as date) and cast(@dtUpto as date)"""

pd.read_sql(Sql_query,con=conn,params={'@dtFrom':date.today(),'@dtUpto':date.today()})

Getting below error:

Error:
ProgrammingError                          Traceback (most recent call last)
C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py in execute(self, *args, **kwargs)
   1377             else:
-> 1378                 cur.execute(*args)
   1379             return cur

ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

During handling of the above exception, another exception occurred:

DatabaseError                             Traceback (most recent call last)
<ipython-input-144-11ba7d565e99> in <module>()
      2 TRIM([Insured Name]) AS [Insured Name] From
      3 tblPremiumRegisterReport Where
----> 4 cast(dtInforceDate as date) between cast(@fromdate as date) and cast(@Uptodate as date)""",con=conn,params={'@dtFrom':date.today(),'@dtUpto':date.today()})

[heiner55]

You should show us the whole code.

[Ilangos]

Hi Heiner55,

Thank you for your reply. I was able to fix the error above. But,ended up in the new error.

Data_Source: .NET Framework Data Provider for OLE DB
OLE DB Provider : IBM DB2 for i IBMDA400 OLE DB

Python code as follows:

import adodbapi
import win32com.client

server='xx.x.xx.xx'
database='B60FCE23'
username='Robot'
password='xxxxxxx'

conn = adodbapi.connect("Provider=IBM DB2 for i IBMDA400 OLE DB; SERVER=server; Initial Catalog=database;User Id=username; Password=password;")

Not able to establish the connection successfully

Below error is received :

Error:
com_error                                 Traceback (most recent call last)
~\AppData\Roaming\Python\Python36\site-packages\adodbapi\adodbapi.py in connect(*args, **kwargs)
    112     try:  # connect to the database, using the connection information in kwargs
--> 113        co.connect(kwargs)
    114        return co

~\AppData\Roaming\Python\Python36\site-packages\adodbapi\adodbapi.py in connect(self, kwargs, connection_maker)
    274         try:
--> 275             self.connector.Open()  # Open the ADO connection
    276         except api.Error:

~\AppData\Roaming\Python\Python36\site-packages\win32com\client\dynamic.py in Open(self, ConnectionString, UserID, Password, Options)

~\AppData\Roaming\Python\Python36\site-packages\win32com\client\dynamic.py in _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args)
    286         def _ApplyTypes_(self, dispid, wFlags, retType, argTypes, user, resultCLSID, *args):
--> 287                 result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
    288                 return self._get_good_object_(result, user, resultCLSID)

com_error: (-2147352567, 'Exception occurred.', (0, 'Provider', 'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.', None, 1240640, -2147217887), None)

During handling of the above exception, another exception occurred:

OperationalError                          Traceback (most recent call last)
<ipython-input-21-95bd38acc841> in <module>
----> 1 conn = adodbapi.connect("Provider=IBM DB2 for i IBMDA400 OLE DB Provider; SERVER=xx.x.xx.xx; Initial Catalog=B60FCE23;User Id=Robot; Password=xxxxxxx;")

~\AppData\Roaming\Python\Python36\site-packages\adodbapi\adodbapi.py in connect(*args, **kwargs)
    115     except (Exception) as e:
    116         message =  'Error opening connection to "%s"' % co.connection_string
--> 117         raise api.OperationalError(e, message)
    118 
    119 # so you could use something like:

OperationalError: (com_error(-2147352567, 'Exception occurred.', (0, 'Provider', 'Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.', None, 1240640, -2147217887), None), 'Error opening connection to "Provider=IBM DB2 for i IBMDA400 OLE DB Provider; SERVER=xx.x.xx.xx; Initial Catalog=B60FCE23;User Id=Robot; Password=xxxxxxx;"')

[parthi1705]

Below code to read data from SQL server with pyodbc, with two parameters

import pyodbc
import pandas as pd
cnxn = pyodbc.connect('Driver={SQL Server};'
                      'Server=localhost\sqlexpress;'
                      'Database=DBname;'
		      'UID=myuserid'
		      'PWD=mypassword;')
crsr = cnxn.cursor()

sql="Select Top 5 * from mytable where id between ? and ?"
df = pd.read_sql(sql,cnxn,params =[52,65])
df

[Ilangos]

Hi parthi,

Thank you for your response. However, I need to connect to IBM D2. pyodbc doesn't support that. I found out that adodbapi will be useful in such scenario.

My code for the same :

import adodbapi
import win32com.client
 
server='xx.x.xx.xx'
database='B60FCE23'
username='Robot'
password='xxxxxxx'
 
conn = adodbapi.connect("Provider=IBM DB2 for i IBMDA400 OLE DB; SERVER=server; Initial Catalog=database;User Id=username; Password=password;")

However, it error out. The error is mentioned in the above thread.

[parthi1705]

Your subject says SQL Server

Did you try with ibm_db module ?

pip install ibm_db

import ibm_db
conn = ibm_db.connect("DATABASE=name;HOSTNAME=host;PORT=60000;PROTOCOL=TCPIP;UID=username; PWD=password;", "", "")
cursor = conn.cursor()
cursor.execute("SELECT * FROM yourtable")
for cr in cursor.fetchall():
    print(cr)

[Ilangos]

Hi Parthi,

Pardon me for the subject. I did tried the above code snippet it fails though.

This is the connection string the RPA team is using. They are able to establish the connection successfully.

"Provider=IBMDA400.DataSource.1;Data Source=xx.x.xx.xx;Password=xxxxxxxx;User ID=Robot;Initial
Catalog=B60FCE23"

import ibm_db
conn = ibm_db.connect("DATABASE=B60FCE23;HOSTNAME=xx.x.xx.xx;PORT=60000;PROTOCOL=TCPIP;UID=Robot; PWD=xxxxxxxx;", "","")
cursor = conn.cursor()
cursor.execute("SELECT * FROM yourtable")
for cr in cursor.fetchall():
    print(cr)

Give me below error:

Error:
 SQLCODE=-30081n: [IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "xx.x.xx.xx".  Communication function detecting the error: "connect".  Protocol specific error code(s): "10060", "*", "*".  SQLSTATE=08001

[parthi1705]

Message shows that it was unable to connect to the specified port. Check if you have proper rights and concern are ports. Mostly issue aries when they are not configured or not having proper rights to do the task.
Check with your admin team if the fire is opened.

[Ilangos]

Hi parthi,

Thank you for the inputs.

Finally, found the solution for it. Have mentioned the solution for the folks who might face similar problem in future.

Problem statement: Connecting to IBM DB2 having OLE DB provider: IBM DB2 for I IBMDA400 OLE DB provider

driver='{iSeries Access ODBC Driver}' # Install this driver by contacting your local IT team
system='xx.x.xx.xx'
uid='Robot'
pwd='xxxxxxxx'

import pyodbc
import pandas as pd


ole_conn = pyodbc.connect(driver=driver,system=system,uid=uid,pwd=pwd)
ole_query= "your desired query"
ole_data = pd.read_sql_query(ole_query,con=ole_conn)
ole_data.head(5)