Connecting Python to SQL Server

In this tutorial, we will look at how to connect Python with SQL server using Pyodbc Library. The Pyodbc library is an open-source Python module that makes accessing ODBC databases simple. It is also a community-supported software. We have other Database Management Systems (DMS) that also work with ODBC. These Database Management Systems include; Oracle, MS Access, IBM Db2, MS SQL Server, and MySQL.

How to Get Started: Installing Pyodbc

The first thing we need to do is to install the Pyodbc Library in order to create a connection between Python and the SQL Server.

Terminal
pip install pyodbc

Creating a SQL Server Connection

Now, we need to create a connection using the Pyodbc library we just installed. The credentials we need to use are; Server Name, Database Name, and the table we want to connect to.

Python
#import the pyodbc library import pyodbc #create a connection with the pyodbc using the needed credentials the_conn = pyodbc.connect('Driver={SQL Server};' 'Server=my-project.xxxxxxxxxxxxxs.amazonaws.com;' 'Database=myData;' 'Trusted_Connection=yes;') #create an instance and execute using the select command to retrieve your table details cursor = the_conn.cursor() cursor.execute('SELECT * FROM your_table_name') #loop through the result to obtain your table data for j in cursor: print(j)

Connecting Pyodbc with Pandas

You can also use Pandas library to connect to SQL Server along with the Pyodbc. The Pandas library is well known for its flexibility and ease of use. You can follow the steps below to use the library with Pandas.

Install Pandas

We can install Pandas with the Pip Package or can be installed with Anaconda or Miniconda as it is part of the Anaconda distribution:

Terminal — Install with PIP
pip install pandas
Terminal — Install with Conda
conda install pandas

From Pandas DataFrame to SQL

Here, we will read data from our computer using Pandas to a Dataframe. After then, we convert the Dataframe to a SQL Table. The SQLAlchemy create engine will also be used in the script to pass in our connection URL.

Python
#import our library import pyodbc import pandas as pd import urllib #import create engine from sqlalchemy from sqlalchemy import create_engine #this is what the credentials we'll be passing in looks like server = 'my-project.xxxxxxxxxxxxxs.amazonaws.com' database = 'myData' username = 'admin' password = 'xxxxxxxx' #the file we are bringing in, we read it with pandas df= pd.read_csv('Python_Project.csv') #we use the urllib_parse_quote to read in all our credentials to a url string quoted = urllib.parse.quote_plus("DRIVER={ODBC Driver 17 for SQL Server};SERVER=my-project.xxxxxxxxxxxxxs.amazonaws.com;DATABASE=myData;UID=admin;PWD=xxxxxxxx") engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted)) #we convert the file to SQL Table df.to_sql('provide_the_name_for_our_table', schema='dbo', con = engine)

From SQL to Pandas DataFrame

Earlier we learned how to convert a Pandas DataFrame to SQL Table. We can also read SQL data with Pandas and convert it to a DataFrame.

Python
#import our libraries import pandas as pd import pyodbc #create a connection using your credentials the_conn = pyodbc.connect('Driver={SQL Server};' 'Server=my-project.xxxxxxxxxxxxxs.amazonaws.com;' 'Database=myData;' 'Trusted_Connection=yes;') #we use the pandas read sql_query along with our connection instance to retrieve our table df = pd.read_sql_query('SELECT * FROM students', the_conn) #we then print our data print(df.head(n=5))

Conclusion

In this tutorial, we explored how to connect Python to SQL Server using the Pyodbc library. We covered installing Pyodbc, creating a database connection, and exchanging data between Pandas DataFrames and SQL Server tables in both directions. These techniques form the foundation for building robust data pipelines between Python applications and SQL Server databases.

Originally published on Medium. Read the original article →
Need help with your data?

Let CNDRO build the data infrastructure your business needs

From database integrations to analytics pipelines — we engineer the backend so you can focus on growth.

Book a free consultation →