Use Python to manipulate data in Azure Database for MySQL

This tutorial will show you how to use Python to create tables and manipulate data in Azure Database for MySQL. You will learn how to insert, read, update, and delete data. To complete the exercise, you will need to install:

• Python 3 (make sure to add Python to your PATH)
• Visual Studio Code or other code editor
• MySQL Connector for Python

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 of this tutorial from my GitHub repository.

Instructions

Open Visual Studio Code and create a new Python file named db2.py. Copy the code from the previous tutorial to connect to your database.

1
2
3
4
5
6
7
8

import mysql.connector
# Establish the connection
conn = mysql.connector.connect(
  user = '<username>@<server>', 
  password = '<password>', 
  host = '<server>.mysql.database.azure.com', 
  database = '<demodb>'
)

Create a cursor object

Cursor objects can interact with the MySQL server and execute operations such as SQL statements. To create a cursor, use the cursor() method of the conn object.

1

cursor = conn.cursor()

To execute SQL statements you can use the execute() method of the cursor object.

Create a table

The following example creates a new table named books. The items between the parentheses specify the details of each column. The table has four columns:

• The id column is used to uniquely identify a row (PRIMARY KEY) and it is an integer (INT). The AUTO_INCREMENT keyword allows a unique number to be created automatically when a new record is inserted into the table.

• The data type of the title, author and genre columns is VARCHAR which stands for variable length character data.

1

cursor.execute("CREATE TABLE books (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(50), author VARCHAR(50), genre VARCHAR(50))")

Insert data

To load data, use the INSERT SQL statement.

1

cursor.execute("INSERT INTO books (title, author, genre) VALUES ('East of Eden', 'John Steinbeck', 'Novel')")

It is possible to write the INSERT statement in a slightly different form. You can use a %s marker to specify the parameters in the SQL statement and add a tuple containing the values.

1

cursor.execute("INSERT INTO books (title, author, genre) VALUES (%s, %s, %s)", ("East of Eden", "John Steinbeck", "Novel"))

Commit the changes

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

1

conn.commit()

Insert more data

Use the following code to add three more books into the table.

1
2
3

cursor.execute("INSERT INTO books (title, author, genre) VALUES (%s, %s, %s)", ("The Alchemist", "Paulo Coelho", "Novel"))
cursor.execute("INSERT INTO books (title, author, genre) VALUES (%s, %s, %s)", ("The Picture of Dorian Gray", "Oscar Wilde", "Drama"))
cursor.execute("INSERT INTO books (title, author, genre) VALUES (%s, %s, %s)", ("1984", "George Orwell", "Novel"))

Read data

You can read the data by using a SELECT statement. The following code selects all the rows of the table and reads the data using the fetchall() method.

1
2

cursor.execute("SELECT * FROM books")
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)

Insert multiple rows at a time

You can use the executemany() method to insert multiple rows at once. You specify the parameters in a list and the executemany() method executes a SQL command against all parameter sequences found in that list.

Run the following code to insert 11 new books into the books table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

sql = "INSERT INTO books (title, author, genre) VALUES (%s, %s, %s)"
values = [
  ("The Grapes of Wrath", "John Steinbeck", "Novel"),
  ("Of Mice and Men", "John Steinbeck", "Novel"),
  ("The Great Gatsby", "F. Scott Fitzgerald", "Novel"),
  ("Animal Farm", "George Orwell", "Political satire"),
  ("The Adventures of Huckleberry Finn", "Mark Twain", "Novel"),
  ("Little Women", "Louisa May Alcott", "Novel"),
  ("Hamlet", "William Shakespeare", "Tragedy"),
  ("The Stranger", "Albert Camus", "Novel"),
  ("Farmer Giles of Ham", "J. R. R. Tolkien", "Children's literature"),
  ("Moby Dick", "Herman Melville", "Novel"),
  ("The Lord of the Rings", "J. R. R. Tolkien", "Fantasy")
]
cursor.executemany(sql, values)

Update a data row in the table

The next example shows how to update an existing record using an UPDATE SQL statement. It modifies the genre of the book “Animal Farm”. All other rows are left unchanged.

1

cursor.execute("UPDATE books SET genre = %s WHERE title = %s", ("Allegorical novella", "Animal Farm"))

Delete data

You can use a DELETE statement to delete existing records in a table. The following code removes the book “Hamlet” from the table.

1

cursor.execute("DELETE FROM books WHERE title = %s", ("Hamlet", ))

In a WHERE clause, you can use operators such as the OR operator to specify which records will be deleted. For example, the following statement deletes all the books by J. R. R. Tolkien and Mark Twain.

1

cursor.execute("DELETE FROM books WHERE author = %s OR author = %s", ("J. R. R. Tolkien", "Mark Twain"))

Close the connection

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

1
2

cursor.close()
conn.close()

Next steps

In the next tutorial, you will build a simple database for a small library and learn how to insert data from a .txt file and manipulate data in Azure Database for MySQL by using Python.