CRUD Operations on Azure SQL Database with Python

Python connection with azure cloud database

Photo by Boitumelo Phetla on Unsplash

In this article, we will cover the CRUD operations i.e. create, read, update, and delete operations with python pyodbc on Azure SQL database.

Below are examples of the operations with python

1. Firstly, we need a SQL database service on the Azure cloud.
2. At the time of making the database remember the database name, server name, username, and password. To open the SQL database on azure is shown below.

How to Create and Delete SQL Database on Azure Cloud

3. Now, open the jupyter notebook on the local machine and import the libraries shown below:

import pyodbc
import pandas as pd

4. Create a connection with the help of the pyodbc library with the azure SQL database.

server = '<server>.database.windows.net'
database = '<database>'
username = '<username>'
password = '{<password>}'   
driver= '{ODBC Driver 17 for SQL Server}'

After hitting shift+enter it will give no error means our connection is successful. We need to allow the users to use this database and add the IP at the time of creating the SQL database. Also, use the sample tables in the SQL while enabling them at the time of creating a database.

CRUD operations with python examples

• Create a new table: In this operation, we will create a new table in the Azure SQL database. To execute a query we need a cursor method.

cursor = conx1.cursor()

cursor.execute('''
            CREATE TABLE products (
            product_id int primary key,
            product_name nvarchar(50),
            price int
            )
               ''')
conx1.commit()

After the creation of the table, we need to insert the data in the table.

cursor.execute('''
        INSERT INTO products (product_id, product_name, price)…