Access DB Python

gistfile1.txt

import pandas as pd
from sqlalchemy import create_engine
import urllib
import pyodbc
import numpy as np

#Config
literal_list = 'LITR_TBL.xlsx'
literal_db_location = r'C:\Users\popurisu\Downloads\LiteralDatabase.accdb'


#########################################################################################
#Globals
conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
            r'DBQ=C:\Users\popurisu\Downloads\LiteralDatabase.accdb;')

#########################################################################################
#Generate Comparision Dump between literal sheet and access DB to identify existing literals
def generate_comparision(df1,df2):
    df = pd.merge(df1,df2,left_on='JAPANESE',right_on='RESC_VAL',how = 'left',indicator=True)
    df[df['_merge'] == 'both'].to_csv('dump.csv')
    return df

def insert_literals(df):
    sql = "INSERT INTO LITR_TBL (LTL_ID,RESC_KEY,RESC_VAL,CATEGORY,LOCALE,APPL_ID,INSERTDATE) VALUES (?, ?, ?, ?, ?, ?, ?)"
    conn.cursor().executemany(sql,df.itertuples())
    # conn.cursor().execute("DELETE FROM LITR_TBL WHERE APPL_ID = 'RP'")
    conn.commit()

def generate_literal_insertion_format(df):
    df_i = df[['SCoTS','JAPANESE','Category']]
    df_i.drop_duplicates()
    df_i = df_i.reset_index(drop=True)
    df_i.index.name='LTL_ID'
    df_i.rename(columns = {'SCoTS':'RESC_KEY','JAPANESE':'RESC_VAL'}, inplace = True)
    df_i.loc[:,'LOCALE'] = 'JP'
    df_i.loc[:,'APPL_ID'] = 'RP'
    df_i.loc[:,'INSERTDATE'] = '15-06-2023'
    return df_i


#MAIN Execution

df1 = pd.read_excel(literal_list) #Read Literals from provided Sheet
# df2 = pd.read_sql('SELECT * FROM LITR_TBL', conn) #Read Literal from Access DB
# df = generate_comparision(df1,df2) # Compare for new Literals
# df = df[df['_merge'] == 'left_only']
df = generate_literal_insertion_format(df1)
# print(df)
insert_literals(df)
# df.to_csv('Inserted_literals.csv')