pyodbc library for Python to connect with SQL Server databases

Download and install the latest version of python from Python Website if not already done.

Setting up virtual environment is not a must. But this is a best practice when working on python projects. Click virtual environment for more details.

Install pyodbc using the command: pip install pyodbc

pyodbc installation may some times throw an error message like Microsoft Visual C++ 14.0 or greater is required.
Refer next section to fix this error.

This issue is caused by the fact that the latest version of pyodbc is not optimized for the python version that is installed in our machines.
For example, at the time of writing this article, we had python 3.10.0 as the latest version in our machine and pyodbc version was 4.0.32 and this pyodbc version was throwing this error.
Follow the below steps to fix the issue.

Delete the already created virtual environment. Set it up again using the command.

Then install pyodbc using command: pip3.9 install pyodbc

Type command pip3.9 freeze
Should give an output like:

pyodbc==4.0.32

To connect to SQL Server database using pyodbc, we have to import pyodbc, define connection string and then use pyodbc.connect() method. Copy the below code to a python file in your Virtual Environment and run it.

import pyodbc 
s = '' #Your server name 
d = '' #Your database name
u = '' #Your login
p = '' #Your login password
str = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER='+s+';DATABASE='+d+';UID='+u+';PWD='+ p
conn = pyodbc.connect(str)
print(conn)

To run the code, use command python filename.py or py filename.py depending on the OS.
If using older version of Virtual Environment, use:

On running the code should see the below output if connection successful.

pyodbc.Connection object at 0x00000167148AE2A0

pyodbc Cursor object represents a database cursor, that we can use to manage data from an SQL query. We can create multiple cursors from same connection. Any changes done to data using one cursor is visible to the other cursor immediately.

To read data from SQL Server database, execute the command and use fetchone(), fetchval() or fetchall() method.

import pyodbc

#Prepare connection string as in Section 5

conn = pyodbc.connect(connstr)
cursor = conn.cursor()
cursor.execute("SELECT * FROM TableName")
for row in cursor.fetchall():
    print(row)

Insert to table sample below.

import pyodbc

#Prepare connection string as in Section 5

conn = pyodbc.connect(connstr)
cursor = conn.cursor()
cursor.execute("INSERT INTO TableName (id, name) VALUES (?, ?)", someid, 'somename')
conn.commit()

Update data to table sample below.

import pyodbc

#Prepare connection string as in Section 5

conn = pyodbc.connect(connstr)
cursor = conn.cursor()
cursor.execute("UPDATE TableName SET name = ? WHERE id = ?", 'somename', someid)
conn.commit()

import pyodbc

#Prepare connection string as in Section 5

conn = pyodbc.connect(connstr)
cursor = conn.cursor()
cursor.execute("DELETE FROM TableName WHERE id = ?", someid)
conn.commit()

Stored procedure without parameters

import pyodbc

#Prepare connection string as in Section 5

conn = pyodbc.connect(connstr)
cursor = conn.cursor()
cursor.execute("{CALL proc_name}")
conn.commit()

Stored procedure with parameters

import pyodbc

#Prepare connection string as in Section 5

conn = pyodbc.connect(connstr)
cursor = conn.cursor()
cursor.execute("{CALL proc_name(?, ?)}", p1, p2)
conn.commit()

Rowcount returns the number of rows modified. For select it will be -1. Sample below.

import pyodbc

#Prepare connection string as in Section 5

conn = pyodbc.connect(connstr)
cursor = conn.cursor()
c = cursor.execute("SELECT * FROM TableName").rowcount
print(c)

fetchone() returns next row.
fetchval() returns first column value of first row.
fetchall() returns all remaining rows.

import pyodbc

#Prepare connection string as in Section 5

conn = pyodbc.connect(connstr)
cursor = conn.cursor()
cursor.execute("SELECT * FROM TableName")
for row in cursor.fetchall():
    print(row)

Use conn.autocommit = True to auto commit your transactions without using commit() method.

commit() Commits all transactions since last commit or rollback.
rollback() Rolls back all uncommitted transactions.

Make sure to close connection after use. Use conn.close()