Modifica le applicazioni Python e Perl per supportare la migrazione dei database da Microsoft SQL Server a Amazon Aurora PostgreSQL Compatible Edition

Segui questi passaggi di conversione del codice per migrare la tua applicazione a PostgreSQL.

I seguenti poemi epici forniscono istruzioni dettagliate per alcune di queste attività di conversione per applicazioni Python e Perl.

Usa una lista di controllo per ogni fase della migrazione.

Aggiungi quanto segue alla tua lista di controllo per ogni fase della migrazione delle applicazioni, inclusa la fase finale:

Analizza la tua base di codice Python esistente.

L'analisi dovrebbe includere quanto segue per facilitare il processo di migrazione delle applicazioni:

Converti le connessioni al database per supportare PostgreSQL.

La maggior parte delle applicazioni Python utilizza la libreria pyodbc per connettersi ai database di SQL Server come segue.

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

Converti la connessione al database per supportare PostgreSQL come segue.

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

Cambia le query SQL in linea in PostgreSQL.

Converti le tue query SQL in linea in un formato compatibile con PostgreSQL. Ad esempio, la seguente query di SQL Server recupera una stringa da una tabella.

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)

Dopo la conversione, la query SQL in linea compatibile con PostgreSQL ha il seguente aspetto.

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)

Gestisci le query SQL dinamiche.

L'SQL dinamico può essere presente in uno script o in più script Python. Gli esempi precedenti hanno mostrato come utilizzare la funzione di sostituzione delle stringhe di Python per inserire variabili per la costruzione di query SQL dinamiche. Un approccio alternativo consiste nell'aggiungere la stringa di query con variabili laddove applicabile. 

Nell'esempio seguente, la stringa di query viene costruita al volo in base ai valori restituiti da una funzione.

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

Questi tipi di interrogazioni dinamiche sono molto comuni durante la migrazione delle applicazioni. Segui questi passaggi per gestire le query dinamiche:

Gestisci set di risultati, variabili e frame di dati.

Per Microsoft SQL Server, si utilizzano metodi Python come fetchone() o fetchall() per recuperare il set di risultati dal database. È inoltre possibile utilizzare fetchmany(size) e specificare il numero di record da restituire dal set di risultati. A tale scopo, è possibile utilizzare l'oggetto di connessione pyodbc come illustrato nell'esempio seguente.

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

In Aurora, per eseguire attività simili come la connessione a PostgreSQL e il recupero dei set di risultati, puoi usare psycopg2 o. SQLAlchemy Queste librerie Python forniscono il modulo di connessione e l'oggetto cursore da attraversare tra i record del database PostgreSQL, come mostrato nell'esempio seguente.

psycopg2 (compatibile con 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 (Compatibile 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)

Testa la tua applicazione durante e dopo la migrazione.

Il test dell'applicazione Python migrata è un processo continuo. Poiché la migrazione include modifiche agli oggetti di connessione (psycopg2 o SQLAlchemy), gestione degli errori, nuove funzionalità (frame di dati), modifiche SQL in linea, funzionalità di copia in blocco (bcpanzichéCOPY) e modifiche simili, deve essere testata attentamente durante e dopo la migrazione dell'applicazione. Controlla:

Analizza la tua base di codice Perl esistente.

L'analisi dovrebbe includere quanto segue per facilitare il processo di migrazione delle applicazioni. È necessario identificare:

Converti le connessioni dall'applicazione Perl e dal modulo DBI per supportare PostgreSQL.

Le applicazioni basate su Perl utilizzano generalmente il modulo Perl DBI, che è un modulo di accesso al database standard per il linguaggio di programmazione Perl. È possibile utilizzare lo stesso modulo DBI con driver diversi per SQL Server e PostgreSQL.

Per ulteriori informazioni sui moduli Perl richiesti, sulle installazioni e altre istruzioni, consultate la documentazione di DBD: :Pg. L'esempio seguente si connette a Aurora, compatibile con PostgreSQL all'indirizzo. 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}
      );

Cambia le query SQL in linea in PostgreSQL.

L'applicazione potrebbe avere query SQL in linea conSELECT, DELETEUPDATE, e istruzioni simili che includono clausole di query non supportate da PostgreSQL. Ad esempio, parole chiave di query come TOP e NOLOCK non sono supportate in PostgreSQL. Gli esempi seguenti mostrano come è possibile gestire le variabili TOP NOLOCK booleane e.

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

Per PostgreSQL, converti in:

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

Gestisci query SQL dinamiche e variabili Perl.

Le query SQL dinamiche sono istruzioni SQL create in fase di esecuzione dell'applicazione. Queste query vengono create dinamicamente durante l'esecuzione dell'applicazione, a seconda di determinate condizioni, in modo che il testo completo della query non sia noto fino all'esecuzione. Un esempio è un'applicazione di analisi finanziaria che analizza quotidianamente le prime 10 azioni e queste azioni cambiano ogni giorno. Le tabelle SQL vengono create in base alle migliori prestazioni e i valori sono noti solo in fase di esecuzione.

Supponiamo che le query SQL in linea per questo esempio vengano passate a una funzione wrapper per ottenere i risultati impostati in una variabile e che quindi una variabile utilizzi una condizione per determinare se la tabella esiste:

Ecco un esempio di gestione delle variabili, seguito dalle query SQL Server e PostgreSQL per questo caso d'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')";

L'esempio seguente utilizza una variabile Perl in SQL in linea, che esegue un'SELECTistruzione con JOIN a per recuperare la chiave primaria della tabella e la posizione della colonna chiave.

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

Converti costrutti SQL Server aggiuntivi in PostgreSQL.

Le seguenti modifiche si applicano a tutte le applicazioni, indipendentemente dal linguaggio di programmazione.

Sfrutta i servizi AWS per migliorare le prestazioni.

Quando esegui la migrazione al cloud AWS, puoi perfezionare la progettazione di applicazioni e database per sfruttare i servizi AWS. Ad esempio, se le query della tua applicazione Python, connessa a un server di database Aurora compatibile con PostgreSQL, richiedono più tempo rispetto alle query originali di Microsoft SQL Server, potresti prendere in considerazione la creazione di un feed di dati storici direttamente in un bucket Amazon Simple Storage Service (Amazon S3) dal server Aurora e utilizzare Amazon Athena Query SQL per generare report e query di dati analitici per i dashboard degli utenti.