Connecting to SQL in your Python script


pypyodbc python package

I had been wondering for such a long time on how to connect my python scripts with our MS SQL database. So, when I had a little break from my workload today, I decided to finally solve this problem.

I have tried searching for solutions before too, but it seems today was my lucky day. I found the pypyodbc module.  It’s written in python, and helps execute SQL in your database.

Here’s an example of the module’s usage, and a getting started guide.

Some other article’s  withe useful information are – article on the various methods available in moduleUsing stored procedures

I needed to execute an SP in the database through my automated test script to check the data displayed on the front-end. So, I included the  following function in my code.

It’s great that I finally know how to integrate python and database because I can do so much more.  🙂

import pypyodbc

connection_string = "driver={SQL Server Native Client 10.0};
 server=127.0.0.1; database=databaseName; 
  uid=user; pwd=pwd"

cnxn = pypyodbc.connect(connection_string)
cursor = cnxn.cursor()

# SET NOCOUNT ON should be added to prevent errors thrown by cursor

sql = """
SET NOCOUNT ON
DECLARE @var1 INT = 0
DECLARE @var2Out NVARCHAR(MAX)
EXEC [dbo].[storedProc] 1,5,'',0,0,2,@var1 OUTPUT,@var2Out OUTPUT
SELECT @var1, @var2Out 
"""
result = cursor.execute(sql)

print result.fetchall()

result.nextset()
print result.fetchall()
result.nextset()
print result.fetchall()