Python - How to copy a DB record?

Question

Newbie on Python, Pandas and SqlAlchemy.

1. I need to read a record from a table
2. Update a field ('N' to 'Y') for this original record
3. Changing some other fields of the original record
4. Save the record as new record in the table

def set_prj01_status( db: Session, ifu_infounit_id:str,  statusDesc: str):
    logger.info(f"[set_prj01_status] set '{ifu_infounit_id}' to '{statusDesc}'")

    # read current status record for the id
    query = fetch_prj01_status( db=db, ifu_infounit_id=ifu_infounit_id)
    # print(query.statement)
    query_df = pd.read_sql( query.statement, query.session.bind)
    print(f"[set_prj01_status][fetch_prj01] count: {query_df.shape[0]}")

    # the original record (only one)
    record = query_df.iloc[0]
    prj_id = record[0]
    print(f"prj_id={prj_id}")

    # Update modified record
    record.FLAG_DELETED = 'Y'
    # update_prj01_status( db=db, record)
    db.query(PRJ01).filter(PRJ01.PRJ_ID==prj_id).update({'FLAG_DELETED':'ID'})
    
    # New record to add - same record?
    record.PRJ01_CMS_PROJECTDATA_EID = null
    record.FLAG_DELETED = 'N'
    record.PRJ01_LEGENDPROCESSSTATUS = statusDesc
    print("-- newRecord:")
    print(record)
    # insert_prj01_status( db=db, new_record=record)
    # db.add(record)

In this way I simply have the two operations (update and insert) but I receive the error 'pandas.core.series.Series is not mapped'

I'm wondering if there is a way to build (and save) the new record starting from the original record, changing only some fields.

Thanks

Answer 1

The error you're encountering, 'pandas.core.series.Series is not mapped', suggests that you are trying to add a Pandas Series object directly to the database, which is not supported by SQLAlchemy. the modified code, may be like this.

from sqlalchemy.orm import Session
from your_module import PRJ01  # Import your SQLAlchemy model

def set_prj01_status(db: Session, ifu_infounit_id: str, statusDesc: str):
    logger.info(f"[set_prj01_status] set '{ifu_infounit_id}' to '{statusDesc}'")

    # Read the original record from the database
    original_record = db.query(PRJ01).filter(PRJ01.PRJ_ID == ifu_infounit_id).first()

    if original_record:
        # Update the original record
        original_record.FLAG_DELETED = 'Y'
        
        # Create a new record based on the modified original record
        new_record = PRJ01(
            PRJ01_CMS_PROJECTDATA_EID=None,
            FLAG_DELETED='N',
            PRJ01_LEGENDPROCESSSTATUS=statusDesc,
            # Copy other fields from the original_record if needed
        )

        # Add the new record to the session
        db.add(new_record)
        db.commit()

    else:
        print(f"No record found for PRJ_ID={ifu_infounit_id}")

# Call the function with your SQLAlchemy session and other arguments
# set_prj01_status(db, ifu_infounit_id, statusDesc)