python_change_tracking_process.py

import sqlalchemy
from sqlalchemy.pool import NullPool
from sqlalchemy import text


def connect_sqlserver():

    user = ""
    password = ""
    database = ""
    port = "1433"
    server = "127.0.0.1"
    engine = "mssql+pyodbc"
    args = "driver=ODBC Driver 17 for SQL Server"

    return sqlalchemy.create_engine(engine+'://'+user+':'+password+'@'+server+':'+port+'/'+database+'?'+args,poolclass=NullPool)   

def connect_mysql():

    user = ""
    password = ""
    database = ""
    port = "3306"
    server = "127.0.0.1"
    engine = "mysql+pymysql"
    args = ""
    
    return sqlalchemy.create_engine(engine+'://'+user+':'+password+'@'+server+':'+port+'/'+database+'?'+args,poolclass=NullPool)   

sqlserver_engine = connect_sqlserver()
mysql_engine = connect_mysql() 


print(" mengambil id paling awal ") 

with sqlserver_engine.connect() as sqlserver_con :

    sql = text("SELECT TOP 1 * FROM pegawai_log WHERE status = 'ANTRI' ORDER BY id ASC")
    
    res_antrian = sqlserver_con.execute(sql)
    for antrian in res_antrian.fetchall() : 
        print(antrian)

        if antrian.operasi.upper() in ['UPDATE','INSERT'] :
            print ("UPSERT")

            sql = text(" SELECT TOP 1 * FROM pegawai WHERE id = :id ")
            
            res_data = sqlserver_con.execute(sql,id=antrian.id_pegawai)
            
            for data in res_data.fetchall() :
                
                with mysql_engine.connect() as mysql_con :
                    
                    sql = text (""" INSERT INTO pegawai_replicated
                                    (id,nama,jenis_kelamin,is_deleted) 
                                    VALUES
                                    (:id,:nama,:jenis_kelamin,0)
                                    ON DUPLICATE KEY UPDATE
                                    nama = :nama,
                                    jenis_kelamin = :jenis_kelamin
                                """)
                    mysql_con.execute(sql,id=data.id,nama=data.nama,jenis_kelamin = data.jenis_kelamin)
                    
                    print("selesai upsert")

            sql = text("UPDATE pegawai_log SET status = 'SELESAI', update_at = getdate() WHERE id = :id")
                
            sqlserver_con.execute(sql,id = antrian.id) 

            print("selesai")          
    
        if antrian.operasi.upper() == "DELETE" :
            print ("DELETE")

            with mysql_engine.connect() as mysql_con : 
                
                sql = text (""" UPDATE pegawai_replicated SET is_deleted = 1 WHERE id = :id """)
                
                mysql_con.execute(sql,id = antrian.id_pegawai) 

                print("delete di replicated ")

            sql = text (""" UPDATE pegawai_log SET status = 'SELESAI', update_at = getdate() WHERE id = :id """)

            sqlserver_con.execute(sql, id = antrian.id)

            print("selesai") 
            
             



sqlserver_engine.dispose()
mysql_engine.dispose()