mapping excel columns to table column in database with python

Question

json file

"mappingdef": [
                {
                    "src": "A",
                    "dest": "id"
                },
                {
                    "src": "B",
                    "dest": "expense_type"
                },
                {
                    "src": "C",
                    "dest": "balance"
                },
                {
                    "src": "D",
                    "dest": "debit"
                },
                {
                    "src": "E",
                    "dest": "credit"
                },
                {
                    "src": "F",
                    "dest": "total_balance"
                }
            ]

my python script:

#changing excel column names

df.columns = ["A", "B", "C", "D", "E", "F"]

#fetching data from dataframe

for row in range(df.shape[0]):
    col_A = str(df.at[row, "A"]),
    col_B = str(df.at[row, "B"]),
    col_C = float(df.at[row, "C"]),
    col_D = float(df.at[row, "D"]),
    col_E = float(df.at[row, "E"]),
    col_F = float(df.at[row, "F"])

#query to insert data in database

query2 = """
    INSERT INTO ocean_street_apartments(
    id,
    expense_type,
    balance,
    debit,
    credit,
    total_balance)
    values (%s, %s, %s, %s, %s, %s)
    """

i have this table definition info in json which tells src as excel column, and dest as database table column name. i want to read an excel file through pandas and want to map excel column (src) to database table column (dest). i am working in python

Answer 1

Assuming that its JSON file so its an API get response.

Things i am assuming you know how to do: 1)fetch get response and what is returned is an array of object descriptions for every file. 2)create script to download this and move it to a DF.

Now you have a a list of direct links to our csv files! We can read these urls directly using pandas.read_csv(url).

If data is problematic transform them.

It's time to Directly Load DF into a SQL DB using pandas.DataFrame.to_sql

Code below describes how to connect to a SQLite db.

def upload_to_sql(filenames, db_name, debug=False):
    """ Given a list of paths, upload to a database
    """
    conn = sqlite3.connect(f"{db_name}.db")
    
    if debug:
        print("Uploading into database")
    for i, file_path in tqdm(list(enumerate(filenames))):
        
        dat = pd.read_csv(file_path)

        # rename labels
        filename = os.path.basename(file_path).split('.')[0]
        dat = factor_dataframe(dat, filename)

        # write records to sql database
        if i == 0: # if first entry, and table name already exist, replace
            dat.to_sql(db_name, con=conn, index = False, if_exists='replace')
        else: # otherwise append to current table given db_name
            dat.to_sql(db_name, con=conn, index = False, if_exists='append')


# upload into sql database
upload_to_sql(download_urls, 'example', debug=True)