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()
Advertisements

One thought on “Connecting to SQL in your Python script

  1. The driver in connection string depends on the SQL Server version you’re working with.
    For eg. I was using the following string to connect to SQL server 2010

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

    I changed the driver to “SQL Server Native Client 11.0” when I needed to connect to SQL Server 2012.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s