Problem with changing from mysqlclient to pymysql: save dict as string

Question

I need to change a project that currently uses the library mysqlclient to use pymysql because of license issues.

The project uses sqlalchemy and doesn't use mysqlclient directly so I thought I will only need to change the connection string but I seem to encounter an edge case.

I have places in the code where some columns are defined in the sqlalchemy model as String, but for some reason (old code) the code tries to put a dict there. This works by casting the dict to str (this is the expected behaviour for all types - if I put int it will be cast to str).

When I change from the library mysqlclient to pymysql this behaviour seem to break only for dicts.

Here is a sample code that replicate this issue:

import sqlalchemy
from sqlalchemy import Column, Integer, String, DateTime, func, text, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


SCHEMA = 'testing'
con = "mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}/{SCHEMA}?charset=utf8mb4".format(USERNAME='reducted',
                                                                                     PASSWORD="reducted",
                                                                                     HOST='127.0.0.1:3306',
                                                                                     SCHEMA=SCHEMA)
engine = sqlalchemy.create_engine(con, pool_recycle=3600, pool_size=20, pool_pre_ping=True, max_overflow=100)
metadata = MetaData(bind=engine)
base = declarative_base(metadata=metadata)

class TestModel(base):
    __tablename__ = 'test_table'
    __table_args__ = {'autoload': False,
                      'schema': SCHEMA
                      }
    id = Column(Integer(), primary_key=True, nullable=False, index=True)
    test_value = Column(String(50), nullable=False)
    date_created = Column(DateTime, server_default=func.now(), index=True)
    date_modified = Column(DateTime, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), index=True)

metadata.create_all()

session_maker = sessionmaker(bind=engine)
session = session_maker()
row = TestModel()
row.test_value = {}
session.add(row)
session.commit()

This causes this error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '})' at line 1

If you change pymysql in the connection string to mysqldb the code will work.

My question is this: Is there a workaround or is there a sqlalchemy hook i can use cast the dicts myself?

Also if anymore knows about more issues in moving from mysqlclient to pymysql i would appreciate any tip, I cant seem to find any documentation of the differences (except the license part)

Answer 1

is there a sqlalchemy hook i can use cast the dicts myself?

You could add a validator to your TestModel class:

    @validates("test_value")
    def validate_test_value(self, key, thing):
        if isinstance(thing, dict):
            return str(thing)
        else:
            return thing