Insert data from file in Azure Database for MySQL

In this tutorial, you will create a simple database for a library and learn how to open and manage .txt files in Python. You will also use INSERT, SELECT, UPDATE and DELETE statements to manipulate data in Azure Database for MySQL. 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 db3.py. Copy the code from the previous tutorials to connect to your database and create a cursor object.

Create a table

The following code creates a table named library. The table has five columns:

• The id column is used to uniquely identify a row (PRIMARY KEY) and it is an integer (INT).
• The data type of the title, author and genre columns is VARCHAR which stands for variable length character data.
• The quantity column specifies the number of the available books.

1

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

Open the file and insert data

A file Input/Output (IO) requires three steps:

1. Open the file using the open() function.
2. Read or write data.
3. Close the file using the close() function.

Assume that we have the books.txt file located in the same folder as the Python program. You can use a with statement and the open() function to open the file.

1
2
3

fname = 'books.txt'
with open(fname) as fh:
    # Your code

Then use a for loop to read the file line-by-line, the strip() function to remove the newline character at the end of each line and the split() function to split the string into a list. To load data, use the INSERT SQL statement. As you learned in the previous tutorial, 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. You can use a %s marker to specify the parameters in the SQL statement and add a tuple containing the values.

Use the following code to open the file and insert data into the table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

fname = 'books.txt'
with open(fname) as fh:
    for line in fh:
        parts = line.strip().split(", ")
        title = parts[0]
        author = parts[1]
        genre = parts[2]
        quantity = parts[3]
        # Insert some data into table
        cursor.execute("INSERT INTO library (title, author, genre, quantity) VALUES (%s, %s, %s, %s)", (title, author, genre, quantity))

Read data

In the previous tutorial, you learned that 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()

You can use the .rowcount property to print the number of rows returned by a SELECT statement.

1

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

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

1
2
3

for row in rows:
  print(str(row[0]) + ". " + row[1] + " by " + row[2])
  print("Genre: " + row[3] + " Quantity: " + str(row[4]))

Update data

The next example shows how to update an existing record using an UPDATE SQL statement. It reduces by 1 the quantity of the book with id equal to 18. All other rows are left unchanged.

1

cursor.execute("UPDATE library SET quantity = quantity - 1 WHERE id = %s", (18, ))

The following code increases by 1 the quantity of the books with id equal to 10 or 22. All other rows are left unchanged.

1

cursor.execute("UPDATE library SET quantity = quantity + 1 WHERE id = %s OR id = %s", (10, 22))

You can also use the .rowcount property to print the numbers of rows affected by the UPDATE statement.

1

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

Commit the changes and close the connection

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

1
2
3

conn.commit()
cursor.close()
conn.close()