CODEWITHSUNDEEP
pythonsqlalchemy
codingInMyBasement
codingInMyBasement

Reputation: 848

Delete SQL Table Row Where Column Value Matches Pandas Dataframe Column Value

I am trying to delete rows in a SQL table that have a matching column value in my pandas dataframe. This is the code I have so far. It only works on some of the data for some reason. I keep getting the error listed below. What is the best way to do this?

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import update
from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy import select
from sqlalchemy import or_
from sqlalchemy import and_

    engine = create_engine(cnxn_str)
    meta = sqlalchemy.MetaData()
    # Map the Inventory table in your database to a SQLAlchemy object
    inventory = sqlalchemy.Table(table_name, meta, autoload=True, autoload_with=engine)
    #make the condition
    cond = inventory.c['column_name'].in_(df['column_name'])
    # Define and execute the DELETE
    delete = inventory.delete().where(cond)
    with engine.connect() as conn:
        conn.execute(delete)

DBAPIError: (pyodbc.Error) ('07002', '[07002] [Microsoft][ODBC Driver 13 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)') [SQL: 'DELETE FROM [table_name] WHERE [table_name].[column_name] IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,

Upvotes: 3

Views: 1168

Answers (1)

Yackens
Yackens

Reputation: 29

This post is a bit old. Nevertheless, I will share my solution so it might help others.

I had a similar problem and ended up creating the follow function:

def execute_query(query: str, is_fw=False, conexion_ar=con_db_AR, conexion_fw="con_db_FW"):
"""

Parameters:
    query(str): Text with the query
    conexion_ar: Conexion to Redshift
    conexion_fw: Conexion to the framework
"""
if is_fw:
    conexion = conexion_fw
else:
    conexion = conexion_ar

conexion.execute(f"{query}; commit;")

Hope it helps ;D

Upvotes: 0

Related Questions