變更 Python 和 Perl 應用程式，以支援從 Microsoft SQL Server 遷移至 Amazon Aurora PostgreSQL 相容版本

請依照這些程式碼轉換步驟，將您的應用程式遷移至 PostgreSQL。

下列語彙提供 Python 和 Perl 應用程式部分這些轉換任務的詳細說明。

針對遷移的每個步驟使用檢查清單。

將以下內容新增至應用程式遷移每個步驟的檢查清單，包括最後一個步驟：

分析現有的 Python 程式碼基礎。

您的分析應包含下列項目，以促進應用程式遷移程序：

轉換資料庫連線以支援 PostgreSQL。

大多數 Python 應用程式使用 pyodbc 程式庫來連接 SQL Server 資料庫，如下所示。

import pyodbc
....
try:
    conn_string = "Driver=ODBC Driver 17 for SQL
    Server;UID={};PWD={};Server={};Database={}".format (conn_user, conn_password,
    conn_server, conn_database)
    conn = pyodbc.connect(conn_string)
    cur = conn.cursor()
    result = cur.execute(query_string)
    for row in result:
    print (row)
except Exception as e:
    print(str(e))

轉換資料庫連線以支援 PostgreSQL，如下所示。

import pyodbc
import psycopg2
....
try:
    conn_string = ‘postgresql+psycopg2://’+
    conn_user+’:’+conn_password+’@’+conn_server+’/’+conn_database
    conn = pyodbc.connect(conn_string, connect_args={‘options’:’-csearch_path=dbo’})
    cur = conn.cursor()
    result = cur.execute(query_string)
    for row in result:
    print (row)
except Exception as e:
    print(str(e))

將內嵌 SQL 查詢變更為 PostgreSQL。

將您的內嵌 SQL 查詢轉換為 PostgreSQL 相容格式。例如，下列 SQL Server 查詢會從資料表擷取字串。

dtype = “type1”
stm = ‘“SELECT TOP 1 searchcode FROM TypesTable (NOLOCK)
WHERE code=”’ + “’” + str(dtype) + “’”
# For Microsoft SQL Server Database Connection
engine = create_engine(‘mssql+pyodbc:///?odbc_connect=%s’ % urllib.parse.quote_plus(conn_string), connect_args={‘connect_timeout’:login_timeout})
conn = engine_connect()
rs = conn.execute(stm)
for row in rs:
    print(row)

轉換後，PostgreSQL 相容內嵌 SQL 查詢如下所示。

dtype = “type1”
stm = ‘“SELECT searchcode FROM TypesTable
WHERE code=”’ + “’” + str(dtype) + “’ LIMIT 1”
# For PostgreSQL Database Connection
engine = create_engine(‘postgres+psycopg2://%s’ %conn_string, connect_args={‘connect_timeout’:login_timeout})
conn = engine.connect()
rs = conn.execute(stm)
for row in rs:
    print(row)

處理動態 SQL 查詢。

動態 SQL 可以存在於一個指令碼或多個 Python 指令碼中。先前範例示範如何使用 Python 的字串取代函數插入變數，以建構動態 SQL 查詢。替代方法是在適用的情況下使用變數附加查詢字串。 

在下列範例中，查詢字串會根據函數傳回的值，即時建構。

query = ‘“SELECT id from equity e join issues i on e.permId=i.permId where e.id’”
query += get_id_filter(ids) + “ e.id is NOT NULL

這些類型的動態查詢在應用程式遷移期間非常常見。請依照下列步驟處理動態查詢：

處理結果集、變數和資料影格。

對於 Microsoft SQL Server，您可以使用 Python 方法，例如 fetchall()fetchone()或 從資料庫擷取結果集。您也可以使用 fetchmany(size)並指定要從結果集傳回的記錄數目。若要這樣做，您可以使用 pyodbc 連線物件，如下列範例所示。

pyodbc (Microsoft SQL Server)

import pyodbc 
server = 'tcp:myserver.database.windows.net' 
database = 'exampledb' 
username = 'exampleusername' 
password = 'examplepassword' 
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = conn.cursor()
cursor.execute("SELECT * FROM ITEMS") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

在 Aurora 中，若要執行類似任務，例如連線至 PostgreSQL 和擷取結果集，您可以使用 psycopg2 或 SQLAlchemy。這些 Python 程式庫提供連線模組和游標物件，以周遊 PostgreSQL 資料庫記錄，如下列範例所示。

psycopg2 (Aurora PostgreSQL 相容）

import psycopg2
query = "SELECT * FROM ITEMS;"
//Initialize variables
host=dbname=user=password=port=sslmode=connect_timeout=""
connstring = "host='{host}' dbname='{dbname}' user='{user}' \
password='{password}'port='{port}'".format(host=host,dbname=dbname,\
user=user,password=password,port=port) 
conn = psycopg2.connect(connstring)
cursor = conn.cursor()
cursor.execute(query)
column_names = [column[0] for column in cursor.description]
print("Column Names: ", column_names)
print("Column values: "
for row in cursor:
    print("itemid :", row[0])
    print("itemdescrption :", row[1])
    print("itemprice :", row[3]))

SQLAlchemy (Aurora PostgreSQL 相容）

from sqlalchemy import create_engine
from pandas import DataFrame
conn_string = 'postgresql://core:database@localhost:5432/exampledatabase'
engine = create_engine(conn_string)
conn = engine.connect()
dataid = 1001
result = conn.execute("SELECT * FROM ITEMS")
df = DataFrame(result.fetchall())
df.columns = result.keys()
df = pd.DataFrame()
engine.connect()
df = pd.read_sql_query(sql_query, engine, coerce_float=False)
print(“df=”, df)

在遷移期間和之後測試您的應用程式。

測試遷移的 Python 應用程式是持續進行的程序。由於遷移包含連線物件變更 (psycopg2 或 SQLAlchemy)、錯誤處理、新功能 （資料框架）、內嵌 SQL 變更、大量複製功能 (bcp而非 COPY) 和類似的變更，因此必須在應用程式遷移期間和之後仔細測試。檢查：

分析現有的 Perl 程式碼基礎。

您的分析應包含下列項目，以促進應用程式遷移程序。您應該識別：

轉換 Perl 應用程式和 DBI 模組的連線以支援 PostgreSQL。

Perl 型應用程式通常使用 Perl DBI 模組，這是 Perl 程式設計語言的標準資料庫存取模組。您可以使用相同的 DBI 模組搭配 SQL Server 和 PostgreSQL 的不同驅動程式。

如需必要 Perl 模組、安裝和其他說明的詳細資訊，請參閱 DBD：：Pg 文件。下列範例會連線至 上的 Aurora PostgreSQL 相容exampletest-aurorapg-database.cluster-sampleclusture.us-east.-rds.amazonaws.com。

#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $hostname = “exampletest-aurorapg-database-sampleclusture.us-east.rds.amazonaws.com”
my $dsn = "DBI:$driver: dbname = $hostname;host = 127.0.0.1;port = 5432";
my $username = "postgres";
my $password = "pass123";
$dbh = DBI->connect("dbi:Pg:dbname=$hostname;host=$host;port=$port;options=$options",
      $username,
      $password,
      {AutoCommit => 0, RaiseError => 1, PrintError => 0}
      );

將內嵌 SQL 查詢變更為 PostgreSQL。

您的應用程式可能具有與 SELECT、UPDATE、 DELETE和類似陳述式的內嵌 SQL 查詢，其中包含 PostgreSQL 不支援的查詢子句。例如，PostgreSQL NOLOCK 不支援查詢關鍵字，例如 TOP和 。下列範例示範如何處理 TOP、 NOLOCK和布林值變數。

在 SQL Server 中：

$sqlStr = $sqlStr
. "WHERE a.student_id in (SELECT TOP $numofRecords c_student_id   \
FROM active_student_record b WITH (NOLOCK) \
INNER JOIN student_contributor c WITH (NOLOCK) on c.contributor_id = b.c_st)

對於 PostgreSQL，請轉換為：

$sqlStr = $sqlStr
. "WHERE a.student_id in (SELECT TOP $numofRecords c_student_id  \
FROM active_student_record b INNER JOIN student_contributor c  \
on c.contributor_id = b.c_student_contr_id WHERE b_current_1 is true \
LIMIT $numofRecords)"

處理動態 SQL 查詢和 Perl 變數。

動態 SQL 查詢是在應用程式執行時間建置的 SQL 陳述式。這些查詢會在應用程式執行時動態建構，視特定條件而定，因此在執行時間之前，不會知道查詢的全文。例如，財務分析應用程式每天分析前 10 個共享，這些共享每天都會變更。SQL 資料表是根據最佳執行者建立的，而且在執行時間之前不會知道這些值。

假設此範例的內嵌 SQL 查詢會傳遞至包裝函式，以取得變數中的結果集，然後變數會使用 條件來判斷資料表是否存在：

以下是變數處理的範例，後面接著此使用案例的 SQL Server 和 PostgreSQL 查詢。

my $tableexists = db_read( arg 1, $sql_qry, undef, 'writer');
my $table_already_exists = $tableexists->[0]{table_exists};
if ($table_already_exists){
# do some thing
}
else {
# do something else
}

SQL Server：

my $sql_qry = “SELECT OBJECT_ID('$backendTable', 'U') table_exists", undef, 'writer')";

PostgreSQL：

my $sql_qry = “SELECT TO_REGCLASS('$backendTable', 'U') table_exists", undef, 'writer')";

下列範例使用內嵌 SQL 中的 Perl 變數，該變數會使用 執行SELECT陳述式JOIN，以擷取資料表的主要索引鍵和索引鍵資料欄的位置。

SQL Server：

my $sql_qry = "SELECT column_name', character_maxi mum_length \
FROM INFORMATION_SCHEMA.COLUMNS \
WHERE TABLE_SCHEMA='$example_schemaInfo' \
AND TABLE_NAME='$example_table' \
AND DATA_TYPE IN ('varchar','nvarchar');";

PostgreSQL：

my $sql_qry = "SELECT c1.column_name, c1.ordinal_position \
FROM information_schema.key_column_usage AS c LEFT \
JOIN information_schema.table_constraints AS t1 \
ON t1.constraint_name = c1.constraint_name \
WHERE t1.table_name = $example_schemaInfo'.'$example_table’ \
AND t1.constraint_type = 'PRIMARY KEY' ;";

將其他 SQL Server 建構轉換為 PostgreSQL。

下列變更適用於所有應用程式，無論程式設計語言為何。

利用 AWS 服務來增強效能。

當您遷移至 AWS 雲端時，您可以改進應用程式和資料庫設計，以利用 AWS 服務。例如，如果來自連接至 Aurora PostgreSQL 相容資料庫伺服器的 Python 應用程式的查詢花費比原始 Microsoft SQL Server 查詢更長的時間，您可以考慮直接從 Aurora 伺服器建立歷史資料的摘要至 Amazon Simple Storage Service (Amazon S3) 儲存貯體，並使用以 Amazon Athena 為基礎的 SQL 查詢來產生使用者儀表板的報告和分析資料查詢。