快速入门:使用 Python 连接到 Azure Database for MySQL 并查询其中的数据Quickstart: Use Python to connect and query data in Azure Database for MySQL

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

在本快速入门中,你将使用 Python 连接到 Azure Database for MySQL。In this quickstart, you connect to an Azure Database for MySQL by using Python. 然后使用 SQL 语句在 Mac、Ubuntu Linux 和 Windows 平台的数据库中查询、插入、更新和删除数据。You then use SQL statements to query, insert, update, and delete data in the database from Mac, Ubuntu Linux, and Windows platforms.

本主题假设你熟悉如何使用 Python 进行开发,但不熟悉 Azure Database for MySQL 的用法。This topic assumes that you're familiar with developing using Python, but you're new to working with Azure Database for MySQL.

先决条件Prerequisites

重要

确保已使用 Azure 门户Azure CLI 将服务器的防火墙规则添加到连接的 IP 地址Ensure the IP address you're connecting from has been added the server's firewall rules using the Azure portal or Azure CLI

安装 Python 和 MySQL 连接器Install Python and the MySQL connector

使用以下步骤在计算机上安装 Python 和用于 Python 的 MySQL 连接器:Install Python and the MySQL connector for Python on your computer by using the following steps:

备注

本快速入门使用原始 SQL 查询方法连接到 MySQL。This quickstart uses a raw SQL query approach to connect to MySQL. 如果使用的是 Web 框架,请对此框架使用建议的连接器,例如 mysqlclient for Django。If you're using a web framework, use the recommended connector for the framework, for example, mysqlclient for Django.

  1. 下载并安装适合自己 OS 的 Python 3.7 或更高版本Download and install Python 3.7 or above for your OS. 请确保将 Python 添加到 PATH,因为 MySQL 连接器需要它。Make sure to add Python to your PATH, because the MySQL connector requires that.

  2. 打开命令提示符或 bash shell,使用大写 V 开关运行 python -V,以便检查 Python 版本。Open a command prompt or bash shell, and check your Python version by running python -V with the uppercase V switch.

  3. 最新版本的 Python 中包含 pip 包安装程序。The pip package installer is included in the latest versions of Python. 通过运行 pip install -U pippip 更新为最新版本。Update pip to the latest version by running pip install -U pip.

    如果未安装 pip,则可使用 get-pip.py 下载并安装它。If pip isn't installed, you can download and install it with get-pip.py. 有关详细信息,请参阅安装For more information, see Installation.

  4. 使用 pip,安装用于 Python 的 MySQL 连接器及其依赖项:Use pip to install the MySQL connector for Python and its dependencies:

    pip install mysql-connector-python
    

    还可以从 mysql.com 安装用于 MySQL 的 Python 连接器。You can also install the Python connector for MySQL from mysql.com. 有关用于 Python 的 MySQL 连接器的详细信息,请参阅 MySQL Connector/Python Developer Guide(MySQL 连接器/Python 开发人员指南)。For more information about the MySQL Connector for Python, see the MySQL Connector/Python Developer Guide.

获取连接信息Get connection information

从 Azure 门户获取连接到 Azure Database for MySQL 所需的连接信息。Get the connection information you need to connect to Azure Database for MySQL from the Azure portal. 需要服务器名称、数据库名称和登录凭据。You need the server name, database name, and login credentials.

  1. 登录 Azure 门户Sign in to the Azure portal.

  2. 在门户搜索栏中,搜索并选择创建的 Azure Database for MySQL 服务器,如 mydemoserverIn the portal search bar, search for and select the Azure Database for MySQL server you created, such as mydemoserver.

    Azure Database for MySQL 服务器名称

  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.

    Azure Database for MySQL 服务器名称

运行 Python 示例Run the Python examples

对于本文中的每个代码示例:For each code example in this article:

  1. 在文本编辑器中创建新的文件。Create a new file in a text editor.

  2. 将代码示例添加到文件。Add the code example to the file. 在代码中,将 <mydemoserver><myadmin><mypassword><mydatabase> 占位符替换为 MySQL 服务器和数据库的值。In the code, replace the <mydemoserver>, <myadmin>, <mypassword>, and <mydatabase> placeholders with the values for your MySQL server and database.

  3. 将文件保存在项目文件夹中,扩展名为 .py,例如 C:\pythonmysql\createtable.py/home/username/pythonmysql/createtable.pySave the file in a project folder with a .py extension, such as C:\pythonmysql\createtable.py or /home/username/pythonmysql/createtable.py.

  4. 若要运行代码,请打开命令提示符或 bash shell,将目录更改为项目文件夹,例如 cd pythonmysqlTo run the code, open a command prompt or bash shell and change directory into your project folder, for example cd pythonmysql. 键入 python 命令,后跟文件名,例如 python createtable.py,然后按 Enter。Type the python command followed by the file name, for example python createtable.py, and press Enter.

    备注

    在 Windows 上,如果找不到 python.exe,则可能需要将 Python 路径添加到 PATH 环境变量中,或提供 python.exe 的完整路径,例如 C:\python27\python.exe createtable.pyOn Windows, if python.exe is not found, you may 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.

创建表并插入数据Create a table and insert data

通过以下代码连接到服务器和数据库,创建一个表,然后使用 INSERT SQL 语句加载数据。Use the following code to connect to the server and database, create a table, and load data by using an INSERT SQL statement.

该代码将导入 mysql.connector 库,并使用 connect() 函数连接到 Azure Database for MySQL,使用配置集合中的参数The code imports the mysql.connector library, and uses the connect() function to connect to Azure Database for MySQL using the arguments in the config collection. 该代码对连接使用游标,并通过 cursor.execute() 方法对 MySQL 数据库执行 SQL 查询。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.chinacloudapi.cn',
  'user':'<myadmin>@<mydemoserver>',
  '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.")

读取数据Read data

使用以下代码进行连接,并使用 SELECT SQL 语句读取数据。Use the following code to connect and read the data by using a SELECT SQL statement.

该代码将导入 mysql.connector 库,并使用 connect() 函数连接到 Azure Database for MySQL,使用配置集合中的参数The code imports the mysql.connector library, and uses the connect() function to connect to Azure Database for MySQL using the arguments in the config collection. 该代码对连接使用游标,并通过 cursor.execute() 方法对 MySQL 数据库执行 SQL 查询。The code uses a cursor on the connection, and the cursor.execute() method executes the SQL query against the MySQL database.

代码使用 fetchall() 方法读取数据行,将结果集保留在集合行中,并使用 for 迭代器对行进行循环操作。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.chinacloudapi.cn',
  'user':'<myadmin>@<mydemoserver>',
  '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.")

更新数据Update data

使用以下代码进行连接,并使用 UPDATE SQL 语句更新数据。Use the following code to connect and update the data by using an UPDATE SQL statement.

该代码将导入 mysql.connector 库,并使用 connect() 函数连接到 Azure Database for MySQL,使用配置集合中的参数The code imports the mysql.connector library, and uses the connect() function to connect to Azure Database for MySQL using the arguments in the config collection. 该代码对连接使用游标,并通过 cursor.execute() 方法对 MySQL 数据库执行 SQL 查询。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.chinacloudapi.cn',
  'user':'<myadmin>@<mydemoserver>',
  '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.")

删除数据Delete data

使用以下代码进行连接,并使用 DELETE SQL 语句删除数据。Use the following code to connect and remove data by using a DELETE SQL statement.

该代码将导入 mysql.connector 库,并使用 connect() 函数连接到 Azure Database for MySQL,使用配置集合中的参数The code imports the mysql.connector library, and uses the connect() function to connect to Azure Database for MySQL using the arguments in the config collection. 该代码对连接使用游标,并通过 cursor.execute() 方法对 MySQL 数据库执行 SQL 查询。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.chinacloudapi.cn',
  'user':'<myadmin>@<mydemoserver>',
  '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.")

后续步骤Next steps