Manipulate data in Azure SQL Database using Python and Jupyter Notebook

This tutorial will show you how to manipulate and query data in an Azure SQL database using Python and Jupyter Notebooks in Visual Studio Code. To complete the exercise, you will need to install:

  • Python 3 (make sure to add Python to your PATH),
  • Visual Studio Code,
  • Microsoft ODBC Driver for SQL Server and the pyodbc library,
  • Jupyter Notebook and Jupyter Extension for Visual Studio Code,
  • the Python pandas package using pip.

If you need help with installing these tools, follow the instructions in the tutorial Connect to Azure SQL Database using Python and Jupyter Notebook.

You can download the code from my GitHub repository.

Create a notebook

Open Visual Studio Code and create a new Jupyter file named manipulate-data-sql.ipynb. Copy the code from the previous tutorial to connect to your Azure SQL database and create a cursor object.

Insert a new movie

The following code adds a new movie in the table using the INSERT statement.

In an INSERT statement, you specify a table and columns in an INTO clause, and a list of values to be stored in these columns in a VALUES clause.
1
2
3
4
5
6
7

cursor.execute("INSERT INTO movies "
    "(movie_title, released_year, runtime, genre, rating, director, "
    "star1, star2, number_of_votes, gross) "
    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
    ("The Imitation Game", 2014, 114, "Biography", 80,
    "Morten Tyldum", "Benedict Cumberbatch", "Keira Knightley",
    685201, 91125683))

Commit the changes

It is important to use the commit() method after every transaction that modifies data.

1

conn.commit()

Read data

You can read data by using a SELECT statement. Use the following code to select the id, the title and the director of all the movies in the table and read the data using the fetchall() method.

1
2
3
4

cursor.execute("SELECT "
    "movie_id, movie_title, director "
    "FROM movies")
rows = cursor.fetchall()

Then you can use a simple for loop to iterate over the rows and print the data.

1
2

for row in rows:
  print(row)

Update an existing record

In SQL we use the UPDATE statement to modify existing records in a table. The next example shows how to update the genre of a movie.

1
2
3
4

cursor.execute("UPDATE movies "
    "SET genre = ? "
    "WHERE movie_title = ?",
    ("Biography", "The Theory of Everything"))
Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies that only the movie “The Theory of Everything” should be updated. If you omit the WHERE clause, all the records in the table will be updated!

Then you can use the .rowcount property to print the number of rows updated by the previous UPDATE statement.

1

print("Updated",cursor.rowcount,"row(s) of data.")

In the next cell of your notebook, you can use the following code to print the genre of the movie “The Theory of Everything” to verify that you have successfully updated the genre.

1
2
3
4
5
6
7

cursor.execute("SELECT "
    "movie_id, movie_title, genre "
    "FROM movies "
    "WHERE movie_title = ?",
    ("The Theory of Everything", ))
rows = cursor.fetchall()
print(rows[0])

Delete a movie

You can use a DELETE statement to delete existing records in a table. The following code deletes all the horror movies and prints the number of the movies that have been removed from the table.

1
2
3
4

cursor.execute("DELETE FROM movies "
    "WHERE genre = ?",
    ("Horror", ))
print("Deleted",cursor.rowcount,"row(s) of data.")
Notice the WHERE clause in the above DELETE statement. If you omit the WHERE clause, all records in a table will be deleted!

Order data

Now that you have learned the basics, you can perform complex queries. Select all the action movies from the movies table that released between 2009 and 2019, sorted ascending by the released_year and descending by the gross.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# Read data
cursor.execute("SELECT movie_title, released_year, genre, gross "
    "FROM movies "
    "WHERE genre = ? AND released_year BETWEEN ? AND ? "
    "ORDER BY released_year ASC, gross DESC",
    ("Action", 2009, 2019))
rows = cursor.fetchall()
# Print data
for row in rows:
    print(row)

Use aggregate functions

In SQL you can use aggregate function, such as MIN, MAX, COUNT, AVG, SUM, etc. to perform calculations on a set of values. Aggregate functions return a single value.

Using the MAX() function, find and print the gross of the highest-grossing movie of Martin Scorsese.

1
2
3
4
5
6
7
8

# Read data
cursor.execute("SELECT MAX(gross) "
    "FROM movies "
    "WHERE director = ? ",
    ("Martin Scorsese", ))
rows = cursor.fetchall()
# Print data
print(rows[0])

Close the connection

Use the following code to close the cursor and the connection.

1
2

cursor.close()
conn.close()

Next steps

Congratulations! In the “Azure SQL Database and Python” tutorial series, you learned how to create an Azure SQL Database and connect to the database, create tables, manipulate, and query data by using Python and Jupyter Notebooks.

If you are interested in getting started with Azure databases and Python, you can read the tutorial series that I have written about how to manipulate and query data in Azure Database for MySQL by using Python.

Foteini Savvidou

Written By

Foteini Savvidou

I am an Electrical and Computer Engineering student at Aristotle University of Thessaloniki, Greece, interested in AI, IoT, Wireless Communications, and Cloud Technologies, as well as in applications of technology in healthcare and education. I am also Microsoft AI MVP and Gold Microsoft Learn Student Ambassador.

You May Also Like