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

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