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 Database for MySQL using Python.
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 (Insert Python dataframe into Azure SQL table) 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
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.
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
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.
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.
cursor.execute("UPDATE movies " "SET genre = ? " "WHERE movie_title = ?", ("Biography", "The Theory of Everything"))
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
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.
cursor.execute("SELECT " "movie_id, movie_title, genre " "FROM movies " "WHERE movie_title = ?", ("The Theory of Everything", )) rows = cursor.fetchall() print(rows)
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.
cursor.execute("DELETE FROM movies " "WHERE genre = ?", ("Horror", )) print("Deleted",cursor.rowcount,"row(s) of data.")
WHERE clause in the above
DELETE statement. If you omit the
WHERE clause, all records in a table will be deleted!
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
# 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
SUM, etc. to perform calculations on a set of values. Aggregate functions return a single value.
MAX() function, find and print the gross of the highest-grossing movie of Martin Scorsese.
# Read data cursor.execute("SELECT MAX(gross) " "FROM movies " "WHERE director = ? ", ("Martin Scorsese", )) rows = cursor.fetchall() # Print data print(rows)
Close the connection
Use the following code to close the cursor and the connection.
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.