The issue I was having was due to passing an instance of a Session to the "con" in pandas.DataFrame.to_sql and not the issues with the data type as I previously suspected.

According to pandas' docs DataFrame.to_sql

parameter "con" should have "sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection"

Thanks to @GordThompson pointing me in the right direction, I checked through the SQLAlchemy docs on the Session.connection() which it starts transaction and is used to procure a new connection on the session's bound engin, that is exactly what my df.to_sql(con=) needed.