Azure Database for PostgreSQL - 单一服务器:使用 Go 语言连接和查询数据Azure Database for PostgreSQL - Single Server: Use Go language to connect and query data

本快速入门演示如何使用以 Go 语言 (golang) 编写的代码连接到 Azure Database for PostgreSQL。This quickstart demonstrates how to connect to an Azure Database for PostgreSQL using code written in the Go language (golang). 同时还介绍了如何使用 SQL 语句在数据库中查询、插入、更新和删除数据。It shows how to use SQL statements to query, insert, update, and delete data in the database. 本文假设你熟悉如何使用 Go 进行开发,但不熟悉如何使用 Azure Database for PostgreSQL。This article assumes you are familiar with development using Go, but that you are new to working with Azure Database for PostgreSQL.

先决条件Prerequisites

此快速入门使用以下任意指南中创建的资源作为起点:This quickstart uses the resources created in either of these guides as a starting point:

安装 Go 和 pq 连接器Install Go and pq connector

在自己的计算机上安装 Go纯 Go 语言 Postgres 驱动程序 (pq)Install Go and the Pure Go Postgres driver (pq) on your own machine. 根据自己的平台执行相应步骤:Depending on your platform, follow the appropriate steps:

WindowsWindows

  1. 根据安装说明下载和安装用于 Microsoft Windows 的 Go。Download and install Go for Microsoft Windows according to the installation instructions.

  2. 从开始菜单启动命令提示符。Launch the command prompt from the start menu.

  3. 为项目创建一个文件夹,例如 mkdir %USERPROFILE%\go\src\postgresqlgoMake a folder for your project, such as mkdir %USERPROFILE%\go\src\postgresqlgo.

  4. 将目录转到项目文件夹,例如 cd %USERPROFILE%\go\src\postgresqlgoChange directory into the project folder, such as cd %USERPROFILE%\go\src\postgresqlgo.

  5. 设置 GOPATH 的环境变量,使之指向源代码目录。Set the environment variable for GOPATH to point to the source code directory. set GOPATH=%USERPROFILE%\goset GOPATH=%USERPROFILE%\go.

  6. 通过运行 go get github.com/lib/pq 命令安装纯 Go 语言 Postgres 驱动程序 (pq)Install the Pure Go Postgres driver (pq) by running the go get github.com/lib/pq command.

    总起来说就是,安装 Go,然后在命令提示符处运行以下命令:In summary, install Go, then run these commands in the command prompt:

    mkdir  %USERPROFILE%\go\src\postgresqlgo
    cd %USERPROFILE%\go\src\postgresqlgo
    set GOPATH=%USERPROFILE%\go
    go get github.com/lib/pq
    

Linux (Ubuntu)Linux (Ubuntu)

  1. 启动 Bash Shell。Launch the Bash shell.

  2. 通过运行 sudo apt-get install golang-go 来安装 Go。Install Go by running sudo apt-get install golang-go.

  3. 在主目录中为项目创建一个文件夹,例如 mkdir -p ~/go/src/postgresqlgo/Make a folder for your project in your home directory, such as mkdir -p ~/go/src/postgresqlgo/.

  4. 将目录转到该文件夹,例如 cd ~/go/src/postgresqlgo/Change directory into the folder, such as cd ~/go/src/postgresqlgo/.

  5. 设置 GOPATH 环境变量,使之指向有效的源目录,例如当前主目录的 Go 文件夹。Set the GOPATH environment variable to point to a valid source directory, such as your current home directory's go folder. 在 Bash Shell 中运行 export GOPATH=~/go,将 Go 目录添加为当前 Shell 会话的 GOPATH。At the bash shell, run export GOPATH=~/go to add the go directory as the GOPATH for the current shell session.

  6. 通过运行 go get github.com/lib/pq 命令安装纯 Go 语言 Postgres 驱动程序 (pq)Install the Pure Go Postgres driver (pq) by running the go get github.com/lib/pq command.

    总起来说就是,运行以下 bash 命令:In summary, run these bash commands:

    sudo apt-get install golang-go
    mkdir -p ~/go/src/postgresqlgo/
    cd ~/go/src/postgresqlgo/
    export GOPATH=~/go/
    go get github.com/lib/pq
    

Apple macOSApple macOS

  1. 下载 Go,然后根据与平台相对应的安装说明进行安装。Download and install Go according to the installation instructions matching your platform.

  2. 启动 Bash Shell。Launch the Bash shell.

  3. 在主目录中为项目创建一个文件夹,例如 mkdir -p ~/go/src/postgresqlgo/Make a folder for your project in your home directory, such as mkdir -p ~/go/src/postgresqlgo/.

  4. 将目录转到该文件夹,例如 cd ~/go/src/postgresqlgo/Change directory into the folder, such as cd ~/go/src/postgresqlgo/.

  5. 设置 GOPATH 环境变量,使之指向有效的源目录,例如当前主目录的 Go 文件夹。Set the GOPATH environment variable to point to a valid source directory, such as your current home directory's go folder. 在 Bash Shell 中运行 export GOPATH=~/go,将 Go 目录添加为当前 Shell 会话的 GOPATH。At the bash shell, run export GOPATH=~/go to add the go directory as the GOPATH for the current shell session.

  6. 通过运行 go get github.com/lib/pq 命令安装纯 Go 语言 Postgres 驱动程序 (pq)Install the Pure Go Postgres driver (pq) by running the go get github.com/lib/pq command.

    总起来说就是,安装 Go,然后运行以下 bash 命令:In summary, install Go, then run these bash commands:

    mkdir -p ~/go/src/postgresqlgo/
    cd ~/go/src/postgresqlgo/
    export GOPATH=~/go/
    go get github.com/lib/pq
    

获取连接信息Get connection information

获取连接到 Azure Database for PostgreSQL 所需的连接信息。Get the connection information needed to connect to the Azure Database for PostgreSQL. 需要完全限定的服务器名称和登录凭据。You need the fully qualified server name and login credentials.

  1. 登录到 Azure 门户Log in to the Azure portal.
  2. 在 Azure 门户的左侧菜单中,单击“所有资源” ,然后搜索已创建的服务器(例如 mydemoserver )。From the left-hand menu in Azure portal, click All resources, and then search for the server you have created (such as mydemoserver).
  3. 单击服务器名称。Click 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 PostgreSQL 服务器名称Azure Database for PostgreSQL server name

生成和运行 Go 代码Build and run Go code

  1. 若要编写 Golang 代码,可以使用纯文本编辑器,例如 Microsoft Windows 中的记事本、Ubuntu 中的 viNano,或者 macOS 中的 TextEdit。To write Golang code, you can use a plain text editor, such as Notepad in Microsoft Windows, vi or Nano in Ubuntu, or TextEdit in macOS. 如果偏好功能更丰富的交互式开发环境 (IDE),可尝试 Jetbrains 推出的 GoLand、Microsoft 推出的 Visual Studio Code,或者 AtomIf you prefer a richer Interactive Development Environment (IDE) try GoLand by Jetbrains, Visual Studio Code by Microsoft, or Atom.
  2. 将以下部分中的 Golang 代码粘贴到文本文件中,再使用文件扩展名 *.go 将这些文件保存到项目文件夹中,例如 Windows 路径 %USERPROFILE%\go\src\postgresqlgo\createtable.go 或 Linux 路径 ~/go/src/postgresqlgo/createtable.goPaste the Golang code from the following sections into text files, and save into your project folder with file extension *.go, such as Windows path %USERPROFILE%\go\src\postgresqlgo\createtable.go or Linux path ~/go/src/postgresqlgo/createtable.go.
  3. 在代码中找到 HOSTDATABASEUSERPASSWORD 常量,将示例值替换为自己的值。Locate the HOST, DATABASE, USER, and PASSWORD constants in the code, and replace the example values with your own values.
  4. 启动命令提示符或 Bash Shell。Launch the command prompt or bash shell. 将目录转到项目文件夹。Change directory into your project folder. 例如,在 Windows 上使用 cd %USERPROFILE%\go\src\postgresqlgo\For example, on Windows cd %USERPROFILE%\go\src\postgresqlgo\. 在 Linux 上使用 cd ~/go/src/postgresqlgo/On Linux cd ~/go/src/postgresqlgo/. 提到的某些 IDE 环境提供不需 shell 命令的调试和运行时功能。Some of the IDE environments mentioned offer debug and runtime capabilities without requiring shell commands.
  5. 通过键入命令 go run createtable.go 来运行代码,编译并运行应用程序。Run the code by typing the command go run createtable.go to compile the application and run it.
  6. 另外,若要将代码内建到本机应用程序中,请键入命令 go build createtable.go,再启动 createtable.exe 来运行该应用程序。Alternatively, to build the code into a native application, go build createtable.go, then launch createtable.exe to run the application.

进行连接并创建表Connect and create a table

使用以下代码进行连接,使用 CREATE TABLE SQL 语句创建表,然后使用 INSERT INTO SQL 语句将行添加到表中。Use the following code to connect and create a table using CREATE TABLE SQL statement, followed by INSERT INTO SQL statements to add rows into the table.

代码将导入三个包:sql 包pq 包(充当与 PostgreSQL 服务器通信所需的驱动程序)以及 fmt 包(用于命令行中的打印输入和输出)。The code imports three packages: the sql package, the pq package as a driver to communicate with the PostgreSQL server, and the fmt package for printed input and output on the command line.

代码通过调用 sql.Open() 方法来连接到 Azure Database for PostgreSQL 数据库,并使用 db.Ping() 方法来检查连接。The code calls method sql.Open() to connect to Azure Database for PostgreSQL database, and checks the connection using method db.Ping(). 将始终使用数据库句柄来存储数据库服务器的连接池。A database handle is used throughout, holding the connection pool for the database server. 代码多次调用 Exec() 方法来运行多个 SQL 命令。The code calls the Exec() method several times to run several SQL commands. 每次都将使用自定义 checkError() 方法来检查是否发生错误,以及是否在发生错误时死机退出。Each time a custom checkError() method checks if an error occurred and panic to exit if an error does occur.

HOSTDATABASEUSERPASSWORD 参数替换为你自己的值。Replace the HOST, DATABASE, USER, and PASSWORD parameters with your own values.

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)

const (
    // Initialize connection constants.
    HOST     = "mydemoserver.postgres.database.chinacloudapi.cn"
    DATABASE = "mypgsqldb"
    USER     = "mylogin@mydemoserver"
    PASSWORD = "<server_admin_password>"
)

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {
    // Initialize connection string.
    var connectionString string = fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=require", HOST, USER, PASSWORD, DATABASE)

    // Initialize connection object.
    db, err := sql.Open("postgres", connectionString)
    checkError(err)

    err = db.Ping()
    checkError(err)
    fmt.Println("Successfully created connection to database")

    // Drop previous table of same name if one exists.
    _, err = db.Exec("DROP TABLE IF EXISTS inventory;")
    checkError(err)
    fmt.Println("Finished dropping table (if existed)")

    // Create table.
    _, err = db.Exec("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
    checkError(err)
    fmt.Println("Finished creating table")

    // Insert some data into table.
    sql_statement := "INSERT INTO inventory (name, quantity) VALUES ($1, $2);"
    _, err = db.Exec(sql_statement, "banana", 150)
    checkError(err)
    _, err = db.Exec(sql_statement, "orange", 154)
    checkError(err)
    _, err = db.Exec(sql_statement, "apple", 100)
    checkError(err)
    fmt.Println("Inserted 3 rows of data")
}

读取数据Read data

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

代码将导入三个包:sql 包pq 包(充当与 PostgreSQL 服务器通信所需的驱动程序)以及 fmt 包(用于命令行中的打印输入和输出)。The code imports three packages: the sql package, the pq package as a driver to communicate with the PostgreSQL server, and the fmt package for printed input and output on the command line.

代码通过调用 sql.Open() 方法来连接到 Azure Database for PostgreSQL 数据库,并使用 db.Ping() 方法来检查连接。The code calls method sql.Open() to connect to Azure Database for PostgreSQL database, and checks the connection using method db.Ping(). 将始终使用数据库句柄来存储数据库服务器的连接池。A database handle is used throughout, holding the connection pool for the database server. select 查询通过调用方法 db.Query() 来运行,生成的行保留在类型为的变量中。The select query is run by calling method db.Query(), and the resulting rows are kept in a variable of type rows. 代码使用 rows.Scan() 方法来读取当前行中的列数据值,并使用迭代器 rows.Next() 来循环访问相关行,直到再也没有行存在。The code reads the column data values in the current row using method rows.Scan() and loops over the rows using the iterator rows.Next() until no more rows exist. 每行的列值列显到控制台。每次都将使用自定义 checkError() 方法检查是否发生错误,以及是否在发生错误时死机退出。Each row's column values are printed to the console out. Each time a custom checkError() method is used to check if an error occurred and panic to exit if an error does occur.

HOSTDATABASEUSERPASSWORD 参数替换为你自己的值。Replace the HOST, DATABASE, USER, and PASSWORD parameters with your own values.

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)

const (
    // Initialize connection constants.
    HOST     = "mydemoserver.postgres.database.chinacloudapi.cn"
    DATABASE = "mypgsqldb"
    USER     = "mylogin@mydemoserver"
    PASSWORD = "<server_admin_password>"
)

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {

    // Initialize connection string.
    var connectionString string = fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=require", HOST, USER, PASSWORD, DATABASE)

    // Initialize connection object.
    db, err := sql.Open("postgres", connectionString)
    checkError(err)

    err = db.Ping()
    checkError(err)
    fmt.Println("Successfully created connection to database")

    // Read rows from table.
    var id int
    var name string
    var quantity int

    sql_statement := "SELECT * from inventory;"
    rows, err := db.Query(sql_statement)
    checkError(err)
    defer rows.Close()

    for rows.Next() {
        switch err := rows.Scan(&id, &name, &quantity); err {
        case sql.ErrNoRows:
            fmt.Println("No rows were returned")
        case nil:
            fmt.Printf("Data row = (%d, %s, %d)\n", id, name, quantity)
        default:
            checkError(err)
        }
    }
}

更新数据Update data

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

代码导入三个包:sql 包pq 包(充当与 Postgres 服务器通信所需的驱动程序)以及 fmt 包(适用于在命令行中列显的输入和输出)。The code imports three packages: the sql package, the pq package as a driver to communicate with the Postgres server, and the fmt package for printed input and output on the command line.

代码通过调用 sql.Open() 方法来连接到 Azure Database for PostgreSQL 数据库,并使用 db.Ping() 方法来检查连接。The code calls method sql.Open() to connect to Azure Database for PostgreSQL database, and checks the connection using method db.Ping(). 将始终使用数据库句柄来存储数据库服务器的连接池。A database handle is used throughout, holding the connection pool for the database server. 代码通过调用 Exec() 方法来运行 SQL 语句,以便更新表。The code calls the Exec() method to run the SQL statement that updates the table. 使用自定义 checkError() 方法检查是否发生错误,以及是否在发生错误时死机退出。A custom checkError() method is used to check if an error occurred and panic to exit if an error does occur.

HOSTDATABASEUSERPASSWORD 参数替换为你自己的值。Replace the HOST, DATABASE, USER, and PASSWORD parameters with your own values.

package main

import (
  "database/sql"
  _ "github.com/lib/pq"
  "fmt"
)

const (
    // Initialize connection constants.
    HOST     = "mydemoserver.postgres.database.chinacloudapi.cn"
    DATABASE = "mypgsqldb"
    USER     = "mylogin@mydemoserver"
    PASSWORD = "<server_admin_password>"
)

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {
    
    // Initialize connection string.
    var connectionString string = 
        fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=require", HOST, USER, PASSWORD, DATABASE)

    // Initialize connection object.
    db, err := sql.Open("postgres", connectionString)
    checkError(err)

    err = db.Ping()
    checkError(err)
    fmt.Println("Successfully created connection to database")

    // Modify some data in table.
    sql_statement := "UPDATE inventory SET quantity = $2 WHERE name = $1;"
    _, err = db.Exec(sql_statement, "banana", 200)
    checkError(err)
    fmt.Println("Updated 1 row of data")
}

删除数据Delete data

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

代码导入三个包:sql 包pq 包(充当与 Postgres 服务器通信所需的驱动程序)以及 fmt 包(适用于在命令行中列显的输入和输出)。The code imports three packages: the sql package, the pq package as a driver to communicate with the Postgres server, and the fmt package for printed input and output on the command line.

代码通过调用 sql.Open() 方法来连接到 Azure Database for PostgreSQL 数据库,并使用 db.Ping() 方法来检查连接。The code calls method sql.Open() to connect to Azure Database for PostgreSQL database, and checks the connection using method db.Ping(). 将始终使用数据库句柄来存储数据库服务器的连接池。A database handle is used throughout, holding the connection pool for the database server. 代码通过调用 Exec() 方法来运行 SQL 语句,以便从表中删除行。The code calls the Exec() method to run the SQL statement that deletes a row from the table. 使用自定义 checkError() 方法检查是否发生错误,以及是否在发生错误时死机退出。A custom checkError() method is used to check if an error occurred and panic to exit if an error does occur.

HOSTDATABASEUSERPASSWORD 参数替换为你自己的值。Replace the HOST, DATABASE, USER, and PASSWORD parameters with your own values.

package main

import (
  "database/sql"
  _ "github.com/lib/pq"
  "fmt"
)

const (
    // Initialize connection constants.
    HOST     = "mydemoserver.postgres.database.chinacloudapi.cn"
    DATABASE = "mypgsqldb"
    USER     = "mylogin@mydemoserver"
    PASSWORD = "<server_admin_password>"
)

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {
    
    // Initialize connection string.
    var connectionString string = 
        fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=require", HOST, USER, PASSWORD, DATABASE)

    // Initialize connection object.
    db, err := sql.Open("postgres", connectionString)
    checkError(err)

    err = db.Ping()
    checkError(err)
    fmt.Println("Successfully created connection to database")

    // Delete some data from table.
    sql_statement := "DELETE FROM inventory WHERE name = $1;"
    _, err = db.Exec(sql_statement, "orange")
    checkError(err)
    fmt.Println("Deleted 1 row of data")
}

后续步骤Next steps