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)

You're using the MySQLdb driver with SQLA. What if you use pymysql with it as well (mysql+pymysql://...)? — Ilja Everilä
– Ilja Everilä, Commented May 3, 2018 at 14:23
What version of MySQL etc. are you using? Also please include the full traceback, not just the error message. — Ilja Everilä
– Ilja Everilä, Commented May 4, 2018 at 13:24

tsumnia · Accepted Answer · 2018-11-27 20:39:35Z

15

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.

edited Nov 27, 2018 at 20:39

answered Jul 6, 2018 at 20:02

tsumnia

4644 silver badges18 bronze badges

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

1 Comment

Chris Over a year ago

I upgraded from 1.1.13 to 1.2.15 and had/fixed this error.

lucyf · Accepted Answer · 2018-05-20 14:08:33Z

6

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

answered May 20, 2018 at 14:08

lucyf

1451 gold badge1 silver badge5 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related