Cambie las aplicaciones de Python y Perl para que admitan la migración de bases de datos de Microsoft SQL Server a una edición compatible con PostgreSQL de Amazon Aurora

Siga estos pasos de conversión de código para migrar su aplicación a PostgreSQL.

Las siguientes epics proporcionan instrucciones detalladas para algunas de estas tareas de conversión para aplicaciones de Python y Perl.

Use una lista de verificación para cada paso de la migración.

Añada lo siguiente a la lista de verificación para cada paso de la migración de la aplicación, incluido el paso final:

Analice su base de código Python existente.

Su análisis debe incluir lo siguiente para facilitar el proceso de migración de la aplicación:

Convierta sus conexiones de bases de datos para que sean compatibles con PostgreSQL.

La mayoría de las aplicaciones de Python utilizan la biblioteca pyodbc para conectarse con las bases de datos de SQL Server de la siguiente manera.

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))

Convierta la conexión de base de datos para que sea compatible con PostgreSQL de la siguiente manera.

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))

Cambie las consultas SQL en línea a PostgreSQL.

Convierta sus consultas SQL en línea a un formato compatible con PostgreSQL. Por ejemplo, la siguiente consulta de SQL Server recupera una cadena de una tabla.

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)

Tras la conversión, la consulta SQL en línea compatible con PostgreSQL tiene el siguiente aspecto.

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)

Gestione consultas SQL dinámicas.

El SQL dinámico puede estar presente en un script o en varios scripts de Python. Los ejemplos anteriores mostraron cómo utilizar la función de reemplazo de cadenas de Python para insertar variables con el fin de crear consultas SQL dinámicas. Un enfoque alternativo consiste en añadir variables a la cadena de consulta siempre que sea aplicable. 

En el ejemplo siguiente, la cadena de consulta se construye sobre la marcha en función de los valores devueltos por una función.

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

Estos tipos de consultas dinámicas son muy comunes durante la migración de aplicaciones. Siga estos pasos para gestionar consultas dinámicas:

Gestione los conjuntos de resultados, las variables y los marcos de datos.

Para Microsoft SQL Server, se utilizan métodos de Python como fetchone() o fetchall() para recuperar el conjunto de resultados de la base de datos. También puede usar fetchmany(size) y especificar el número de registros que se van a devolver del conjunto de resultados. Para ello, puede utilizar el objeto de conexión pyodbc como se muestra en el siguiente ejemplo.

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()

En Aurora, para realizar tareas similares, como conectarse a PostgreSQL y obtener conjuntos de resultados, puede usar psycopg2 o. SQLAlchemy Estas bibliotecas de Python proporcionan el módulo de conexión y el objeto de cursor para recorrer los registros de la base de datos PostgreSQL, como se muestra en el siguiente ejemplo.

psycopg2 (Aurora compatible con 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 (Compatible con 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)

Pruebe la aplicación durante y después de la migración.

La prueba de la aplicación Python migrada es un proceso continuo. Como la migración incluye cambios en los objetos de conexión (psycopg2 o SQLAlchemy), la gestión de errores, nuevas funciones (marcos de datos), cambios en el SQL en línea, funcionalidades de copia masiva (bcpen lugar deCOPY) y cambios similares, debe probarse detenidamente durante y después de la migración de la aplicación. Consultar si:

Analice su base de código Perl existente.

Su análisis debe incluir lo siguiente para facilitar el proceso de migración de la aplicación. Debe identificar:

Convierta las conexiones de la aplicación Perl y el módulo DBI para que sean compatibles con PostgreSQL.

Las aplicaciones basadas en Perl suelen utilizar el módulo DBI de Perl, que es un módulo de acceso a bases de datos estándar para el lenguaje de programación Perl. Puede usar el mismo módulo DBI con controladores diferentes para SQL Server y PostgreSQL.

Para obtener más información sobre los módulos de Perl necesarios, las instalaciones y otras instrucciones, consulte la documentación de DBD::Pg. El siguiente ejemplo se conecta a Aurora compatible con PostgreSQL en 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}
      );

Cambie las consultas SQL en línea a PostgreSQL.

Es posible que su aplicación tenga consultas SQL en línea con SELECT, DELETE, UPDATE y sentencias similares que incluyan cláusulas de consulta que PostgreSQL no admite. Por ejemplo, consulte palabras clave como TOP y NOLOCK no se admiten en PostgreSQL. En los siguientes ejemplos, se muestra cómo se pueden gestionar TOP, NOLOCK y variables booleanas.

En 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)

Para PostgreSQL, conviértalo a:

$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)"

Gestione consultas SQL dinámicas y variables de Perl.

Las consultas SQL dinámicas son sentencias SQL que se crean durante el tiempo de ejecución de la aplicación. Estas consultas se crean de forma dinámica cuando la aplicación está en ejecución, en función de determinadas condiciones, por lo que el texto completo de la consulta no se conoce hasta el tiempo de ejecución. Un ejemplo es una aplicación de análisis financiero que analiza las 10 principales acciones a diario, y estas acciones cambian todos los días. Las tablas SQL se crean en función de los mejores resultados y los valores no se conocen hasta el tiempo de ejecución.

Supongamos que las consultas SQL en línea de este ejemplo se pasan a una función contenedora para obtener los resultados establecidos en una variable y, a continuación, una variable utiliza una condición para determinar si la tabla existe:

A continuación, se muestra un ejemplo de gestión de variables, seguido de las consultas de SQL Server y PostgreSQL para este caso de uso.

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')";

En el siguiente ejemplo, se utiliza una variable de Perl en SQL en línea, que ejecuta una sentencia SELECT con un JOIN para obtener la clave principal de la tabla y la posición de la columna clave.

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' ;";

Convierta construcciones adicionales de SQL Server a PostgreSQL.

Los siguientes cambios se aplican a todas las aplicaciones, independientemente del lenguaje de programación.

Aproveche los servicios de AWS para mejorar el rendimiento.

Al migrar a la nube de AWS, puede refinar el diseño de sus aplicaciones y bases de datos para aprovechar los servicios de AWS. Por ejemplo, si las consultas de su aplicación Python, que está conectada a un servidor de bases de datos compatible con Aurora PostgreSQL, tardan más que las consultas originales de Microsoft SQL Server, podría considerar la posibilidad de crear una fuente de datos históricos directamente a un bucket de Amazon Simple Storage Service (Amazon S3) desde el servidor Aurora y utilizar consultas SQL basadas en Amazon Athena para generar informes y consultas de datos analíticos para sus usuarios. cuadros de mando.