Connecting Python 3 to SQL Server 2017 using pyodbc


In this post “Connecting Python 3 to SQL Server 2017 using pyodbc”, we are going to learn that how we can connect Python 3 to SQL Server 2017 to execute SQL queries. We can change the settings accordingly to connect to other versions of SQL Server also. If you are interested to know more about Python and why you should learn it, visit our post “Why Python and how to use it in SQL Server 2017“.

What is pyodbc?

pyodbc is an open source DB API 2 Python module. It provides a convenient interface to connect a database which accepts an ODBC connection. In order to use pyodbc module, firstly, we need to install it. Click here for more information on pyodbc.

pip install pyodbc module

We can use pip install command to install the pyodbc module in Python 3 on a Windows machine. Before executing the “pip install command“, make sure that the PATH variable (in environment variables) value is pointing to the Python 3 installation folder in case we have installed multiple versions of Python on the same machine.

Open a command prompt window with administrator privilege and execute the below command:

pip install pyodbc

Once, we execute the above command, after successful installation, we should get a message like this:

pip install pyodbc

pip install pyodbc

Connect to SQL Server 2017

In order to connect to SQL Server 2017 from Python 3, import the pyodbc module and create a connection string. Then, create a cursor using pyodbc.connect() method like this:

#Import pyodbc module using below command
import pyodbc as db

#Create connection string to connect DBTest database with windows authentication 
con = db.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=.;Trusted_Connection=yes;DATABASE=DBTest')
cur = con.cursor()

SELECT Records from a table

Once, we have created the connection and a database cursor as well, we can use cursor.execute() method to execute a query against the DBTest database. For example, if we have an employee table tbl_EmpMaster with columns “EmpName“,  and “CommaSepSkills“, which looks like this:

Employee Table

Employee Table

We can write python code to extract all the rows from that table like this:

#Import pyodbc module using below command
import pyodbc as db

#Create connection string to connect DBTest database with windows authentication 
con = db.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=.;Trusted_Connection=yes;DATABASE=DBTest')
cur = con.cursor()

#SELECT all rows from employee table
qry = 'SELECT EmpName, CommaSepSkills FROM dbo.tbl_EmpMaster'
cur.execute(qry)

row = cur.fetchone() #Fetch first row
while row: #Fetch all rows using a while loop
    print(row)
    row = cur.fetchone()
cur.close() #Close the cursor and connection objects
con.close()

Output:

SELECT query output

SELECT query output

We can use cursor.fetchone() or cursor.fetchall() methods to fetch one or all rows from the cursor respectively.

INSERT Records into a table

To insert rows into a table, we can use an INSERT query and pass it to the cursor.execute() method. Also, to avoid any SQL injection attack, we should always use parameterized queries like this:

#Import pyodbc module using below command
import pyodbc as db

#Create connection string to connect DBTest database with windows authentication 
con = db.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=.;Trusted_Connection=yes;DATABASE=DBTest')
cur = con.cursor()

#SELECT all rows from employee table
qry = '''INSERT INTO dbo.tbl_EmpMaster
        (EmpName, CommaSepSkills)
        VALUES(?, ?)
        '''
param_values = ['Mark', 'SQL,BigData,Spark,Azure']
cur.execute(qry, param_values)

print('{0} row inserted successfully.'.format(cur.rowcount))

cur.commit() #Use this to commit the insert operation
cur.close()
con.close()

Output:

INSERT query output

INSERT query output

We can use cursor.execute() or cursor.executemany() methods to insert one or multiple rows respectively. Also, we must use the cursor.commit() method to commit the insert operation to the database otherwise it will be discarded.

To get the affected rows count, we can use cursor’s rowcount property as “cur.rowcount“.

UPDATE Records

Similarly, we can use UPDATE query with cursor.execute() method to update existing records. Here is the Python code to update the records:

#Import pyodbc module using below command
import pyodbc as db

#Create connection string to connect DBTest database with windows authentication 
con = db.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=.;Trusted_Connection=yes;DATABASE=DBTest')
cur = con.cursor()

#SELECT all rows from employee table
qry = '''UPDATE dbo.tbl_EmpMaster
        SET CommaSepSkills = ?
        WHERE EmpName = ?
        '''
param_values = ['Hadoop,Spark,BigData,SQL,Azure', 'Mark']
cur.execute(qry, param_values)

print('{0} row updated successfully.'.format(cur.rowcount))

cur.commit() #Use this to commit the update operation
cur.close()
con.close()

Output:

UPDATE query output

UPDATE query output

DELETE Records

We can use a DELETE command with cursor.execute() method to delete records from a table like this:

#Import pyodbc module using below command
import pyodbc as db

#Create connection string to connect DBTest database with windows authentication 
con = db.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=.;Trusted_Connection=yes;DATABASE=DBTest')
cur = con.cursor()

#SELECT all rows from employee table
qry = '''DELETE tbl_EmpMaster
        WHERE EmpName = ?
        '''
param_values = ['Mark']
cur.execute(qry, param_values)

print('{0} row deleted successfully.'.format(cur.rowcount))

cur.commit() #Use this to commit the delete operation
cur.close()
con.close()

Output:

DELETE query output

DELETE query output

I have uploaded the script files used in this example as a zip folder here. Click here to download it.

This zip folder contains these scripts:

  1. Create table script
  2. Add initial data to the table script
  3. Python code file – SELECT records
  4. Python code file – INSERT records
  5. Python code file – UPDATE records
  6. Python code file – DELETE records

You can download and use it for practice purpose.

Thanks for reading. Please share your inputs in the comment section.

Rate This
[Total: 1    Average: 5/5]


Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 8 years of industry experience in Software development. I have a head down experience in Database, Data Warehouse, Big Data and cloud technologies and have implemented end to end Database, Data Warehouse,  Big Data and Cloud Solutions.
I have extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). I also have good experience in windows and web application development using ASP.Net and C#.

Leave a comment

Your email address will not be published. Required fields are marked *