更改 Python 和 Perl 应用程序以支持数据库从微软SQL服务器迁移到亚马逊 Aurora Postgre SQL-兼容版

按照以下代码转换步骤将您的应用程序迁移到 Postgre。SQL

以下操作说明详细说明了 Python 和 Perl 应用程序的一些转换任务。

为迁移的每个步骤使用清单。

将以下内容添加到应用程序迁移的每个步骤（包括最后一步）的清单中：

分析现有的 Python 代码库。

您的分析应包含以下内容，以简化应用程序迁移过程：

转换您的数据库连接以支持 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

这些类型的动态查询在应用程序迁移过程中非常常见。请按照以下步骤处理动态查询：

处理结果集、变量与数据框。

对于 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 Postgre SQL-兼容）

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模块的连接以支持 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。

以下更改适用于所有应用程序，无论编程语言如何。

利用AWS服务来增强性能。

迁移到AWS云端时，您可以优化应用程序和数据库设计以利用AWS服务。例如，如果连接到 Aurora Postgre SQL 兼容数据库服务器的 Python 应用程序的查询比最初的 Microsoft SQL 服务器查询花费的时间长，则可以考虑从 Aurora 服务器直接向亚马逊简单存储服务 (Amazon S3) Simple S3 存储桶创建历史数据源，然后使用基于亚马逊雅典娜的SQL查询为用户控制面板生成报告和分析数据查询。