CODEWITHSUNDEEP
pythonpandas
Mohekar
Mohekar

Reputation: 185

Delete Records from SQL Table using query in pandas

I need to delete pre-existing records from the SQL Table from pandas SQL query. For Example. SQL table:

Id   Name   Qualification
1    Rick    Lawyer
2    John    Engg
3    Gini    Doctor
4    Bist    Architect
5    mady    lawyer

df:

Id   Name   Qualification
3    Gini    Engg
4    Bist    Lawyer

How do I write the query to update my table from pandas, because in real time there would be more columns, so I cannot write Update query. One thing we can do, first delete those rows with the same Id's and then push the df in to the table.

What I am trying to do is to delete the records from the sql table. What I did is,

pd.read_sqlquery('''
DELETE FROM TABLE_NAME
WHERE Id in ({})
''').format(df2.Id.to_list())--- 
Getting Error

How can I pass my df2 Id's to the query.

Upvotes: 1

Views: 16076

Answers (2)

Shawn
Shawn

Reputation: 21

If your SQLAlchemy version is 2.0, you have to run a query in another way.

from sqlalchemy import create_engine,text
engine = create_engine("YOUR CONNECTION CONFIG")
with engine.connect() as conn:
    conn.execute(text("SQL QUERY TEXT"))
    conn.commit()

Or use engine.begin(), so that you don't need to commit

from sqlalchemy import create_engine,text
engine = create_engine("YOUR CONNECTION CONFIG")
with engine.begin() as conn:
    conn.execute(text("SQL QUERY TEXT"))

SQLAlchemy 1.4 migrate to 2.0 document below: https://docs.sqlalchemy.org/en/14/changelog/migration_20.html#migration-core-connection-transaction

Upvotes: 0

perl
perl

Reputation: 9941

One option is to insert those updated records from pandas into a separate table. Let's call it records_updated here.

Then we can run a query to delete from the original table records with IDs found in records_updated, and then insert the records from records_updated into the original table.

from sqlalchemy import create_engine
engine = create_engine('sqlite://', echo=False)

# save original records to 'records' table
df0.to_sql('records', con=engine)

# save updated records to 'records_updated' table
df.to_sql('records_updated', con=engine)

# delete updated records from 'records'
engine.execute("DELETE FROM records WHERE Id IN (SELECT Id FROM records_updated)")

# insert updated records from 'records_updated' to 'records'
engine.execute("INSERT INTO records SELECT * FROM records_updated")

# drop 'records table'
engine.execute("DROP TABLE records_updated")

# read from records
print(pd.read_sql('records', con=engine))

    Name Qualification
Id                    
1   Rick        Lawyer
2   John          Engg
5   mady        lawyer
3   Gini          Engg
4   Bist        Lawyer

P.S. SQL syntax may vary slightly depending on the dialect. In some dialects it may be possible to combine DELETE and INSERT operations into a single UPSERT, and also to store updated records as a temporary table, so that we don't need to drop it manually.

Upvotes: 3

Related Questions