[Python] Python : How to handle case-sensitive and double-quoted postgres column name?

I have python script for incremental data processing. The data source come from Oracle 32-bit and target table is Postgres like below :

import pandas as pd
import cx_Oracle
import psycopg2
import os
import access as ac
import urllib.parse
from sqlalchemy import create_engine

# Set the Oracle connection string properties
hostname = ac.src_host
port = ac.src_port
username = ac.src_user
password = ac.src_pwd
service_name = ac.src_service

# Set the Oracle client library path for 32-bit Python
os.environ["PATH"] = f"{ac.src_envi};" + os.environ["PATH"]

# Construct the Oracle connection string
dsn = cx_Oracle.makedsn(hostname, port, service_name=service_name)
oracle_conn_str = f"{username}/{password}@{dsn}"
oracle_conn=cx_Oracle.connect(oracle_conn_str)


#Construct the target connection string
postgresql_hostname = ac.trg_host
postgresql_port = ac.trg_port
postgresql_database = ac.trg_db
postgresql_schema = ac.trg_schema
postgresql_username = ac.trg_user
postgresql_password = urllib.parse.quote(ac.trg_pwd)
postgres_url=f'postgresql://{postgresql_username}:{postgresql_password}@{postgresql_hostname}:{postgresql_port}/{postgresql_database}'

#Target database (psycopg2)
pg_conn = psycopg2.connect(
host=postgresql_hostname,
port=postgresql_port,
database=postgresql_database,
user=postgresql_username,
password=ac.trg_pwd
)

#create engine
trg_engine= create_engine(postgres_url,connect_args={'options': f'-csearch_path={postgresql_schema}'})

# both source and target
table_name = 'tblmexchange'
join_key = 'exchangeid'

#set connection synonym
src_conn=oracle_conn
src_cursor=src_conn.cursor()

trg_conn=pg_conn
trg_cursor=trg_conn.cursor()

#set query for dataframe
src_query= f'''
select * from {table_name}
WHERE
TO_CHAR(CREATEDATE, 'yyyy-mm-dd')= TO_CHAR(SYSDATE-1, 'yyyy-mm-dd')
OR TO_CHAR(LASTMODIFYDATE, 'yyyy-mm-dd')= TO_CHAR(SYSDATE-1, 'yyyy-mm-dd')
OR TO_CHAR(LASTSTATUSCHGDT, 'yyyy-mm-dd')= TO_CHAR(SYSDATE-1, 'yyyy-mm-dd')
'''

trg_query = f'''
select * from {postgresql_schema}.{table_name}
where
cast(createdate as date)>= cast(current_timestamp - interval '7 day' as date)
or cast(lastmodifydate as date)>= cast(current_timestamp - interval '7 day' as date)
or cast(laststatuschgdt as date)>= cast(current_timestamp - interval '7 day' as date)
'''


src_df=pd.read_sql(src_query,src_conn)
src_df.columns=src_df.columns.str.lower()
src_df=src_df.astype(str)


trg_df=pd.read_sql(trg_query,trg_conn)
trg_df.columns=trg_df.columns.str.lower()
trg_df=trg_df.astype(str)


# Step 1: Filter rows based on join key
filtered_rows = src_df[src_df[join_key].isin(trg_df[join_key])]
different_rows= src_df[~src_df[join_key].isin(trg_df[join_key])]

# Step 2: Delete filtered rows from target table
for index, row in filtered_rows.iterrows():
trg_cursor.execute(f"DELETE FROM {postgresql_schema}.{table_name} WHERE {join_key} = %s", (row[join_key],))

# Step 3: Insert filtered rows from source DataFrame into target table
different_rows.to_sql(table_name, trg_engine, if_exists='append', index=False,schema=postgresql_schema)


But when executing this code, I faced this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "name" of relation "tblmexchange" does not exist
​

I realize when I check on the Postgres table, there is column name => "NAME" has uppercase and double-quoted if I generate sql select code from the table.

And also I didn't have permission to alter that column into lowercase on postgresdb.

It's confusing for me when I tried to using trg_df.columns=trg_df.columns.str.lower() and also trg_df.columns=trg_df.columns.str.replace('"','') but still not working.

I want to bypass the case-sensitive with python code, but still confused.

Continue reading...