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ä, May 03 '18 at 14:23
What version of MySQL etc. are you using? Also please include the full traceback, not just the error message. — Ilja Everilä, May 04 '18 at 13:24

tsumnia · Answer 1 · 2018-11-27T20:39:35.460

14

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 '18 at 20:39

answered Jul 06 '18 at 20:02

tsumnia

454
4
18

3

I upgraded from 1.1.13 to 1.2.15 and had/fixed this error. – Chris Jan 09 '19 at 20:08

score 6 · Answer 2 · answered May 20 '18 at 14:08

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 '18 at 14:08

lucyf

115
1
1
5

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

2 Answers2