Current MySQL Driver Support? #12332
-
|
Hi, I'm trying to migrate to SqlAlchemy 2.0 and I hit a significant performance issue with the pymysql driver (caused by PyMySQL/PyMySQL#898). I am investigating what driver to migrate to, and I found a warning in the documentation:
I want to check that this is still true? And not outdated documentation? And hence I should try mysqlclient as the best alternative to PyMySQL (I did look at the SqlAlchemy Jenkins dashboard but my familiarity with the Jenkins UI isn't good enough for me to spot what drivers are being tested against). |
Beta Was this translation helpful? Give feedback.
Replies: 3 comments 14 replies
-
|
Hi, That's still the case. jenkins uses tox, so you can look at it for info regarding what's tested: Lines 38 to 40 in 3c69e9d and Lines 68 to 69 in 3c69e9d You may also try the mariadb connection, since I think it will work also when connecting to mysql |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for the quick information! |
Beta Was this translation helpful? Give feedback.
-
|
mysqlclient is the best driver to use, correct |
Beta Was this translation helpful? Give feedback.
-
|
well the first deeply annoying thing with mysql-connector is that it is unfriendly towards mariadb. so with most recent versions you can't just connect: nope, you get a hardcoded collation it tries to spit out: both charset and collation must be passed |
Beta Was this translation helpful? Give feedback.
-
|
it's possible that mysql connector/python is 2x slower than mysqlclient in fetching large numbers of rows, but this is not a comprehensive test |
Beta Was this translation helpful? Give feedback.
-
|
OK yeah sorry, here's an extremely basic off-by-one bug that's been in this driver likely for years, I'll report it now but this driver is NOT mature the second loop is infinite because from mysql import connector
connection = connector.connect(user="scott", password="tiger", host="mysql80", database="test")
cursor = connection.cursor(buffered=False)
cursor.execute("Drop table if exists test")
cursor.execute("create table test (id integer)")
cursor.execute("insert into test (id) values (1), (2), (3), (4), (5)")
cursor.execute("select * from test")
while True:
many = cursor.fetchmany(2)
print(f"Fetchmany(2): {many}")
if not many:
break
cursor.execute("select * from test")
while True:
many = cursor.fetchmany(1)
print(f"Fetchmany(1): {many}")
if not many:
break |
Beta Was this translation helpful? Give feedback.
-
Beta Was this translation helpful? Give feedback.
-
|
OK. I won't recommend people to use MySQL Connector/Python for now. |
Beta Was this translation helpful? Give feedback.
-
PyMySQL and mysqlclient uses same regular expression for executemany. I am not sure that using mysqlclient fix your issue. Driver is low level library. It doesn't parse SQL correctly. |
Beta Was this translation helpful? Give feedback.
-
|
I don't know the root cause, but calling |
Beta Was this translation helpful? Give feedback.
-
|
I'll try and create a MRE, but it turned out to be non-trivial, so may not be able to post it soon. |
Beta Was this translation helpful? Give feedback.
-
|
@zzzeek Does SQLAlchemy has SQL compiler that generates |
Beta Was this translation helpful? Give feedback.
-
|
we have this feature in 2.0. it is much more complicated than the one that's in mysqlclient since it also supports RETURNING |
Beta Was this translation helpful? Give feedback.
-
|
but if you are using upsert then you'd want to use values(.....) with the list inline |
Beta Was this translation helpful? Give feedback.
Hi,
That's still the case. jenkins uses tox, so you can look at it for info regarding what's tested:
sqlalchemy/tox.ini
Lines 38 to 40 in 3c69e9d
and
sqlalchemy/tox.ini
Lines 68 to 69 in 3c69e9d
You may also try the mariadb connection, since I think it will work also when connecting to mysql