Connect to and query an Azure SQL database

Jul 17, 2021 2 min read

Prerequisites

Open a Microsoft Azure account.
Create an Azure SQL database

Install pyodbc on Windows

In a command prompt window, run: pip install pyodbc

Add a table to your Azure SQL database

Use the Python pyodbc library to create, populate, and delete a table in your Azure SQL database.

Python code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
import pyodbc   # import the pyodbc library

server = 'tcp:myserver011.database.windows.net' 
database = 'MyDatabase' 
username = 'MyUsername'
password = '**********'  # insert password
connStr = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password
cnxn = pyodbc.connect(connStr, autocommit=True)  # autocommit avoids explicit cdxn.commit() after each execute().
cursor = cnxn.cursor()

# create table with columns (declare column type)
cursor.execute("""
CREATE TABLE myTable 
    (Title TEXT, 
    Author TEXT, 
    Format TEXT, 
    Price DECIMAL (4,2))
""")

# insert rows in table
cursor.execute("""
INSERT INTO myTable VALUES ('Dark sacred night', 'Michael Connelly', 'paperback', 12.99)
INSERT INTO myTable VALUES ('The hungry tide', 'Amitav Ghosh', 'paperback', 13.99)
INSERT INTO myTable VALUES ('Watership down', 'Richard Adams', 'paperback', 11.98)
""")

# add row
cursor.execute("INSERT INTO myTable (Title, Author, Format) VALUES ('The storm', 'Tomás González', 'paperback')")

# update data
cursor.execute("UPDATE myTable SET Price = 16.00 WHERE CONVERT(VARCHAR, Title) = 'The storm'")

# delete data
cursor.execute("DELETE FROM myTable WHERE CONVERT(VARCHAR, Title) = 'The storm'")

#delete table
cursor.execute("DROP TABLE IF EXISTS myTable")

Microsoft Azure Query editor

You can use the Microsoft Azure Query editor to query your SQL database:

Sign into your Microsoft Azure account.
Search for your database in ‘Recent resources’ or select the ‘SQL databases’ button:
Select Query editor and then sign-in:
Select Tables and expand the node to see your tables:
Expand your table node to see column names and types:
To see the data in your table, ‘Select Top 1000 Rows’ from the ellipsis menu:
Run the auto-generated query:
To run a different query, select ‘New Query’:
Enter a new query (1) and select ‘Run’ (2):
Check the Messages window to see the status of the query (3).
To see the updated table, ‘Select Top 1000 Rows’ again from the ellipsis menu:
To update data, select ‘New Query’ (1), enter a query (2), and run the query (3):
Check the Messages window to see the status of the query (4).
To see the updated table, ‘Select Top 1000 Rows’ again from the ellipsis menu: