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

本快速入门演示了如何使用 Ruby 应用程序连接到 Azure Database for PostgreSQL。This quickstart demonstrates how to connect to an Azure Database for PostgreSQL using a Ruby application. 同时还介绍了如何使用 SQL 语句在数据库中查询、插入、更新和删除数据。It shows how to use SQL statements to query, insert, update, and delete data in the database. 本文中的步骤假定你熟悉如何使用 Ruby 进行开发,但不熟悉如何使用 Azure Database for PostgreSQL。The steps in this article assume that you are familiar with developing using Ruby, and 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:

还需要安装:You also need to have installed:

  • RubyRuby
  • Ruby pg(适用于 Ruby 的 PostgreSQL 模块)Ruby pg, the PostgreSQL module for Ruby

获取连接信息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

Note

Azure Postgres 用户名中的 @ 符号已在所有连接字符串中以 url 编码为 %40The @ symbol in the Azure Postgres username has been url encoded as %40 in all the connection strings.

进行连接并创建表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.

该代码使用 PG::Connection 对象和构造函数 new() 来连接到 Azure Database for PostgreSQL。The code uses a PG::Connection object with constructor new() to connect to Azure Database for PostgreSQL. 然后调用 exec() 方法,以便运行 DROP、CREATE TABLE 和 INSERT INTO 命令。Then it calls method exec() to run the DROP, CREATE TABLE, and INSERT INTO commands. 代码使用 PG::Error 类来检查是否存在错误。The code checks for errors using the PG::Error class. 然后,它会调用方法 close(),在终止之前关闭连接。Then it calls method close() to close the connection before terminating.

hostdatabaseuserpassword 字符串替换为你自己的值。Replace the host, database, user, and password strings with your own values.

require 'pg'

begin
    # Initialize connection variables.
    host = String('mydemoserver.postgres.database.chinacloudapi.cn')
    database = String('postgres')
    user = String('mylogin%40mydemoserver')
    password = String('<server_admin_password>')

    # Initialize connection object.
    connection = PG::Connection.new(:host => host, :user => user, :dbname => database, :port => '5432', :password => password)
    puts 'Successfully created connection to database'

    # Drop previous table of same name if one exists
    connection.exec('DROP TABLE IF EXISTS inventory;')
    puts 'Finished dropping table (if existed).'

    # Drop previous table of same name if one exists.
    connection.exec('CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);')
    puts 'Finished creating table.'

    # Insert some data into table.
    connection.exec("INSERT INTO inventory VALUES(1, 'banana', 150)")
    connection.exec("INSERT INTO inventory VALUES(2, 'orange', 154)")
    connection.exec("INSERT INTO inventory VALUES(3, 'apple', 100)")
    puts 'Inserted 3 rows of data.'

rescue PG::Error => e
    puts e.message 
    
ensure
    connection.close if connection
end

读取数据Read data

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

该代码使用 PG::Connection 对象和构造函数 new() 来连接到 Azure Database for PostgreSQL。The code uses a PG::Connection object with constructor new() to connect to Azure Database for PostgreSQL. 然后,它会调用方法 exec() 来运行 SELECT 命令,将结果保存在结果集中。Then it calls method exec() to run the SELECT command, keeping the results in a result set. 结果集集合使用 resultSet.each do 循环进行循环访问,将最新的行值保存在 row 变量中。The result set collection is iterated over using the resultSet.each do loop, keeping the current row values in the row variable. 代码使用 PG::Error 类来检查是否存在错误。The code checks for errors using the PG::Error class. 然后,它会调用方法 close(),在终止之前关闭连接。Then it calls method close() to close the connection before terminating.

hostdatabaseuserpassword 字符串替换为你自己的值。Replace the host, database, user, and password strings with your own values.

require 'pg'

begin
    # Initialize connection variables.
    host = String('mydemoserver.postgres.database.chinacloudapi.cn')
    database = String('postgres')
    user = String('mylogin%40mydemoserver')
    password = String('<server_admin_password>')

    # Initialize connection object.
    connection = PG::Connection.new(:host => host, :user => user, :database => dbname, :port => '5432', :password => password)
    puts 'Successfully created connection to database.'

    resultSet = connection.exec('SELECT * from inventory;')
    resultSet.each do |row|
        puts 'Data row = (%s, %s, %s)' % [row['id'], row['name'], row['quantity']]
    end

rescue PG::Error => e
    puts e.message 
    
ensure
    connection.close if connection
end

更新数据Update data

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

该代码使用 PG::Connection 对象和构造函数 new() 来连接到 Azure Database for PostgreSQL。The code uses a PG::Connection object with constructor new() to connect to Azure Database for PostgreSQL. 然后,它会调用方法 exec() 来运行 UPDATE 命令。Then it calls method exec() to run the UPDATE command. 代码使用 PG::Error 类来检查是否存在错误。The code checks for errors using the PG::Error class. 然后,它会调用方法 close(),在终止之前关闭连接。Then it calls method close() to close the connection before terminating.

hostdatabaseuserpassword 字符串替换为你自己的值。Replace the host, database, user, and password strings with your own values.

require 'pg'

begin
    # Initialize connection variables.
    host = String('mydemoserver.postgres.database.chinacloudapi.cn')
    database = String('postgres')
    user = String('mylogin%40mydemoserver')
    password = String('<server_admin_password>')

    # Initialize connection object.
    connection = PG::Connection.new(:host => host, :user => user, :dbname => database, :port => '5432', :password => password)
    puts 'Successfully created connection to database.'

    # Modify some data in table.
    connection.exec('UPDATE inventory SET quantity = %d WHERE name = %s;' % [200, '\'banana\''])
    puts 'Updated 1 row of data.'

rescue PG::Error => e
    puts e.message 
    
ensure
    connection.close if connection
end

删除数据Delete data

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

该代码使用 PG::Connection 对象和构造函数 new() 来连接到 Azure Database for PostgreSQL。The code uses a PG::Connection object with constructor new() to connect to Azure Database for PostgreSQL. 然后,它会调用方法 exec() 来运行 UPDATE 命令。Then it calls method exec() to run the UPDATE command. 代码使用 PG::Error 类来检查是否存在错误。The code checks for errors using the PG::Error class. 然后,它会调用方法 close(),在终止之前关闭连接。Then it calls method close() to close the connection before terminating.

hostdatabaseuserpassword 字符串替换为你自己的值。Replace the host, database, user, and password strings with your own values.

require 'pg'

begin
    # Initialize connection variables.
    host = String('mydemoserver.postgres.database.chinacloudapi.cn')
    database = String('postgres')
    user = String('mylogin%40mydemoserver')
    password = String('<server_admin_password>')

    # Initialize connection object.
    connection = PG::Connection.new(:host => host, :user => user, :dbname => database, :port => '5432', :password => password)
    puts 'Successfully created connection to database.'

    # Modify some data in table.
    connection.exec('DELETE FROM inventory WHERE name = %s;' % ['\'orange\''])
    puts 'Deleted 1 row of data.'

rescue PG::Error => e
    puts e.message 
    
ensure
    connection.close if connection
end

后续步骤Next steps