Connecting Python to SQL server with Pyodbc only requires some simple steps. This model allows users to use Python’s dynamic nature to build and execute SQL queries.

These two formidable forces will take your code to the next level of efficiency and automation. So how to connect Python to SQL server using Pyodbc? Here is the answer.

How To Connect Python To SQL Server Using Pyodbc

Install And Import The pyodbc Library

To do this task, run the pip install pyodbc code and then import it in your Jupyter notebook:

import pyodbc

Now, pyodbc will become the bridge between Python and SQL, making it accessible to open database connectivity (ODBC) databases.

Connect To The SQL Server

There should be a connection with the server, so you need to use the pyodbc.connect function here. Yet, this function employs a parameter like a defined and separately declared connection string.

Two connection string types include trusted and non-trusted ones. With the former type, user_id and password are compulsory to access:

connection_string = ("Driver={SQL Server Native Client 11.0};"
            "Server=Your_Server_Name;"
            "Database=My_Database_Name;"
            "Trusted_Connection=yes;")

Here is the code for non-trusted type:

connection_string = ("Driver={SQL Server Native Client 11.0};"
            "Server=Your_Server_Name;"
            "Database=My_Database_Name;"
            "UID=Your_User_ID;"
            "PWD=Your_Password;")

You need to have a server, a database, a user ID, and password to access the SQL server. The name can be found in your SQL server Management login window or the SELECT @@SERVERNAME query.

Besides, look for the database in the left side of the object Explorer menu. For user_ID, it is exactly the Login_ID_Here.

Once you have got all the necessary information and write a connection string, call the pyodbc.connect function to execute the connection:

connection = pyodbc.connect(connection_string)

Run The SQL Query

Let’s define the cursor to run the SQL queries in the Python environment. Make a simple query with the first 10 rows in the connection_string with the State_Population table name.

# Initialise the Cursor
cursor = connection.cursor()

# Executing a SQL Query
cursor.execute('SELECT TOP(10) * FROM State_Population')

When executing the query in SQL, you will receive no Python output. Yet, you can print the results:

Input:

for row in cursor:
    print(row)

Output:

(AL,  under18,  2012,   1117489.0)
(AL,    total,  2012,   4817528.0)
(AL,  under18,  2010,   1130966.0)
(AL,    total,  2010,   4785570.0)
(AL,  under18,  2011,   1125763.0)
(AL,    total,  2011,   4801627.0)
(AL,    total,  2009,   4757938.0)
(AL,  under18,  2009,   1134192.0)
(AL,  under18,  2013,   1111481.0)
(AL,    total,  2013,   4833722.0)

While the code allows you to see the result, it is not ideal for carrying out further processing.

Bring SQL Table In Python

Import the SQL data in the form of a dataframe. There are three functions for SQL reading: pandas.read_sql(), pandas.read_sql_table(), and pandas.read_sql_query(). The query can be written outside the function with the variable name.

Instead of the first 10 rows, you will see all states more than 100,000 square miles:

Input:

# write the query and assign it to variable
query = 'SELECT * FROM STATE_AREAS WHERE [area (sq. mi)] > 100000'

# use the variable name in place of query string
area = pd.read_sql(query, connection)

Conclusion

The article gives detailed instructions with examples to connect Python to SQL server using Pyodbc.