How to Automate Backend Processes in SQL Server Instance with Python

Total: 2 Average: 5

Automating backend processes in your SQL Server instance is a common task. Whether it is refreshing the statistics, rebuilding indexes, reading or deleting data, other programming frameworks integrating with the RDMS is extremely valuable. This article will consider these types of tasks specifically integrated with Python and various method modules available to do so.

CodingSight - SQL Server and Python

SQL Server Connection String

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.

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 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.

Connect to SQL Server

SQL Server Database and Table Сreation Сode

First, let’s create the database with a table structure required to work with Python and SQL Server. The following script will create a database and a single table which will be used for demonstration purposes.

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

Multiple Python modules are available to connect to your SQL server instance, and two of them are primary. In this post, we’ll focus on PYODC and SQLAlchemy. These modules largely 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 is 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
read_sql_query command

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 4th and 5th rows are loaded by Python.

inserting the data directly into the table with the execute command

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 Windows authentication and the SQL server authentication. Also, we reviewed the two methods for loading data, one of which involves using object DataFrame in Python, and another one is a raw SQL INSERT command.

Latest posts by Ian Fogelman (see all)

Ian Fogelman

Ian is a database enthusiast with expertise across multiple database technologies including SQL Server, RDS, Snowflake, MySQL and Postgres. Other technologies and concepts that Ian has expert level experience with include AWS, ETL strategies, Python scripting, Cloud architecture and system automation.