Quickstart: Use Python to connect and query data in Azure Database for MySQL - Flexible Server

In this quickstart, you connect to Azure Database for MySQL Flexible Server by using Python. You then use SQL statements to query, insert, update, and delete data in the database from Mac, Ubuntu Linux, and Windows platforms.

This article assumes that you're familiar with developing using Python, but you're new to working with Azure Database for MySQL Flexible Server.

• An Azure account with an active subscription.

If you don't have an Azure subscription, create an Azure free account before you begin. Currently, with an Azure free account, you can try Azure Database for MySQL - Flexible Server free for 12 months. For more information, see Use an Azure free account to try Azure Database for MySQL - Flexible Server for free.

• An Azure Database for MySQL Flexible Server instance. To create an Azure Database for MySQL Flexible Server instance, refer to Quickstart: Create an instance of Azure Database for MySQL with the Azure portal or Quickstart: Create an instance of Azure Database for MySQL - Flexible Server by using the Azure CLI.

• If you created your flexible server with Private access (VNet Integration), you will need to connect to your server from a resource within the same VNet as your server. You can create a virtual machine and add it to the VNet created with your flexible server. Refer to Create and manage virtual networks for Azure Database for MySQL - Flexible Server using the Azure CLI.
• If you created your flexible server with Public access (allowed IP addresses), you can add your local IP address to the list of firewall rules on your server. Refer to Manage firewall rules for Azure Database for MySQL - Flexible Server using Azure CLI.

Install Python and the MySQL connector for Python on your computer by using the following steps:

1. Download and install Python 3.7 or above for your OS. Make sure to add Python to your PATH, because the MySQL connector requires that.

2. Open a command prompt or bash shell, and check your Python version by running python -V with the uppercase V switch.

3. The pip package installer is included in the latest versions of Python. Update pip to the latest version by running pip install -U pip.

   If pip isn't installed, you can download and install it with get-pip.py. For more information, see Installation.

4. Use pip to install the MySQL connector for Python and its dependencies:

   pip install mysql-connector-python
   

   You can also install the Python connector for MySQL from mysql.com. For more information about the MySQL Connector for Python, see the MySQL Connector/Python Developer Guide.

Get the connection information you need to connect to Azure Database for MySQL Flexible Server from the Azure portal. You need the server name, database name, and sign in credentials.

1. Sign in to the Azure portal.

2. In the portal search bar, search for and select the Azure Database for MySQL Flexible Server instance you created, such as mydemoserver.

3. From the server's Overview page, make a note of the Server name and Server admin login name. If you forget your password, you can also reset the password from this page.

For each code example in this article:

1. Create a new file in a text editor.

2. Add the code example to the file. In the code, replace the <mydemoserver>, <myadmin>, <mypassword>, and <mydatabase> placeholders with the values for your MySQL server and database.

3. Save the file in a project folder with a .py extension, such as C:\pythonmysql\createtable.py or /home/username/pythonmysql/createtable.py.

4. To run the code, open a command prompt or bash shell and change directory into your project folder, for example cd pythonmysql. Type the python command followed by the file name, for example python createtable.py, and press Enter.

   Note

   On Windows, if python.exe is not found, you might need to add the Python path into your PATH environment variable, or provide the full path to python.exe, for example C:\python27\python.exe createtable.py.

Use the following code to connect to the server and database, create a table, and load data by using an INSERT SQL statement.

The code imports the mysql.connector library, and uses the connect() function to connect to flexible server using the arguments in the config collection. The code uses a cursor on the connection, and the cursor.execute() method executes the SQL query against the MySQL database.

import mysql.connector
from mysql.connector import errorcode

# Obtain connection string information from the portal

config = {
  'host':'<mydemoserver>.mysql.database.azure.com',
  'user':'<myadmin>',
  'password':'<mypassword>',
  'database':'<mydatabase>'
}

# Construct connection string

try:
   conn = mysql.connector.connect(**config)
   print("Connection established")
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with the user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  cursor = conn.cursor()

# Drop previous table of same name if one exists
cursor.execute("DROP TABLE IF EXISTS inventory;")
print("Finished dropping table (if existed).")

# Create table
cursor.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
print("Finished creating table.")

# Insert some data into table
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("banana", 150))
print("Inserted",cursor.rowcount,"row(s) of data.")
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("orange", 154))
print("Inserted",cursor.rowcount,"row(s) of data.")
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("apple", 100))
print("Inserted",cursor.rowcount,"row(s) of data.")

# Cleanup
conn.commit()
cursor.close()
conn.close()
print("Done.")

Use the following code to connect and read the data by using a SELECT SQL statement.

The code imports the mysql.connector library, and uses the connect() function to connect to flexible server using the arguments in the config collection. The code uses a cursor on the connection, and the cursor.execute() method executes the SQL query against the MySQL database.

The code reads the data rows using the fetchall() method, keeps the result set in a collection row, and uses a for iterator to loop over the rows.

import mysql.connector
from mysql.connector import errorcode

# Obtain connection string information from the portal

config = {
  'host':'<mydemoserver>.mysql.database.azure.com',
  'user':'<myadmin>',
  'password':'<mypassword>',
  'database':'<mydatabase>'
}

# Construct connection string

try:
   conn = mysql.connector.connect(**config)
   print("Connection established")
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with the user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  cursor = conn.cursor()

  # Read data
  cursor.execute("SELECT * FROM inventory;")
  rows = cursor.fetchall()
  print("Read",cursor.rowcount,"row(s) of data.")

  # Print all rows
  for row in rows:
      print("Data row = (%s, %s, %s)" %(str(row[0]), str(row[1]), str(row[2])))

  # Cleanup
  conn.commit()
  cursor.close()
  conn.close()
  print("Done.")

Use the following code to connect and update the data by using an UPDATE SQL statement.

The code imports the mysql.connector library, and uses the connect() function to connect to flexible server using the arguments in the config collection. The code uses a cursor on the connection, and the cursor.execute() method executes the SQL query against the MySQL database.

import mysql.connector
from mysql.connector import errorcode

# Obtain connection string information from the portal

config = {
  'host':'<mydemoserver>.mysql.database.azure.com',
  'user':'<myadmin>',
  'password':'<mypassword>',
  'database':'<mydatabase>'
}

# Construct connection string

try:
   conn = mysql.connector.connect(**config)
   print("Connection established")
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with the user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  cursor = conn.cursor()

  # Update a data row in the table
  cursor.execute("UPDATE inventory SET quantity = %s WHERE name = %s;", (200, "banana"))
  print("Updated",cursor.rowcount,"row(s) of data.")

  # Cleanup
  conn.commit()
  cursor.close()
  conn.close()
  print("Done.")

Use the following code to connect and remove data by using a DELETE SQL statement.

The code imports the mysql.connector library, and uses the connect() function to connect to flexible server using the arguments in the config collection. The code uses a cursor on the connection, and the cursor.execute() method executes the SQL query against the MySQL database.

import mysql.connector
from mysql.connector import errorcode

# Obtain connection string information from the portal

config = {
  'host':'<mydemoserver>.mysql.database.azure.com',
  'user':'<myadmin>',
  'password':'<mypassword>',
  'database':'<mydatabase>'
}

# Construct connection string

try:
   conn = mysql.connector.connect(**config)
   print("Connection established.")
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with the user name or password.")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist.")
  else:
    print(err)
else:
  cursor = conn.cursor()

  # Delete a data row in the table
  cursor.execute("DELETE FROM inventory WHERE name=%(param1)s;", {'param1':"orange"})
  print("Deleted",cursor.rowcount,"row(s) of data.")

  # Cleanup
  conn.commit()
  cursor.close()
  conn.close()
  print("Done.")

• Connect to Azure Database for MySQL - Flexible Server with encrypted connections
• Connectivity and networking concepts for Azure Database for MySQL - Flexible Server
• Manage firewall rules for Azure Database for MySQL - Flexible Server using the Azure portal
• Create and manage virtual networks for Azure Database for MySQL - Flexible Server using the Azure portal