pymysql and sqlalchemy: AttributeError: 'DateTime' object has no attribute 'translate'

Question

Upon trying to save a new record to the DB, I receive the above error, related to a DateTime attribute I have (engagementDate).

The type of the value I'm passing to sqlalchemy looks OK:

<wx.DateTime: "Sat Apr 28 00:00:00 2018">

On MySql, my schema has these settings:

Default Collation: utf8_general_ci

Default Character set: utf8

The date value is taken from a wx.adv.DatePickerCtrl on a wx.Dialog (form), properly presented and validated in my local utf8.

My connection string to MySQL has this local defined: utf8mb4.

The table is created with this model:

class Contractors(DeclarativeBase):
    __tablename__ = "contractors"

    id = Column(Integer, primary_key=True)
    company_name = Column("company_name", Unicode(80))
    company_address = Column("company_address", String(250))
    ...
    is_training_provider = Column("is_training_provider", Boolean())
    engagement_date = Column("engagement_date", DateTime)
    comments = Column("comments", Text())

My olv model:

class OlvContractors(object):

    def __init__(self, id, company_name, company_address, company_city, company_zip, company_country, company_phone1,
                 company_phone2, company_description, contact_person, cp_email, cp_phone1, cp_phone2,
                 is_training_provider, engagement_date, comments):
        self.id = id  # unique row id from database
        self.company_name = company_name
        self.company_address = company_address
        ...
        self.is_training_provider = is_training_provider
        self.engagement_date = engagement_date
        self.comments = comments

The SQL Statement generated by sqlalchemy is:

INSERT
INTO
contractors(company_name, company_address, company_city, company_zip, company_country, company_phone1, company_phone2,
            company_description, contact_person, cp_email, cp_phone1, cp_phone2, is_training_provider, engagement_date,
            comments)
VALUES( % (company_name)
s, % (company_address)
s, % (company_city)
s, % (company_zip)
s, % (company_country)
s, % (company_phone1)
s, % (company_phone2)
s, % (company_description)
s, % (contact_person)
s, % (cp_email)
s, % (cp_phone1)
s, % (cp_phone2)
s, % (is_training_provider)
s, % (engagement_date)
s, % (comments)
s)
{'company_name': '', 'company_address': '', 'company_city': '', 'company_zip': '', 'company_country': '',
 'company_phone1': '', 'company_phone2': '', 'company_description': '', 'contact_person': '', 'cp_email': '',
 'cp_phone1': '', 'cp_phone2': '', 'is_training_provider': 0, 'engagement_date'
 : < wx.DateTime: "Sat Apr 28 00:00:00 2018" >, 'comments': ''}

You can see that the engagement date is showing the TYPE, and not the value. Do I need to explicitly convert this? How?

I'm passing the values to be inserted into the DB as a dictionary:

{'company_name': '', 'company_address': '', 'company_city': '', 'company_zip': '', 'company_country': '', 'company_phone1': '', 'company_phone2': '', 'company_description': '', 'contact_person': '', 'cp_email': '', 'cp_phone1': '', 'cp_phone2': '', 'is_training_provider': False, 'engagement_date': <wx.DateTime: "Sat Apr 28 00:00:00 2018">, 'comments': ''}

...using this function:

def insertRecord(targetObject, dict_values):
    print("dict values received {}".format(dict_values))
    session = connectToDatabase()
    session.execute(targetObject.__table__.insert(), dict_values)
    session.commit()
    session.close()

Thanks for any guidance!

Mor Sagmon · Accepted Answer · 2018-04-29 09:13:21Z

1

Posting here my solution, for anyone's benefit:

It seems there was missing a conversion from wx.DateTime to Python datetime.

I made use of Mike's functions provided here to convert before sending the data dictionary to the DB as follows:

    if isinstance(ctrlValue, wx.DateTime):
        ctrlValue = forms_controller.wxdate2pydate(ctrlValue)

answered Apr 29, 2018 at 9:13

Mor Sagmon

1,0552 gold badges20 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pymysql and sqlalchemy: AttributeError: 'DateTime' object has no attribute 'translate'

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related