How To Read And Write To Microsoft SQL Using Python REST API

Introduction

In this article, you will learn how to read Microsoft SQL table, insert the results to another SQL table using Python REST API. I have created simple Microsoft SQL tables for this demo named as Source and Target which contains the following columns and rows.

Source table

Results returned from Source table gets inserted into Target table as shown below.

Target table

Topics Covered

This article demonstrates how to build the following:

• Create the project folder
• Install the required packages
• Implement the REST API
• Test the API

Pre-requisites

1. Windows machine and Visual Studio code are used for this article. Complete all the prerequisites mentioned in this article.
2. Install the Microsoft ODBC Driver for SQL Server on Windows.

Tools

1. Visual Studio Code

Task 1: Create the project folder

In this task, you will see how to create the project folder.

Step 1: Open Windows Command Prompt and run the following commands to create the new folder for this project.

mkdir ReadWriteSQL
cd .\ReadWriteSQL

Task 2: Install the required packages

In this task, you will see how to install the required packages for this project.

Step 1: Open ReadWriteSQL folder in Visual Studio Code. Click Terminal -> New Terminal.

Step 2: Run the following command to install the packages required for this project.

pip install flask
pip install flask-restful
pip install pyodbc

• flask is a simple framework for building complex web applications.
• flask-restful is an extension for Flask that adds support for quickly building REST APIs
• pyodbc is an open source Python module that makes accessing ODBC databases simple.

Task 3: Implement the REST API

In this task, you will see how to implement the REST API to read the Microsoft SQL table, insert the results to another table.

Step 1: In Visual Studio Code, create a new file app.py under ReadWriteSQL folder.

Step 2: Copy and paste the below code. Note: Update the connection string.

from flask import Flask, jsonify
from flask_restful import Resource, Api
import pyodbc 

# Input Parameters
getdatacmd='SELECT id, column1, column2 FROM Source'
connstring='DRIVER={ODBC Driver 18 for SQL Server};SERVER=********\SQLEXPRESS;DATABASE=sample;ENCRYPT=no;Trusted_Connection=yes;'

# Create the flask app
app = Flask(__name__)
# Create an API object
api = Api(app)

# Class for GetData
class GetData(Resource):
	# GET Request
	def get(self):
		cnxn = pyodbc.connect(connstring)
		cursor = cnxn.cursor()
		cursor.execute(getdatacmd)	
		columns = [column[0] for column in cursor.description]
		results = []	
		rows = cursor.fetchall()
		for row in rows:
			value=dict(zip(columns, row))								
			results.append(value)	
			cursor.execute('INSERT INTO Target (id, column1, column2) values(?,?,?)', value['id'],value['column1'],value['column2'])		
		cnxn.commit()
		cursor.close() 
		return jsonify({'message': 'The below specified data added to database'},{'data': results} ) 
				

# Add the defined resources along with their corresponding urls
api.add_resource(GetData, '/')

# Driver function
if __name__ == '__main__':

	app.run(debug = True)

Task 3: Test the API

In this task, you will see how to test the API which inserts the Source table results to Target table.

Step 1: In Visual Studio Code, run the following command in the Terminal.

python .\app.py

Step 2: Open the browser and access the below URL which inserts the results from Source table to Target table.

http://127.0.0.1:5000/

[
  {
    "message": "The below specified data added to database"
  },
  {
    "data": [
      {
        "column1": "X1",
        "column2": "Y1",
        "id": "1001"
      },
      {
        "column1": "X2",
        "column2": "Y2",
        "id": "1002"
      },
      {
        "column1": "X3",
        "column2": "Y3",
        "id": "1003"
      },
      {
        "column1": "X4",
        "column2": "Y4",
        "id": "1004"
      }
    ]
  }
]

References

1. Flask-RESTful documentation
2. Python SQL Driver - pyodbc

Summary

This article describes how to read Microsoft SQL table, insert the results to another SQL table using Python REST API.