Quickstart: Use Python to connect and query data with Azure Database for MySQL

This article demonstrates how to use Python to connect to an Azure Database for MySQL. It uses SQL statements to query, insert, update, and delete data in the database from Mac OS, Ubuntu Linux, and Windows platforms.

This topic assumes that you are familiar with developing using Python and that you are new to working with Azure Database for MySQL.

Prerequisites

This quickstart uses the resources created in either of these guides as a starting point:

Install Python and the MySQL connector

Install Python and the MySQL connector for Python on your own machine. Depending on your platform, follow the steps in the appropriate section below.

Note

This quickstart uses a raw SQL query approach to connect to MySQL to run queries. If you are using a web framework, use the recommended connector for those frameworks. For example, mysqlclient is suggested for use with Django.

Windows

  1. Download and Install Python 3.7 from python.org.
  2. Check the Python installation by launching the command prompt. Run the command C:\python37\python.exe -V using the uppercase V switch to see the version number.
  3. Install the Python connector for MySQL from mysql.com corresponding to your version of Python.

Linux (Ubuntu)

  1. In Linux (Ubuntu), Python is typically installed as part of the default installation.

  2. Check the Python installation by launching the bash shell. Run the command python -V using the uppercase V switch to see the version number.

  3. Check the PIP installation by running the pip show pip -V command to see the version number.

  4. PIP may be included in some versions of Python. If PIP is not installed, you may install the PIP package, by running command sudo apt-get install python-pip.

  5. Update PIP to the latest version, by running the pip install -U pip command.

  6. Install the MySQL connector for Python, and its dependencies by using the PIP command:

    sudo pip install mysql-connector-python-rf
    

MacOS

  1. In Mac OS, Python is typically installed as part of the default OS installation.

  2. Check the Python installation by launching the bash shell. Run the command python -V using the uppercase V switch to see the version number.

  3. Check the PIP installation by running the pip show pip -V command to see the version number.

  4. PIP may be included in some versions of Python. If PIP is not installed, you may install the PIP package.

  5. Update PIP to the latest version, by running the pip install -U pip command.

  6. Install the MySQL connector for Python, and its dependencies by using the PIP command:

    pip install mysql-connector-python-rf
    

Get connection information

Get the connection information needed to connect to the Azure Database for MySQL. You need the fully qualified server name and login credentials.

  1. Log in to the Azure portal.
  2. From the left-hand menu in Azure portal, select All resources, and then search for the server you have created (such as mydemoserver).
  3. Select the server name.
  4. From the server's Overview panel, 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 panel. Azure Database for MySQL server name

Run Python code

  • Paste the code into a text file, and then save the file into a project folder with file extension .py (such as C:\pythonmysql\createtable.py or /home/username/pythonmysql/createtable.py).
  • To run the code, launch the command prompt or Bash shell. Change directory into your project folder cd pythonmysql. Then type the python command followed by the file name python createtable.py to run the application. On the Windows OS, if python.exe is not found, you may need to provide the full path to the executable or add the Python path into the path environment variable. C:\python27\python.exe createtable.py

Connect, create table, and insert data

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

In the code, the mysql.connector library is imported. The connect() function is used to connect to Azure Database for MySQL using the connection arguments in the config collection. The code uses a cursor on the connection, and method cursor.execute() executes the SQL query against MySQL database.

Replace the host, user, password, and database parameters with the values that you specified when you created the server and 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@mydemoserver',
  'password':'yourpassword',
  'database':'quickstartdb'
}

# 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.")

Read data

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

In the code, the mysql.connector library is imported. The connect() function is used to connect to Azure Database for MySQL using the connection arguments in the config collection. The code uses a cursor on the connection, and method cursor.execute() executes the SQL statement against MySQL database. The data rows are read using method fetchall(). The result set is kept in a collection row and a for iterator is used to loop over the rows.

Replace the host, user, password, and database parameters with the values that you specified when you created the server and 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@mydemoserver',
  'password':'yourpassword',
  'database':'quickstartdb'
}

# 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.")

Update data

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

In the code, the mysql.connector library is imported. The connect() function is used to connect to Azure Database for MySQL using the connection arguments in the config collection. The code uses a cursor on the connection, and method cursor.execute() executes the SQL statement against MySQL database.

Replace the host, user, password, and database parameters with the values that you specified when you created the server and 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@mydemoserver',
  'password':'yourpassword',
  'database':'quickstartdb'
}

# 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.")

Delete data

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

In the code, the mysql.connector library is imported. The connect() function is used to connect to Azure Database for MySQL using the connection arguments in the config collection. The code uses a cursor on the connection, and method cursor.execute() executes the SQL query against MySQL database.

Replace the host, user, password, and database parameters with the values that you specified when you created the server and 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@mydemoserver',
  'password':'yourpassword',
  'database':'quickstartdb'
}

# 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.")

Next steps