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:

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:

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:

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:

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:

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:

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.