How to Connect Python to SQL Server using pyodbc

October 1, 2021

Need to connect Python to SQL Server using pyodbc?

If so, you’ll see the full steps to establish this connection using a simple example.

To start, here is a template that you can use to connect Python to SQL Server:

import pyodbc 

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=server_name;'
                      'Database=database_name;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM table_name')

for i in cursor:
    print(i)

The Example

Let’s review a simple example, where:

  • The server name is: RON\SQLEXPRESS
  • The database name is: test_database
  • The table name is: products
  • The ‘products’ table contains the following data:
product_id product_name price
1 Laptop 1100
2 Printer 200
3 Keyboard 80
4 Monitor 450
5 Tablet 300

Steps to Connect Python to SQL Server using pyodbc

Step 1: Install pyodbc

To start, install the pyodbc package which will be used to connect Python to SQL Server.

You may use PIP to install the pyodbc package:

pip install pyodbc

Step 2: Retrieve the server name

Next, retrieve your server name.

One way to find your current server name is by running the following query:

SELECT @@SERVERNAME

For our example, the server name is:

RON\SQLEXPRESS

Step 3: Connect Python to SQL Server

And for the final part, open your Python IDLE and fill the server name, database and table information.

Here is a template that you may use to connect Python to SQL Server:

import pyodbc 

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=server_name;'
                      'Database=database_name;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM table_name')

for i in cursor:
    print(i)

Recall that for our example:

  • The server name is: RON\SQLEXPRESS
  • The database name is: test_database
  • The table name is: products

Therefore, the following code can be used to connect Python to SQL Server:

import pyodbc 

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=test_database;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM products')

for i in cursor:
    print(i)

Run the code in Python (adjusted to your server name, database and table information) and you’ll get the following results:

(1, 'Laptop', 1100)
(2, 'Printer', 200)
(3, 'Keyboard', 80)
(4, 'Monitor', 450)
(5, 'Tablet', 300)

From SQL to Pandas DataFrame

You can take things further by going from SQL to Pandas DataFrame using pd.read_sql_query:

import pandas as pd
import pyodbc 

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=test_database;'
                      'Trusted_Connection=yes;')

df = pd.read_sql_query('SELECT * FROM products', conn)

print(df)
print(type(df))

When applying pd.read_sql_query, don’t forget to place the connection string variable at the end. In our case, the connection string variable is conn.

Once you run the code (adjusted to your database connection information), you’ll get the following Pandas DataFrame:

   product_id  product_name  price
0           1        Laptop   1100
1           2       Printer    200
2           3      Keyboard     80
3           4       Monitor    450
4           5        Tablet    300
<class 'pandas.core.frame.DataFrame'>

Note that the syntax of print(type(df)) was also added at the bottom of the code to confirm that we’ve got a DataFrame.

Conclusion and Additional Resources

You have seen how to connect Python to SQL Server. Once you established this connection, you may start using SQL in Python to manage your data.

You may also use Python to insert values into SQL Server table.

For further information about the pyodbc package, please visit the pyodbc documentation.