Connect to Azure SQL database using Python

You can use an Azure SQL database with a Python application. But there are some steps that you need to follow before you can connect your Python application to the database. And we will discuss the following set of topics.

• Python library to connect to Azure SQL database
• Python code to connect to Azure SQL database
• Python create table in Azure SQL database
• Python insert into Azure SQL database
• Python select Azure SQL
• Python update in Azure SQL database
• Python delete in Azure SQL database
• CRUD operations in Azure SQL database using Python

Python library to connect to Azure SQL database

The Python library you need to connect your Python application and the Azure SQL database is the pyodbc library.

You need ODBC drivers for connecting your Python application to an Azure SQL database. The pyodbc is a library written in Python that uses the ODBC drivers to connect your Python application with an Azure SQL database.

To install the pyodbc library, follow the below steps:

• Open the command prompt and navigate to the directrory where you have installed Python(ignore if you have defined the path in the environment variables).
• Execute the below command:

pip install pyodbc

Once you have installed this library, you can import the library in your Python code and connect your Python application to any Azure SQL database.

Also, check: How to Create a Single Database in Azure SQL

Python code to connect to Azure SQL database

Let us see a sample Python code to make a connection with an Azure SQL database.

import pyodbc
server = 'mysql1000.database.windows.net'
database = 'DemoDatabase'
username = 'azureadmin'
password = 'Test@123' 
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT @@Version")
        row = cursor.fetchall()
        print(row)

• The first step is to import the pyodbc library.
• Secondly, we have defined the login credentials and the necessary database information. We will use this information for connecting and authenticating to the database.
• Then we are using the connect() function of the pyodbc library to make a connection to our Azure SQL database.
• We have used a cursor to fetch results from the database.
• After fetching the results into the cursor, we can use these results in our Python program. In our case, we are simply printing the results.
• The below is the output of our Python code.

Thus, you might have learned how you can connect your Python application to your Azure SQL database.

Also, read: Pause and resume Azure SQL database

Python create table in Azure SQL database

In this section, I will explain how to create a table in an Azure SQL database using Python. I will create a table using Python. The following is the Python code that you can execute to create a table in the Azure SQL database:

import pyodbc
server = 'mysql1000.database.windows.net'
database = 'DemoDatabase'
username = 'azureadmin'
password = 'Test@123' 
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER= '+ driver + '; SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute('''DROP TABLE IF EXISTS dbo.Student
CREATE TABLE dbo.Student(
	[Student ID] int,
	[Student Name] nvarchar(20),
	Stream nvarchar(20)
)''')

• Firstly, we imported the pyodbc library and made a connection to our Azure SQL database.
• Then we created a cursor and used the cursor to execute our SQL statement.
• We wrote our SQL query for CREATE TABLE statement inside the cursor.execute() function.
• The SQL query will first check if the table already exists or not. If it does not exist, the table will be created.

You can see that our table is created in the Azure SQL database.

Hence, in this way you can create a table in an Azure SQL database using Python.

Read: How to rename a database in Azure SQL

Python insert into Azure SQL database

You can also insert rows in an Azure SQL database table from a Python program using the pyodbc library.

For example, I have created a table named dbo.Customers. I will insert a row in this table using Python. Look at the code written below:

import pyodbc
server = 'mysql1000.database.windows.net'
database = 'DemoDatabase'
username = 'azureadmin'
password = 'Test@123' 
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("INSERT INTO dbo.Customers([Customer ID], [Customer Name]) VALUES(1211, 'Rosy')")

After executing this Python program, a row will be inserted into the dbo.Customers table. Let us verify it in our database by executing a Select statement:

SELECT * FROM dbo.Customers WHERE [Customer ID]=1211

You can see that a row is inserted into the dbo.Customers table.

Hence, in this way you can use the pyodbc library in Python to insert a row in an Azure SQL database.

Read: How to create table in azure sql database

Python select Azure SQL

The Select statement can be used to fetch result sets from the database and then use these resultsets in a Python application.

Let us see an example of a Select statement in Azure SQL database using a Python code snippet.

• We have a table named Customer under the SalesLT schema.
• We will fetch a few records from this table using a Python code snippet.

import pyodbc
server = 'mysql1000.database.windows.net'
database = 'DemoDatabase'
username = 'azureadmin'
password = 'Test@123' 
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM SalesLT.Customer")
        row = cursor.fetchall()
        for i in range(10):
            print(row[i])

• In the above code we are fetching all the records of the SalesLT.Customer table. The resultset is very large in size. Therefore, we are printing the first 10 rows of the resultset by iterating over the resultset using a loop.

• You can see the list of rows in the output console.

Hence, in this way, you can use the Select statement to fetch result sets from a table using a Python code snippet.

Also, Read: Backup Azure database to local SQL Server

Python update in Azure SQL database

In this section, you will learn to use the update statement in an Azure SQL database using a Python program. We will again use the pyodbc library to do this.

Let us consider the table dbo.Student that I have created in my database.

Suppose I want to change the Stream of a student in the database using Python code. I will execute the below Python code.

import pyodbc
server = 'mysql1000.database.windows.net'
database = 'DemoDatabase'
username = 'azureadmin'
password = 'Test@123' 
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("UPDATE [dbo].[Student] SET Stream = 'Electrical' WHERE [Student ID] = 1607")

Now let us verify in our database whether the record is updated or not.

You can see that the record is updated.

Hence, in this way, you can update data in an Azure SQL database using Python.

Read: Backup and restore SQL Server to Azure Blob storage

Python delete in Azure SQL database

You may also want to delete a record in an Azure SQL database using Python. You will see in this section, how you can implement the DELETE statement in an Azure SQL database from a Python program.

I have created a table named dbo.New Customer in my Azure SQL database. If I want to delete a particular row in this table from a Python program, I will add the following code snippet in my Python program:

import pyodbc
server = 'mysql1000.database.windows.net'
database = 'DemoDatabase'
username = 'azureadmin'
password = 'Test@123' 
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER= '+ driver + '; SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("DELETE FROM dbo.[New Customer] WHERE CustomerID < 10")

• In the above Python code, firstly, I am making a connection with the database.
• Then I am executing the DELETE statement in the database using a cursor.
• This DELETE statement will delete the records of the customers with the CustomerID less than 10.
• Let us see the table data after the execution.

You can see the desired records are deleted successfully.

Hence, in this way you can delete rows from a table in an Azure SQL database using a Python program.

Read: How to create SQL authentication user in Azure SQL database

CRUD operations in Azure SQL database using Python

In this section, you will see an example of a Python program in which I have defined the CRUD operations for an Azure SQL database.

CRUD operations mean creating, reading, updating, and deleting i.e. using the INSERT, SELECT, UPDATE, and DELETE statement respectively.

I will perform these CRUD operations on a table named dbo.Customers in my Azure SQL database. Look at the code written below:

#Importing the pyodbc library
import pyodbc

#Setting connection properties
server = 'mysql1000.database.windows.net'
database = 'DemoDatabase'
username = 'azureadmin'
password = 'Test@123' 
driver= '{ODBC Driver 17 for SQL Server}'

#Defining the CRUD operations functions
def select():
    id = int(input('Enter Customer ID: '))
    with pyodbc.connect('DRIVER= '+ driver + '; SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM dbo.Customers WHERE [Customer ID] = ?", id)
            row= cursor.fetchall()
            print('Customer ID: ', row[0][0])
            print('Customer Name: ', row[0][1])

def insert():
    id = int(input('Enter the Customer ID: '))
    name = input('Enter the Customer Name: ')
    with pyodbc.connect('DRIVER= '+ driver + '; SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
        with conn.cursor() as cursor:
            cursor.execute("INSERT INTO dbo.Customers([Customer ID], [Customer Name]) VALUES(?, ?)", id, name)
    print('Row Inserted')

def delete():
    id = int(input('Enter Customer ID: '))
    with pyodbc.connect('DRIVER= '+ driver + '; SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
        with conn.cursor() as cursor:
            cursor.execute("DELETE FROM dbo.Customers WHERE [Customer ID] = ?", id)
    print('Row Deleted')

def update():
    id = int(input('Enter the Customer ID: '))
    name = input('Enter the new Name: ')
    with pyodbc.connect('DRIVER= '+ driver + '; SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
        with conn.cursor() as cursor:
            cursor.execute("UPDATE dbo.Customers SET [Customer Name]= ? WHERE [Customer ID] = ?", name, id)
    print('Row Updated')

#Executing the final program
print('''Enter 1 for displaying record: 
Enter 2 for inserting a record
Enter 3 for deleting a record
Enter 4 for updating a record''')

choice = int(input('Enter a choice: '))
if choice == 1:
    select()
elif choice == 2:
    insert()
elif choice == 3:
    delete()
elif choice == 4:
    update()
else:
    print('Invalid Choice')

Let me explain the above code.

• Firstly, I am importing the pyodbc library so that I can make a connection from my Python program to the database.
• Secondly, I have defined the connection properties in some variables. I will use these variables while establishing a connection with the database.
• Then I have created separate functions for every CRUD operation i.e. insert(), select(), delete(), and update().
• Finally, I am taking the user input and calling the function on the basis of the user input.

You can see the execution of the above program. I have executed the program for all the test cases.

You may also like to read the following Azure SQL tutorials.

• Reset Password Azure SQL database
• Azure sql password validation failed
• Azure SQL database configure firewall
• Azure SQL database column encryption

Thus, after reading this article, you might have learned how you can connect to your Azure SQL database from a Python application and execute various T-SQL queries in the database.

• Python library to connect to Azure SQL database
• Python code to connect to Azure SQL database
• Python create table in Azure SQL database
• Python insert into Azure SQL database
• Python select Azure SQL
• Python update in Azure SQL database
• Python delete in Azure SQL database
• CRUD operations in Azure SQL database using Python