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

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:

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

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

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:

About Gopal Krishna Ranjan

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

Thanks for reading and sharing your views.
The cursor that we are using here is not a DB cursor and is a python class. Python uses a cursor object to interact with the database using connection object. For example, for the SELECT query, Python cursor provides an abstraction over the data set received from the DB. Now, you can have a better control on the output using cursor methods fetchall(), fetchone() or fetchmany().