How to add a cursor() of a db connection to a sqlalchemy.orm session? "sqlalchemy.orm.exc.UnmappedInstanceError: Class 'builtins.int' is not mapped"

Question

I am in a Ubuntu 22.04 Docker container with Python 3.10.

I use these apt packages:

mysql-client
libmysqlclient-dev

I use the following Python packages:

import mysqlclient==2.1.0
import sqlalchemy
# for the environment variables:
from dotenv import load_dotenv
# And to use sessions with flush, with the commit only at the end:
from sqlalchemy.orm import sessionmaker

The sessionmaker parameter autoflush is set to True.

I want to add a deletion and after that an insertion to a sqlalchemy.orm session so that I commit only when the two commands worked out well. The aim of this is to update a table that gets updated over the day. I do not want to delete anything before I am not sure that the insertion really works.

The part of the Python code that leads to this error (without the follow-up insertion command):

DELETE_QUERY = f"""
    DELETE FROM {MY_TABLE} 
    WHERE checkdate = DATE(NOW())
"""

def delete_execute(sess, conn, query):
    """Function for deleting and adding to sess values 
    from the DB
    :param connection: pymsql connection to DB
    :param query: SQL query containing DELETE keyword
    :return: count of rows deleted
    """
    try:
        cursor = conn.cursor()
        sess.add(cursor.execute(query))
        # # Not yet commit, only after insertion:
        # connection.commit()
        # # Updates the objects of the session:
        # sess.flush()
        # # Not needed here since autoflush is set to True
        return cursor.rowcount, sess

engine = create_engine(CONNECTION)
# Session = sessionmaker(engine)
Session = sessionmaker(autocommit=False, autoflush=True, bind=engine)

# Connect to DB
logging.info("Connecting to DB ...")

# # with statement closes the connection automatically,
# # see https://docs.sqlalchemy.org/en/14/dialects/mysql.html
# # But the class does not have the needed __enter__ attribute
# https://stackoverflow.com/questions/51427729/python-error-attributeerror-enter
# with engine.connect() as conn:
# # engine.connect() throws an error as well:
# conn = engine.connect() 
# # connection.cursor() AttributeError: 'Connection' object has no attribute 'cursor'
# https://stackoverflow.com/questions/38332787/pandas-to-sql-to-sqlite-returns-engine-object-has-no-attribute-cursor
conn = engine.raw_connection()

with Session() as sess:
    # The records only get deleted after commit
    # This only adds them to the session.
    deleted_records_count, sess = delete_execute(sess, conn, DELETE_QUERY)

I do not get the clue from other links on the same error:

• sqlalchemy.orm.exc.UnmappedInstanceError: Class 'builtins.NoneType' is not mapped:

The User model contains many Task models and the Task model contains many Subtask models.

• sqlalchemy.orm.exc.UnmappedInstanceError: Class 'builtins.dict' is not mapped:

...insert a new User into a DB using SQLAlchemy and Marshmallow.

I am new to sqlalchemy.orm and I fear that I have misunderstood something. I want to use a session for deletion and afterwards insertion of records, and I only want to commit in the end of the two commands. I use a cursor for the deletion. How can I embed the cursor - which is only available through the conn object from conn = engine.raw_connection() - so that the task is done only if the next insertion task works as well? I cannot just add it to the session:

sess.add(cursor.execute(query))

Which throws:

sqlalchemy.orm.exc.UnmappedInstanceError: Class 'builtins.int' is not mapped

The error in detail:

Traceback (most recent call last):
  File "/usr/lib/python3.9/runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/usr/lib/python3.9/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/MY_PROJECT/main.py", line 574, in <module>
    get_sql_and_save_as_csv_in_gcs(request)
  File "/MY_PROJECT/main.py", line 489, in get_sql_and_save_as_csv_in_gcs
    deleted_records_count, sess = delete_execute(sess, conn, DELETE_QUERY)
  File "/usr/local/lib/python3.9/dist-packages/dryable/__init__.py", line 34, in _decorated
    return function( * args, ** kwargs )
  File "/MY_PROJECT/main.py", line 204, in delete_execute
    sess.add(cursor.execute(query))
  File "/usr/local/lib/python3.9/dist-packages/sqlalchemy/orm/session.py", line 2601, in add
    util.raise_(
  File "/usr/local/lib/python3.9/dist-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
sqlalchemy.orm.exc.UnmappedInstanceError: Class 'builtins.int' is not mapped

So how should I do it?

Answer 1

from sqlalchemy.sql import func

with Session() as session:
    # Model here is the orm Model, you could also do something similar
    # using sqlalchemy's core layer.
    cursor_result = session.query(Model).filter(Model.checkdate == func.now()).delete()
    deleted_rowcount = cursor_result.rowcount
    # This will insert each model at a time, maybe you want to do something
    # with better performance here.
    session.add_all(new_models)
    # commit delete and perform inserts
    session.commit()

It turns out using the orm delete above returns a cursor, you can read about it here: update-and-delete-with-arbitrary-where-clause

The core version, using a table object, should work with session.execute and you can read about it here: getting-affected-row-count-from-update-delete