Is there a way to add a feature/column to an SQL table from a pandas dataframe?

Question

I have a pandas dataframe with two columns, PaymentID and WasDenied. I want to add that WasDenied column to my SQL table "Payments" by merging on the PaymentID column. Is there any way to do that?

I'm familiar with pandas .to_sql() but I don't think the replace or append options in the if_exists parameter will help because I'm not looking to append or replace, I'm looking to merge/join, although I could be wrong. I'm using SQLite and MS SQL so if the answer is different depending on the database, let me know.

Payment Table in SQL:

PaymentID	Payment Type	Payment Amount
123	Credit Card	$100.00
456	Cash	$25.50
456	Cash	$25.50
789	Bank Transfer	$500.75
101	Debit Card	$32.14
101	Debit Card	$35.20

WasDenied table in Pandas:

PaymentID	WasDenied
123	True
456	False
789	False
101	True

Resulting updated Payments table that I'd like to see:

PaymentID	Payment Type	Payment Amount	WasDenial
123	Credit Card	$100.00	True
456	Cash	$25.50	False
456	Cash	$25.50	False
789	Bank Transfer	$500.75	False
101	Debit Card	$32.14	True
101	Debit Card	$35.20	True

df = pd.DataFrame([[123, True],[456, False], [789, False], [101, True]], columns=['PaymentID', 'WasDenial'])
df

Answer 1

If your intention is to handle the logic from your python scripts. You can try the following steps:

1. Turn your df into a temporary table
2. Update the Payments table through a join with the temporary table
3. Drop the temporary table

Here's how it would look considering SQLite as your target database:

import pandas as pd
import sqlite3

# Assuming you have a DataFrame named 'df' with columns 'PaymentID' and 'WasDenied'

# Create a temporary table with the new column
conn = sqlite3.connect('your_database.db')
df.to_sql('temp_table', conn, if_exists='replace', index=False)

# Assuming 'PaymentID' is the primary key in the Payments table
update_query = """
UPDATE Payments
SET WasDenied = (
    SELECT t.WasDenied
    FROM temp_table t
    WHERE t.PaymentID = Payments.PaymentID
)
"""

cursor = conn.cursor()
cursor.execute(update_query)
conn.commit()

# Drop the temporary table if it's no longer needed
drop_query = "DROP TABLE temp_table"
cursor.execute(drop_query)

conn.close()

I would personally use SQLAlchemy in such cases, but the choice is totally dependent on your project and requirements. If SQLAlchemy fits into your solution you can try the following code instead:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('your_database_connection_string')

df.to_sql('temp_table', engine, if_exists='replace', index=False)

update_query = """
UPDATE Payments
SET WasDenied = (
    SELECT t.WasDenied
    FROM temp_table t
    WHERE t.PaymentID = Payments.PaymentID
)
"""

with engine.begin() as conn:
    conn.execute(update_query)

drop_query = "DROP TABLE temp_table"
with engine.begin() as conn:
    conn.execute(drop_query)