My objective is to transfer the data from a SQL server to odoo Postgres database automatically. below is my Python script, which will run on a scheduled basis in a linux system.

Now I'm trying to know, what is the industry standard, how are you solving this problem, is there any better way to do this.

This is what I have done so far.

import pandas as pd

import pymssql

import psycopg2

import psycopg2.extras

# Function to fetch data from SQL Server data table

def fetch_data_from_sql_server(server, database, username, password, table_name):

    connection = pymssql.connect(server=server, database=database, user=username, password=password)

    query = f'SELECT TOP 15 CustomerCode,CustomerName,Mobile,Business FROM {table_name};'  # Use "TOP 10" to get the first 10 rows

    data = pd.read_sql(query, connection)

    connection.close()

    return data

# Function to insert data into PostgreSQL database

def insert_data_to_postgres(server, database, username, password, table_name, data):

    connection_string = f'dbname={database} user={username} password={password} host={server}'

    with psycopg2.connect(connection_string) as conn:

        data_columns = data.columns.tolist()

        # Convert data to a list of tuples to use with psycopg2.extras.execute_values

        data_values = [tuple(row) for row in data.to_numpy()]

        with conn.cursor() as cur:

            for row in data_values:

                customer_code = row[0]  # Assuming CustomerCode is the first column in the DataFrame

                # Check if the CustomerCode exists in the PostgreSQL table

                cur.execute(f"SELECT CustomerCode FROM {table_name} WHERE CustomerCode = %s", (customer_code,))

                if not cur.fetchone():  # If the customer does not exist, insert the row

                    insert_query = f"INSERT INTO {table_name} ({', '.join(data_columns)}) VALUES %s"

                    psycopg2.extras.execute_values(cur, insert_query, [row])

        # Commit the changes to the database

        conn.commit()

# Configuration for SQL Server

sql_server_config = {

    "server": "server_url",

    "database": "dbname",

    "username": "sa",

    "password": "password",

    "table_name": "Customer",

}

# Configuration for PostgreSQL

postgres_config = {

    "server": "server_url",

    "database": "dbname",

    "username": "username",

    "password": "password",

    "table_name": "table_name",

}

# Fetch data from SQL Server

data = fetch_data_from_sql_server(**sql_server_config)

# Insert data into PostgreSQL

insert_data_to_postgres(**postgres_config, data=data)

print("Data transfer completed successfully.")