使用 Amazon Redshift Python 連接器的範例

以下是如何使用 Amazon Redshift Python 連接器的範例。若要執行這些範例，您必須先安裝 Python 連接器。如需安裝 Amazon Redshift Python 連接器的相關資訊，請參閱安裝 Amazon Redshift Python 連接器。如需可與 Python 連接器搭配使用之組態選項的相關資訊，請參閱 Amazon Redshift Python 連接器的組態選項。

使用以下方式連接到並查詢 Amazon Redshift 叢集 AWS 登入資料

下列範例會引 Amazon Redshift 使用 AWS 認證，然後查詢資料表並擷取查詢結果。

#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

設定游標 paramstyle

游標的 paramstyle 可透過 cursor.paramstyle 進行修改。所使用的預設 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 資料庫。然後，它會建立名為的CSV資料表，category並將 S3 儲存貯體中的資料複製到資料表中。

#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"