PyMySQL custom converters reset to default / not used during a commit

[Alexandre Avanian]

When creating a mysql engine with pymysql and custom converters, the converters dict passed in parameter is taken in account in the pymysql connection object but during a commit, it seems encoders revert to the default ones. See below some sample code.

The error "AttributeError: 'Timestamp' object has no attribute 'translate'" occurs when pymysql cannot find a converter for the type of the value passed. Dropping in debugger confirms that the encoders are the default ones and the value is of type pandas.Timestamp.

sqlalchemy: 1.0.13
pymysql: 0.7.6.None

from datetime import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, DateTime
from pymysql.converters import conversions, escape_datetime
from pandas import Timestamp


Base = declarative_base()
class TestConverter(Base):
    __tablename__ = 'test_converter'

    id = Column(Integer, primary_key=True)
    when = Column(DateTime)

assert escape_datetime(Timestamp('2016-08-12 08:45:00')) == escape_datetime(datetime(2016, 8, 12, 8, 45))
conversions[Timestamp] = escape_datetime
engine = create_engine('mysql+pymysql://user:password@host:port/db', connect_args={'conv': conversions})
Session = sessionmaker(bind=engine)
session = Session()

Base.metadata.create_all(engine)
print(engine.contextual_connect()connection.connection.encoders)  # shows that the encoder for pandas.Timestamp is there

new = TestConverter(when=Timestamp('2016-08-12 08:45:00'))
session.add(new)
session.commit()  # fails with AttributeError: 'Timestamp' object has no attribute 'translate'

[Mike Bayer]

hello -

thanks for a clear test case. Unfortunately, SQLAlchemy doesn't have any interaction with what PyMySQL does with the parameters, SQLAlchemy passes them straight through. It seems like pymysql is not consulting the "conv" dictionary at all. See below

import pymysql
from pymysql.converters import conversions, escape_datetime
from pandas import Timestamp
from datetime import datetime


assert escape_datetime(Timestamp('2016-08-12 08:45:00')) == escape_datetime(datetime(2016, 8, 12, 8, 45))
conversions[Timestamp] = escape_datetime

conn = pymysql.connect(user='scott', passwd='tiger', host='localhost', db='test', conv=conversions )
cursor = conn.cursor()

cursor.execute("create table if not exists stuff (data datetime)")

cursor.execute("insert into stuff (data) values (%s)", (Timestamp('2016-08-12 08:45:00')))

same traceback (minus the SQLAlchemy part).

[Alexandre Avanian]

Ah, should have checked. Thanks, I'll post the issue to pymysql