快速入门:使用 Python 连接到 Azure Database for PostgreSQL 并查询其中的数据 - 单一服务器Quickstart: Use Python to connect and query data in Azure Database for PostgreSQL - Single Server

本快速入门介绍如何使用 macOS、Ubuntu Linux 或 Windows 上的 Python 来处理 Azure Database for PostgreSQL。In this quickstart, you work with an Azure Database for PostgreSQL using Python on macOS, Ubuntu Linux, or Windows. 本快速入门介绍了如何连接数据库和使用 SQL 语句查询、插入、更新和删除数据。The quickstart shows how to connect to the database and use SQL statements to query, insert, update, and delete data. 本文假设你熟悉如何使用 Python,但不熟悉如何使用 Azure Database for PostgreSQL。The article assumes that you're familiar with Python, but new to working with Azure Database for PostgreSQL.

先决条件Prerequisites

安装适用于 PostgreSQL 的 Python 库Install the Python libraries for PostgreSQL

psycopg2 模块可连接到 PostgreSQL 数据库并对其进行查询,并可作为 Linux、macOS 或 Windows 包提供。The psycopg2 module enables connecting to and querying a PostgreSQL database, and is available as a Linux, macOS, or Windows wheel package. 安装二进制版本的模块,包括所有依赖项。Install the binary version of the module, including all the dependencies. 有关 psycopg2 安装和要求的详细信息,请参阅安装For more information about psycopg2 installation and requirements, see Installation.

若要安装 psycopg2,请打开终端或命令提示符并运行命令 pip install psycopg2To install psycopg2, open a terminal or command prompt and run the command pip install psycopg2.

获取数据库连接信息Get database connection information

连接到 Azure Database for PostgreSQL 数据库需要完全限定的服务器名称和登录凭据。Connecting to an Azure Database for PostgreSQL database requires the fully qualified server name and login credentials. 可以从 Azure 门户获取此信息。You can get this information from the Azure portal.

  1. Azure 门户中,搜索 Azure Database for PostgreSQL 服务器名称并选择该名称。In the Azure portal, search for and select your Azure Database for PostgreSQL server name.

  2. 在服务器的“概述”页上,复制完全限定的“服务器名称”和“管理员用户名” 。On the server's Overview page, copy the fully qualified Server name and the Admin username. 完全限定的“服务器名称”始终采用“<我的服务器名>.postgres.database.chinacloudapi.cn”的格式,“管理员用户名”始终采用“<我的管理员用户名>@<我的服务器名>”的格式 。The fully qualified Server name is always of the form <my-server-name>.postgres.database.chinacloudapi.cn, and the Admin username is always of the form <my-admin-username>@<my-server-name>.

    你还需要管理员密码。You also need your admin password. 如果忘记,可以从此页重置它。If you forget it, you can reset it from this page.

    Azure Database for PostgreSQL 服务器名称

如何运行 Python 示例How to 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. 在代码中,进行以下替换:In the code, replace:

    • <server-name><admin-username> 替换为从 Azure 门户复制的值。<server-name> and <admin-username> with the values you copied from the Azure portal.
    • <admin-password> 替换为服务器密码。<admin-password> with your server password.
    • <database-name> 替换为 Azure Database for PostgreSQL 数据库的名称。<database-name> with the name of your Azure Database for PostgreSQL database. 创建服务器时,会自动创建一个名为 postgres 的默认数据库。A default database named postgres was automatically created when you created your server. 你可以重命名该数据库,或使用 SQL 命令创建新的数据库。You can rename that database or create a new database by using SQL commands.
  3. 将文件保存在具有 .py 扩展名的项目文件夹(如 postgres-insert.py)中 。Save the file in your project folder with a .py extension, such as postgres-insert.py. 对于 Windows,确保在保存文件时选择 UTF-8 编码。For Windows, make sure UTF-8 encoding is selected when you save the file.

  4. 若要运行该文件,请在命令行界面中切换到项目文件夹,然后键入 python 后跟文件名,例如 python postgres-insert.pyTo run the file, change to your project folder in a command-line interface, and type python followed by the filename, for example python postgres-insert.py.

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

下面的代码示例使用 psycopg2.connect 函数连接到 Azure Database for PostgreSQL 数据库,并使用 SQL INSERT 语句加载数据。The following code example connects to your Azure Database for PostgreSQL database using the psycopg2.connect function, and loads data with a SQL INSERT statement. cursor.execute 函数对数据库执行 SQL 查询。The cursor.execute function executes the SQL query against the database.

import psycopg2

# Update connection string information 
host = "<server-name>"
dbname = "<database-name>"
user = "<admin-username>"
password = "<admin-password>"
sslmode = "require"

# Construct connection string
conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host, user, dbname, password, sslmode)
conn = psycopg2.connect(conn_string) 
print("Connection established")

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 a table
cursor.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
print("Finished creating table")

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

# Clean up
conn.commit()
cursor.close()
conn.close()

如果代码成功运行,则会生成以下输出:When the code runs successfully, it produces the following output:

命令行输出

读取数据Read data

下面的代码示例连接到 Azure Database for PostgreSQL 数据库,并使用 cursor.execute 和 SQL SELECT 语句来读取数据。The following code example connects to your Azure Database for PostgreSQL database and uses cursor.execute with the SQL SELECT statement to read data. 此函数可接受查询,并返回可使用 cursor.fetchall() 循环访问的结果集。This function accepts a query and returns a result set to iterate over by using cursor.fetchall().

import psycopg2

# Update connection string information
host = "<server-name>"
dbname = "<database-name>"
user = "<admin-username>"
password = "<admin-password>"
sslmode = "require"

# Construct connection string
conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host, user, dbname, password, sslmode)
conn = psycopg2.connect(conn_string) 
print("Connection established")

cursor = conn.cursor()

# Fetch all rows from table
cursor.execute("SELECT * FROM inventory;")
rows = cursor.fetchall()

# 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()

更新数据Update data

下面的代码示例连接到 Azure Database for PostgreSQL 数据库,并使用 cursor.execute 和 SQL UPDATE 语句来更新数据。The following code example connects to your Azure Database for PostgreSQL database and uses cursor.execute with the SQL UPDATE statement to update data.

import psycopg2

# Update connection string information
host = "<server-name>"
dbname = "<database-name>"
user = "<admin-username>"
password = "<admin-password>"
sslmode = "require"

# Construct connection string
conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host, user, dbname, password, sslmode)
conn = psycopg2.connect(conn_string) 
print("Connection established")

cursor = conn.cursor()

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

# Cleanup
conn.commit()
cursor.close()
conn.close()

删除数据Delete data

下面的代码示例连接到 Azure Database for PostgreSQL 数据库,并使用 cursor.execute 和 SQL DELETE 语句来删除以前插入的库存项。The following code example connects to your Azure Database for PostgreSQL database and uses cursor.execute with the SQL DELETE statement to delete an inventory item that you previously inserted.

import psycopg2

# Update connection string information
host = "<server-name>"
dbname = "<database-name>"
user = "<admin-username>"
password = "<admin-password>"
sslmode = "require"

# Construct connection string
conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host, user, dbname, password, sslmode)
conn = psycopg2.connect(conn_string) 
print("Connection established")

cursor = conn.cursor()

# Delete data row from table
cursor.execute("DELETE FROM inventory WHERE name = %s;", ("orange",))
print("Deleted 1 row of data")

# Cleanup
conn.commit()
cursor.close()
conn.close()

后续步骤Next steps