Cannot connect to a local mysql db with sqlalchemy on windows machine

Question

I'm trying to connect to a local mysql DB on my windows machine using sqlalchemy. It works using pymysql, but not with sqlalchemy.

Code sqlalchemy:

engine = create_engine('mysql+mysqldb://root:mypass@localhost/classicmodels')
engine.connect()

Gives error:

OperationalError: (_mysql_exceptions.OperationalError) (1193, "Unknown system variable 'tx_isolation'")

Working code, using pymysql:

connection = pymysql.connect(host='localhost',
                             user='root',
                             password = 'mypass',
                             db = 'classicmodels')

cursor = connection.cursor()

cursor.execute('select * from customers')
mydata = cursor.fetchall()
mydata = pd.DataFrame(list(mydata))
mydata.head(5)

Answer 1

I ran into a similar error involving tx_isolation.

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1193, "Unknown system variable 'tx_isolation'")

This was due to using an old version of SQLAlchemy.

pip install --upgrade SQLAlchemy

I was running off v1.0.15, upgrading to v1.2.9 fixed the error for me. I am also using the mysql+pymysql connection setting as mentioned above.

Answer 2

What's your version of sqlalchemy? Try to use the newest one, because the 8.0 version of mysql has depreciated 'tx_isolation'.