How to Connect Python to SQL Server to Automate Backend Process

Automating backend processes in your SQL Server instance is a common task. Whether it is refreshing the statistics, rebuilding indexes, reading, or deleting data, it is extremely valuable to integrate other programming frameworks with your RDBMS. This article will consider these types of tasks specifically integrated with Python.

We won’t focus on such fundamental questions as what is SQL and Python, or how and where we use them. Instead, we are going to examine the specific cases that require combining SQL and Python, and various method modules available to do so.

SQL Server Connection String

Before we start to explore the case of Python connect to SQL server, let us get familiar with the basics.

A connection string represents the required pieces of data that point to and authenticate your database instance. There could be slight nuances for each database instance type, but in general, the required information would be the server name, database driver, username, password, and port number.

SQL Server Instance Details

The details of this connection string will be the same as when you connect via SSMS to the database instance. Take note of the database instance {SERVER}\{INSTANCE} format – here it is DESKTOP-6L1B155\FogelDev. It is also possible to connect to a server name if you have a single default instance running only.

SQL Server Database and Table Сreation Сode

First, we need a database with a table structure required to work with Python and SQL Server. The following script will create such a database and a single table in it. That table will serve as the demonstration area for us to see how to use SQL and Python together.

CREATE DATABASE CODESIGHT_DEMOS;
USE CODESIGHT_DEMOS;
CREATE TABLE POSTS
(
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(200),
Author VARCHAR(100),
URL VARCHAR(100),
DATE DATE
)

INSERT INTO POSTS
VALUES('Properly Dealing with Encryption of Databases in an AlwaysOn Availability Group Scenario','Alejandro Cobar','https://codingsight.com/encryption-of-databases-in-alwayson-availability-group-scenario/','4/23/2021')

INSERT INTO POSTS
VALUES('An Overview of DataFrames in Python','Aveek Das','https://codingsight.com/an-overview-of-dataframes-in-python/','4/23/2021')

INSERT INTO POSTS
VALUES('SQL Server Business Intelligence (BI) – Traditional Tools and Technologies','Haroon Ashraf','https://codingsight.com/sql-server-business-intelligence-bi-tools-and-technologies/','4/19/2021')

Python Modules

Our goal now is to define how to connect Python to SQL server instances. Multiple Python modules are available to perform this task, and two of them are primary. We are going to deal with PYODC and SQLAlchemy. These modules handle the connection part of the operations.

An additional code is required to read the data from SQL Server tables into memory. This is where the Pandas library comes in.

Pandas can be integrated closely with SQLALchemy to read the data straight into the DataFrame objects (in memory array-based storage which can operate at lightning speed and look just like database tables).

Let’s take a look at a few connection strings examples.

The first example uses the SQL Server and instance from the previous screenshot. You can set these variables into your specific details to instantiate connection.

Note also that this particular connection uses Windows authentication. Therefore, it will attempt to authenticate as the user from my Windows machine to which I am logged in. If no username and password are specified, this is the default behavior in the connection string.

• Servername = ‘DESKTOP-6L1B155’
• Instancename = ‘FogelDev’
• Databasename = ‘CODESIGHT_DEMOS’
• Portnumber = ‘1433’

print("mssql+pyodbc://@{SERVER}:{PORT}\\{INSTANCE}/{DATABASE}?driver=SQL+Server+Native+Client+11.0".format(SERVER = Servername, INSTANCE = Instancename,DATABASE = Databasename,PORT = Portnumber))

MSSQLengine = sqlalchemy.create_engine("mssql+pyodbc://@{SERVER}\\{INSTANCE}:{PORT}/{DATABASE}?driver=SQL+Server+Native+Client+11.0".format(SERVER = Servername, INSTANCE = Instancename,DATABASE = Databasename,PORT = Portnumber))

We can test if this connection works correctly by executing a query with Pandas. It has to read a query from the connection string using the read_sql_query command.

df = pd.read_sql_query("SELECT * FROM POSTS", MSSQLengine)
df

SQL Server Authentication

What if you need to use the SQL server authentication-based connection string to connect to your SQL Server? We can adjust our Python code to add username and password to the SQLALchemy engine.

• Servername = ‘DESKTOP-6L1B155’
• Instancename = ‘FogelDev’
• Databasename = ‘CODESIGHT_DEMOS’
• Portnumber = ‘1433’
• Username = ‘CodingSightUser’
• Password = ‘Password123’

print("mssql+pyodbc://{USER}:{PASS}@{SERVER}:{PORT}\\{INSTANCE}/{DATABASE}?driver=SQL+Server+Native+Client+11.0".format(SERVER = Servername, INSTANCE = Instancename,DATABASE = Databasename,PORT = Portnumber,USER = Username, PASS = Password))
MSSQLengine = sqlalchemy.create_engine("mssql+pyodbc://{USER}:{PASS}@{SERVER}\\{INSTANCE}:{PORT}/{DATABASE}?driver=SQL+Server+Native+Client+11.0".format(SERVER = Servername, INSTANCE = Instancename,DATABASE = Databasename,PORT = Portnumber,USER = Username, PASS = Password))

Again, we are testing our connection, but this time we are selecting 1 record from the table only.

df = pd.read_sql_query("SELECT TOP 1 * FROM POSTS", MSSQLengine)
df

Writing Data

Finally, take a look at getting the Python data into our SQL Server database table. We create a Dataframe object and append it to the table.

The Dataframe method:

newdata = [{'Name': 'How to Create the Date Table in Power BI', 'Author': 'Haroon Ashraf', 'URL':'https://codingsight.com/how-to-create-date-table-in-power-bi/', 'Date':'4/21/21'}]
load_df = pd.DataFrame(newdata)
load_df.to_sql("POSTS", MSSQLengine, if_exists='append',index = False, chunksize = 200)

Another method would be inserting the data directly into the table with the execute command.

The Execute method:

MSSQLengine.execute("INSERT INTO [POSTS] VALUES('SQL Query Optimization: 5 Core Facts to Boost Queries','Edwin Sanchez','https://codingsight.com/sql-query-optimization-5-core-facts-to-boost-queries/','3/31/21')");

When done, the final table looks as follows. It has the first 3 rows loaded by the initial SQL Server creation script, and the 4^th and 5^th rows are loaded by Python.

Summary

This post highlighted the connection string parameters for connecting the Python code with SQL Server. We examined reading the data out of the SQL Server and checked two different authentication methods – the specific case of Python connect to SQL server Windows authentication and the standard SQL server authentication. Also, we reviewed the methods for loading data, one of which involves using object DataFrame in Python, and another one is a raw SQL INSERT command.