使用 Amazon Redshift Python 连接器的示例

PDFRSS

以下为如何使用 Amazon Redshift Python 连接器的示例。要运行它们，您必须先安装 Python 连接器。有关安装 Amazon Redshift Python 连接器的更多信息，请参阅安装 Amazon Redshift Python 连接器。有关可以与 Python 连接器一起使用的配置选项的更多信息，请参阅 Amazon Redshift Python 连接器的配置选项。

使用 AWS 凭证连接到 Amazon Redshift 集群并进行查询

下面的示例将指导您使用 AWS 凭证连接到 Amazon Redshift 集群，然后查询表并检索查询结果。

#Connect to the cluster
>>> import redshift_connector
>>> conn = redshift_connector.connect(
     host='examplecluster.abc123xyz789.us-west-1.redshift.amazonaws.com',
     database='dev',
     port=5439,
     user='awsuser',
     password='my_password'
  )
  
# Create a Cursor object
>>> cursor = conn.cursor()

# Query a table using the Cursor
>>> cursor.execute("select * from book")
                
#Retrieve the query result set
>>> result: tuple = cursor.fetchall()
>>> print(result)
 >> (['One Hundred Years of Solitude', 'Gabriel García Márquez'], ['A Brief History of Time', 'Stephen Hawking'])
                

启用自动提交

根据 Python 数据库 API 规范，默认情况下自动提交属性处于关闭状态。在执行回滚命令后，您可以使用以下命令打开连接的 autocommit 属性，以确保事务不在进行中。

#Connect to the cluster
>>> import redshift_connector
>>> conn = redshift_connector.connect(...)

# Run a rollback command
>>>  conn.rollback()

# Turn on autocommit
>>>  conn.autocommit = True
>>>  conn.run("VACUUM")

# Turn off autocommit
>>>  conn.autocommit = False

配置游标参数样式

可以通过 cursor.paramstyle 修改游标的参数样式。使用的原定设置参数样式是 format。参数样式的有效值为 qmark、numeric、named、format 和 pyformat。

以下是使用各种参数样式将参数传递给示例 SQL 语句的示例。

# qmark
redshift_connector.paramstyle = 'qmark'
sql = 'insert into foo(bar, jar) VALUES(?, ?)'
cursor.execute(sql, (1, "hello world"))

# numeric
redshift_connector.paramstyle = 'numeric'
sql = 'insert into foo(bar, jar) VALUES(:1, :2)'
cursor.execute(sql, (1, "hello world"))

# named
redshift_connector.paramstyle = 'named'
sql = 'insert into foo(bar, jar) VALUES(:p1, :p2)'
cursor.execute(sql, {"p1":1, "p2":"hello world"})

# format
redshift_connector.paramstyle = 'format'
sql = 'insert into foo(bar, jar) VALUES(%s, %s)'
cursor.execute(sql, (1, "hello world"))

# pyformat
redshift_connector.paramstyle = 'pyformat'
sql = 'insert into foo(bar, jar) VALUES(%(bar)s, %(jar)s)'
cursor.execute(sql, {"bar": 1, "jar": "hello world"})

使用 COPY 从 Amazon S3 桶中复制数据，然后使用 UNLOAD 将数据写入该桶

以下示例说明如何将数据从 Amazon S3 桶复制到表中，然后从该表卸载回到此桶中。

包含以下数据的名为 category_csv.txt 的文本文件将上载到 Amazon S3 桶中。

12,Shows,Musicals,Musical theatre
13,Shows,Plays,"All ""non-musical"" theatre"
14,Shows,Opera,"All opera, light, and ""rock"" opera"
15,Concerts,Classical,"All symphony, concerto, and choir concerts"

以下是 Python 代码的示例，该代码首先连接到 Amazon Redshift 数据库。然后创建一个名为 category 的表并将 S3 桶中的 CSV 数据复制到表中。

#Connect to the cluster and create a Cursor
>>> import redshift_connector
>>> with redshift_connector.connect(...) as conn:
>>> with conn.cursor() as cursor:

#Create an empty table
>>>     cursor.execute("create table category (catid int, cargroup varchar, catname varchar, catdesc varchar)")

#Use COPY to copy the contents of the S3 bucket into the empty table 
>>>     cursor.execute("copy category from 's3://testing/category_csv.txt' iam_role 'arn:aws:iam::123:role/RedshiftCopyUnload' csv;")

#Retrieve the contents of the table
>>>     cursor.execute("select * from category")
>>>     print(cursor.fetchall())

#Use UNLOAD to copy the contents of the table into the S3 bucket
>>>     cursor.execute("unload ('select * from category') to 's3://testing/unloaded_category_csv.txt'  iam_role 'arn:aws:iam::123:role/RedshiftCopyUnload' csv;")

#Retrieve the contents of the bucket
>>>     print(cursor.fetchall())
 >> ([12, 'Shows', 'Musicals', 'Musical theatre'], [13, 'Shows', 'Plays', 'All "non-musical" theatre'], [14, 'Shows', 'Opera', 'All opera, light, and "rock" opera'], [15, 'Concerts', 'Classical', 'All symphony, concerto, and choir concerts'])

如果您未将 autocommit 设置为 true，请在运行 execute() 语句后使用 conn.commit() 提交。

数据卸载到 S3 桶的 unloaded_category_csv.text0000_part00 文件中，内容如下：

12,Shows,Musicals,Musical theatre
13,Shows,Plays,"All ""non-musical"" theatre"
14,Shows,Opera,"All opera, light, and ""rock"" opera"
15,Concerts,Classical,"All symphony, concerto, and choir concerts"