本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。
更改 Python 和 Perl 应用程序以支持数据库从微软SQL服务器迁移到亚马逊 Aurora Postgre SQL-兼容版
由 Dwarika Patra (AWS) 和 Deepesh Jayaprakash () 创作 AWS
环境：PoC 或试点 | 来源：SQL服务器 | 目标：Aurora Postgre SQL-兼容 |
R 类型：更换平台 | 工作负载：Microsoft；开源 | 技术：迁移；数据库 |
AWS服务：亚马逊 Aurora |
Summary
此模式描述了将数据库从微软SQL服务器迁移到亚马逊 Aurora Postgre SQL 兼容版时可能需要对应用程序存储库进行更改。该模式假设这些应用程序基于 Python 或 Perl，并为这些脚本语言提供单独的指令。
将SQL服务器数据库迁移到 Aurora Postgre SQL-Compatible 涉及架构转换、数据库对象转换、数据迁移和数据加载。由于 Postgre SQL 和 SQL Server 之间的差异（与数据类型、连接对象、语法和逻辑有关），因此最困难的迁移任务是对代码库进行必要的更改，使其在 Post SQL gre 中正常运行。
对于基于 Python 的应用程序，连接对象和类分散在整个系统中。此外，Python 代码库可能使用多个库来连接到数据库。如果数据库连接接口发生变化，运行应用程序内联查询的对象也需要更改。
对于基于 Perl 的应用程序，更改涉及连接对象、数据库连接驱动程序、静态和动态内联SQL语句，以及应用程序如何处理复杂的动态DML查询和结果集。
迁移应用程序时，还可以考虑可能的增强功能AWS，例如使用亚马逊简单存储服务 (Amazon S3) 访问权限替换FTP服务器。
应用程序迁移过程涉及以下挑战：
连接对象。如果连接对象分散在包含多个库和函数调用的代码中，则可能需要找到一种通用的方法来更改它们以支持 Post SQL gre。
记录检索或更新期间的错误或者异常处理。如果您对数据库执行有条件的创建、读取、更新和删除 (CRUD) 操作，这些操作会返回变量、结果集或数据框，则任何错误或异常都可能导致具有级联效果的应用程序错误。应通过适当的验证和保存点来仔细处理这些问题。其中一个保存点是在BEGIN...EXCEPTION...END块内调用大型内联SQL查询或数据库对象。
控制事务及其验证。其中包括手动和自动提交与回滚。Perl 的 Postgre SQL 驱动程序要求你始终明确设置自动提交属性。
处理动态SQL查询。这需要对查询逻辑和迭代测试有深入的了解，以确保查询按预期工作。
性能。您应该确保代码更改不会导致应用程序性能下降。
此模式详细解释了转换进程。
先决条件和限制
先决条件
Python 和 Perl 语法工作知识。
SQL服务器和 Postgr SQL e 的基本技能。
了解现有的应用程序架构。
访问您的应用程序代码、SQL服务器数据库和 Postgre SQL 数据库。
使用开发、测试和验证应用程序更改的凭证访问 Windows 或 Linux (或其他 Unix)开发环境。
对于基于 Python 的应用程序，您的应用程序可能需要的标准 Python 库，例如用于处理数据帧的 Pandas，以及用于数据库连接的 psycopg2。SQLAlchemy
对于基于 Perl 应用程序，需要带有依赖库或模块的 Perl 包。综合 Perl 存档网络 (CPAN) 模块可以支持大多数应用程序要求。
所有必需依赖自定义库或模块。
用于SQL服务器读取权限和对 Aurora 进行读/写访问的数据库凭据。
Post SQL gre 用于验证和调试服务和用户的应用程序更改。
在应用程序迁移期间访问开发工具，例如 Visual Studio Code、Sublime Text 或pgAdmin。
限制
某些 Python 或 Perl 版本、模块、库以及包与云环境不兼容。
SQL服务器上使用的某些第三方库和框架无法替换以支持 Postgre SQL 迁移。
性能变化可能需要更改应用程序、内联 Transact-SQL (T-SQL) 查询、数据库函数和存储过程。
Postgre SQL 支持表名、列名和其他数据库对象使用小写名称。
某些数据类型（例如UUID列）仅以小写形式存储。Python 和 Perl 应用程序必须要处理此类大小写差异。
必须使用 Postgre SQL 数据库中相应文本列的正确数据类型来处理字符编码差异。
产品版本
架构
源技术堆栈
目标技术堆栈
迁移架构
工具
AWS服务和工具
其他工具
操作说明
| 任务 | 描述 | 所需技能 |
|---|
按照以下代码转换步骤将您的应用程序迁移到 Postgre。SQL | 为 Postgre 设置特定于数据库的ODBC驱动程序和库。SQL例如，你可以将其中一个CPAN模块用于 Perl 和 p yodbc、psy copg2 或 Python。SQLAlchemy 通过使用这些库连接到 Aurora Postgre SQL-Compatible 来转换数据库对象。 在现有应用程序模块中应用代码更改以获得兼容的 T SQL 语句。 在应用程序代码中重写数据库特定的函数调用与存储过程。 处理对用于内联SQL查询的应用程序变量及其数据类型的更改。 处理不兼容的数据库专用函数。 完成对已转换的用于数据库迁移的应用程序代码的 end-to-end测试。 将来自 Microsoft SQL 服务器的结果与你迁移到 Postgre SQL 的应用程序进行比较。 在 Microsoft SQL 服务器和 Post SQL gre 之间执行应用程序性能基准测试。 修改应用程序调用的存储过程或内联 T SQL 语句以提高性能。
以下操作说明详细说明了 Python 和 Perl 应用程序的一些转换任务。 | 应用程序开发人员 |
为迁移的每个步骤使用清单。 | 将以下内容添加到应用程序迁移的每个步骤（包括最后一步）的清单中： 查看 Postgre SQL 文档，确保您的所有更改都与 Postgre SQL 标准兼容。 检查列的整数值与浮点值。 确定插入、更新和提取的行数，以及列名和日期/时间戳。您可以使用 diff 实用程序或编写脚本自动执行这些检查。 完成大型内联SQL语句的性能检查，并检查应用程序的整体性能。 使用多个 try/catch 块检查数据库操作的错误处理是否正确以及程序正常退出。 检查以确保适当的日志记录流程到位。
| 应用程序开发人员 |
| 任务 | 描述 | 所需技能 |
|---|
分析现有的 Python 代码库。 | 您的分析应包含以下内容，以简化应用程序迁移过程： 识别代码的所有连接对象。 识别所有不兼容的内联SQL查询（例如 T SQL 语句和存储过程）并分析所需的更改。 查看您的代码文档并追踪控制流以了解代码功能。稍后当您测试应用程序性能或负载比较时，这将很有帮助。 了解应用程序用途，以便在数据库转换后对其进行有效测试。大多数可通过数据库迁移进行转换的 Python 应用程序要么是将数据从其他来源加载到数据库表的订阅源，要么是从表中检索数据并将其转换为适合创建报告或API调用以执行验证的不同输出格式（例如CSVJSON、或平面文件）的提取器。
| 应用程序开发人员 |
转换您的数据库连接以支持 Postgre。SQL | 大多数 Python 应用程序使用 pyodbc 库来连接SQL服务器数据库，如下所示。 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查询更改为 Postgre。SQL | 将您的内联SQL查询转换为与 Postgre SQL 兼容的格式。例如，以下SQL服务器查询从表中检索字符串。 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)
转换后，与 Postgre SQL 兼容的内联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
这些类型的动态查询在应用程序迁移过程中非常常见。请按照以下步骤处理动态查询： 检查整体语法(例如，带有子句的 SELECT 语 JOIN 句的语法)。 验证查询中使用的所有变量或列名，例如 i 和 id。 检查查询中使用的函数、参数和返回值（例如 get_id_filter 及其参数 ids）。
| 应用程序开发人员 |
处理结果集、变量与数据框。 | 对于 Microsoft SQL 服务器，你可以使用 Python 方法（例如fetchone()或fetchall()）从数据库中检索结果集。您也可以使用 fetchmany(size) 并指定要从结果集中返回的记录数。为此，您可使用 pyodbc 连接对象，如以下示例中所示。 pyodbc（微软SQL服务器） 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 中，要执行类似的任务，例如连接到 Postgre SQL 和获取结果集，你可以使用 psy copg2 或。SQLAlchemy这些 Python 库提供了连接模块和游标对象，用于遍历 Postgre SQL 数据库记录，如下例所示。 psycopg2（Aurora Postgre-兼容）SQL 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 Postgr SQL e） 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 代码库。 | 您的分析应包含以下内容，以简化应用程序迁移过程。您应确定： 任何INI或基于配置的代码 特定于数据库的标准开放数据库连接 (ODBC) Perl 驱动程序或任何自定义驱动程序 内联查询和 T 型SQL查询需要更改代码 各种 Perl 模块之间的交互（例如，由多个功能组件调用或使用的单个 Perl ODBC 连接对象） 数据集与结果集处理 外部依赖 Perl 库 应用程序中APIs使用的任何内容 Perl 版本兼容性以及与 Aurora Post SQL gre 的驱动程序兼容性——兼容
| 应用程序开发人员 |
转换来自 Perl 应用程序和DBI模块的连接以支持 Post SQL gre。 | 基于 Perl 的应用程序通常使用 Perl DBI 模块，它是 Perl 编程语言的标准数据库访问模块。对于SQL服务器和 Postgr SQL e，你可以使用带有不同驱动程序的同一个DBI模块。 有关所需的 Perl 模块、安装和其他说明的更多信息，请参阅DBD:: Pg 文档。以下示例连接到 Aurora Postgre SQL-Compatible，网址为。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查询更改为 Postgre。SQL | 您的应用程序可能包含带有SELECT、DELETEUPDATE、和类似语句的内联SQL查询，其中包含 Postgre SQL 不支持的查询子句。例如，Postgr SQL e NOLOCK 不支持诸如TOP和之类的查询关键字。以下示例说明如何处理 TOP、NOLOCK 和 Boolean 变量。 在SQL服务器中： $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服务器和 Postgre SQL 查询。 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服务器： my $sql_qry = “SELECT OBJECT_ID('$backendTable', 'U') table_exists", undef, 'writer')";
Postgre: SQL my $sql_qry = “SELECT TO_REGCLASS('$backendTable', 'U') table_exists", undef, 'writer')";
以下示例使用了 inline 中的 Perl 变量SQL，该变量使用带有 a 的SELECT语句JOIN来获取表的主键和键列的位置。 SQL服务器： 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');";
Postgre: SQL 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服务器构造转换为 Post SQL gre。 | 以下更改适用于所有应用程序，无论编程语言如何。 使用新的、适当的模式名称来限定应用程序使用的数据库对象。 使用 Postgre 中的排序规则功能处理区分大小写匹配的LIKE运算符。SQL 处理不支持的数据库特定函数 DATEDIFF，例如 DATEADD、GETDATE、CONVERT 和 CAST 运算符。有关与 Postgre SQL 兼容的等效函数，请参阅 “其他信息” 部分中的原生SQL函数或内置函数。 处理比较语句中的布尔值。 处理函数返回值。这可能是记录集、数据框、变量和布尔值。根据应用程序的要求处理这些问题并支持 Postgre。SQL 使用新的用户定义的 Postgre SQL 函数处理匿名块（例如BEGIN TRAN）。 转换行批量插入。Postgre SQL 等效于SQL服务器批量复制 (bcp) 实用程序（从应用程序内部调用）是COPY。 转换列连接运算符。SQL服务器使用字符串连接+，但 Postgre 使用字符串连接。SQL ||
| 应用程序开发人员 |
| 任务 | 描述 | 所需技能 |
|---|
利用AWS服务来增强性能。 | 迁移到AWS云端时，您可以优化应用程序和数据库设计以利用AWS服务。例如，如果连接到 Aurora Postgre SQL 兼容数据库服务器的 Python 应用程序的查询比最初的 Microsoft SQL 服务器查询花费的时间长，则可以考虑从 Aurora 服务器直接向亚马逊简单存储服务 (Amazon S3) Simple S3 存储桶创建历史数据源，然后使用基于亚马逊雅典娜的SQL查询为用户控制面板生成报告和分析数据查询。 | 应用程序开发人员、云架构师 |
相关资源
其他信息
微软SQL服务器和兼SQL容 Aurora Postgre 的 Aurora Postgre 都令人抱怨 ANSI SQL 但是，在将 Python 或 Perl 应用程序从 Server 迁移到 Postgre 时，您仍应注意语法、列数据类型、本地数据库专用函数、批量插入和区分大小写等方面的任何不兼容之处。SQL SQL
以下部分提供有关每个不一致地方的更多信息。
数据类型比较
从 SQL Server 到 Postgre 的数据类型更改SQL可能会导致应用程序运行的结果数据存在显著差异。有关数据类型的比较，请参阅 Sqlines 网站的表格。
原生或内置SQL函数
SQL服务器和 Postgre SQL 数据库之间某些函数的行为有所不同。下表提供了对比。
微软SQL服务器 | 描述 | Postgre SQL |
|---|
CAST
| 将值从一个数据类型转换为另一个数据类型。 | Postgre SQL type :: operator |
GETDATE()
| 以某种 YYYY-MM-DD hh:mm:ss.mmm 格式返回当前数据库系统的日期和时间。 | CLOCK_TIMESTAMP
|
DATEADD
| 为日期添加时间/日期间隔。 | INTERVAL 表达式
|
CONVERT
| 将值转换为特定数据格式。 | TO_CHAR
|
DATEDIFF
| 返回两个日期字段相差的天数。 | DATE_PART
|
TOP
| 限制 SELECT 结果集中的行数。 | LIMIT/FETCH
|
匿名区块
结构化SQL查询分为声明、可执行文件和异常处理等部分。下表比较了微软SQL服务器和Postgre SQL 版本的简单匿名区块。对于复杂匿名块，我们建议您在应用程序中调用自定义数据库函数。
微软SQL服务器 | Postgre SQL |
|---|
my $sql_qry1=
my $sql_qry2 =
my $sqlqry = "BEGIN TRAN
$sql_qry1 $sql_qry2
if @\@error !=0 ROLLBACK
TRAN
else COMIT TRAN";
| my $sql_qry1=
my $sql_qry2 =
my $sql_qry = " DO \$\$
BEGIN
$header_sql $content_sql
END
\$\$";
|
其他区别
my $sql_qry = "SELECT $record_id FROM $exampleTable WHERE LOWER($record_name) = \'failed transaction\'";
串联：SQL服务器使用+作为字符串连接的运算符，而 Postgre 则使用。SQL ||
验证：在 Postg SQL re 的应用程序代码中使用内联SQL查询和函数之前，应对其进行测试和验证。
ORM库包含：您也可以寻找包含现有数据库连接库或将其替换为 SQLAlchemyPyn om odb 等 Python ORM 库。这将有助于使用面向对象的范例轻松地查询和操作数据库中的数据。
