Adding Support for PyMySQL (for Python 3)

[Ian Clelland]

Hi, all ---

This is a proposal to add support in Django for PyMySQL[1] as an optional replacement for MySQLdb.

I've been working with Vinay Sajip's Python 3-compatible branch; trying to get MySQL support working, and I have had a great deal of success, using PyMySQL as a back end, rather than MySQLdb.

MySQLdb does not support Python3, and I haven't seen any indication online that it will be supporting Python3 any time soon. 

PyMySQL is a pure python implementation of PEP 249 for MySQL, and supports Python 2.4 - 3.2, and MySQL 4.1 and higher. It is nearly a plug-in-replacement for MySQLdb  (although there are a few compatibility issues that I have tried to take care of in Django backend code)

My approach so far has been to patch django/db/backends/mysql:

   - Try to import MySQLdb, and then MySQLdb.converters and MySQLdb.constants.

   - If that fails, try to import pymysql, and pymysql.converters and pymysql.constants

   - Whichever succeeds, set a module-level variable 'backend' to indicate the backend that we are using.

   - Patch the data-type conversions appropriately, depending on the backend that was used.

So far, that's it -- and I'm down to just a couple of unit test failures, but those may even be Python3 issues; I haven't tracked them all down yet.

My questions, then:

1. Is PyMySQL appropriate for django-core, in the opinion of the core devs? There isn't another stable Python 3-compatible mysql adapter, as far as I know, and I don't think Django could move to Python 3 support without at least the currently-supported databases.

2. Is this the right approach? I know that in the past, we have had multiple backends for PostgreSQL, in different modules under django.db.backends, but in this case, the modules are almost 100% compatible, except for the module names themselves, and the calling conventions for the data conversions. It feels more like the situation with JSON libraries, where we just load whichever one is available, and present the same outward api to the developer.

3. Code speaks, I know -- what's the best way to share this? I've sent Vinay a patch, but that's for his Py3k branch, and might not get the audience that something like this needs. I'm doing it for Py3k support, but ideally it would work just as well under Python 2.x. Should I set up a public repository somewhere, or just open a ticket and attach patches?

Thoughts? Flames?

Regards,
Ian Clelland
<clel...@gmail.com>

[1] Original at https://github.com/petehunt/PyMySQL, my patches for better Python 3 support at https://github.com/clelland/PyMySQL

[Alex Gaynor]

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To post to this group, send email to django-d...@googlegroups.com.
To unsubscribe from this group, send email to django-develop...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

It's a fine goal, though I'm not convinced this should necessarily ship with Django given PyMySQL has relatively few users.  However, the approach is the wrong one, django database backends should be really split up between the parts that are database specific, and the parts that are driver specific, that way someone can write an external driver that uses, say, the postgresql spcific stuff but make it run on the 500 other postgresql drivers.

Alex

--
"I disapprove of what you say, but I will defend to the death your right to say it." -- Evelyn Beatrice Hall (summarizing Voltaire)
"The people's good is the highest law." -- Cicero

[Jannis Leidel]

Ian,

> This is a proposal to add support in Django for PyMySQL[1] as an optional replacement for MySQLdb.
>
> I've been working with Vinay Sajip's Python 3-compatible branch; trying to get MySQL support working, and I have had a great deal of success, using PyMySQL as a back end, rather than MySQLdb.
>
> MySQLdb does not support Python3, and I haven't seen any indication online that it will be supporting Python3 any time soon.

That's not entirely true actually, I talked to Andy Dustman (MySQLdb's author earlier this year while researching the bits needed for porting) and he mentioned that several people ported it over the Python 3:

- http://sourceforge.net/projects/mysql-python/forums/forum/70460/topic/3831691
- https://github.com/davispuh/MySQL-for-Python-3

Quoting him from his private reply: "I use 1.2.3 regularly with Django, so if there's serious interest in a Python 3 version, I'll try to find the time and energy to work on it again."

As to which of those unofficial ports is the "correct" one, I can't help. But it would certainly be helpful to get in touch with Andy before dismissing MySQLdb altogether.

> PyMySQL is a pure python implementation of PEP 249 for MySQL, and supports Python 2.4 - 3.2, and MySQL 4.1 and higher. It is nearly a plug-in-replacement for MySQLdb (although there are a few compatibility issues that I have tried to take care of in Django backend code)
>
> My approach so far has been to patch django/db/backends/mysql:
> - Try to import MySQLdb, and then MySQLdb.converters and MySQLdb.constants.
> - If that fails, try to import pymysql, and pymysql.converters and pymysql.constants
> - Whichever succeeds, set a module-level variable 'backend' to indicate the backend that we are using.
>
> - Patch the data-type conversions appropriately, depending on the backend that was used.
>
> So far, that's it -- and I'm down to just a couple of unit test failures, but those may even be Python3 issues; I haven't tracked them all down yet.
>
> My questions, then:
>
> 1. Is PyMySQL appropriate for django-core, in the opinion of the core devs? There isn't another stable Python 3-compatible mysql adapter, as far as I know, and I don't think Django could move to Python 3 support without at least the currently-supported databases.

I think it's better to ask whether it's appropriate to switch the backend library while doing the Python version jump. In my opinion it's not given the stability MySQLdb has shown over the years. At least I'm not seeing reasons to get rid of it.

> 2. Is this the right approach? I know that in the past, we have had multiple backends for PostgreSQL, in different modules under django.db.backends, but in this case, the modules are almost 100% compatible, except for the module names themselves, and the calling conventions for the data conversions. It feels more like the situation with JSON libraries, where we just load whichever one is available, and present the same outward api to the developer.

IMO it's not, database backends vary in the way to speak to the database, so if -- and only if it's really needed to introduce another backend for MySQL -- it should be independent from the current one.

> 3. Code speaks, I know -- what's the best way to share this? I've sent Vinay a patch, but that's for his Py3k branch, and might not get the audience that something like this needs. I'm doing it for Py3k support, but ideally it would work just as well under Python 2.x. Should I set up a public repository somewhere, or just open a ticket and attach patches?

I asked Martin von Löwis who maintains the Py3K branch in Django official SVN repo to review Vinay's changes and merge them, as long as you manage to get patches in line with their work I think we're fine.

Jannis

[Ian Clelland]

On Wed, Dec 7, 2011 at 12:48 PM, Alex Gaynor <alex....@gmail.com> wrote:

It's a fine goal, though I'm not convinced this should necessarily ship with Django given PyMySQL has relatively few users.

Agreed; I just don't know of another way to support Python 3 and MySQL, without pushing the MySQLdb developers to add support. (This is the Python 3 upgrade catch-22, and I'm looking for a way out of it).

 

 However, the approach is the wrong one, django database backends should be really split up between the parts that are database specific, and the parts that are driver specific,

Do you mean to say that putting support for two drivers in django.db.backends.mysql is wrong, or that the django backends need to be refactored?

I had started off by writing a new backend, mysql_pymysql (in the spirit of postgres_psycopg2), but there is so much overlap that it didn't make sense to me to have to duplicate all of the code in a separate module, when most of what I was doing was just changing the imports at the top of base.py and introspection.py.

I would be happy putting this out as a separate backend, which just imported all of its functionality (and future bug fixes) from django.db.backends.mysql, but I can't even import the django backend modules in an environment which doesn't have MySQLdb installed -- say, any Python 3 environment.

I'll think some more on it, but if you have any architectural suggestions, I'm very open to them.

 

that way someone can write an external driver that uses, say, the postgresql spcific stuff but make it run on the 500 other postgresql drivers.

Alex

Ian

--
Regards,
Ian Clelland
<clel...@gmail.com>

[Alex Gaynor]

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To post to this group, send email to django-d...@googlegroups.com.
To unsubscribe from this group, send email to django-develop...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

I'm saying database backends need badly to me refactored to split up these two seperate concerns.

[Ian Clelland]

On Wed, Dec 7, 2011 at 12:56 PM, Jannis Leidel <lei...@gmail.com> wrote:

Ian,

> This is a proposal to add support in Django for PyMySQL[1] as an optional replacement for MySQLdb.
>
> I've been working with Vinay Sajip's Python 3-compatible branch; trying to get MySQL support working, and I have had a great deal of success, using PyMySQL as a back end, rather than MySQLdb.
>
> MySQLdb does not support Python3, and I haven't seen any indication online that it will be supporting Python3 any time soon.

That's not entirely true actually, I talked to Andy Dustman (MySQLdb's author earlier this year while researching the bits needed for porting) and he mentioned that several people ported it over the Python 3:

- http://sourceforge.net/projects/mysql-python/forums/forum/70460/topic/3831691
- https://github.com/davispuh/MySQL-for-Python-3

Quoting him from his private reply: "I use 1.2.3 regularly with Django, so if there's serious interest in a Python 3 version, I'll try to find the time and energy to work on it again."

As to which of those unofficial ports is the "correct" one, I can't help. But it would certainly be helpful to get in touch with Andy before dismissing MySQLdb altogether.

That's great to hear, that there's still at least some motivation to move that project forward.

My goal has been to help Vinay out with his Python 3 drive, by debugging the MySQL backend, and dropping this adapter in has been a way to at least get the rest of the backend code exercised. 

 

> PyMySQL is a pure python implementation of PEP 249 for MySQL, and supports Python 2.4 - 3.2, and MySQL 4.1 and higher. It is nearly a plug-in-replacement for MySQLdb  (although there are a few compatibility issues that I have tried to take care of in Django backend code)
>
> My approach so far has been to patch django/db/backends/mysql:
>    - Try to import MySQLdb, and then MySQLdb.converters and MySQLdb.constants.
>    - If that fails, try to import pymysql, and pymysql.converters and pymysql.constants
>    - Whichever succeeds, set a module-level variable 'backend' to indicate the backend that we are using.
>
>    - Patch the data-type conversions appropriately, depending on the backend that was used.
>
> So far, that's it -- and I'm down to just a couple of unit test failures, but those may even be Python3 issues; I haven't tracked them all down yet.
>
> My questions, then:
>
> 1. Is PyMySQL appropriate for django-core, in the opinion of the core devs? There isn't another stable Python 3-compatible mysql adapter, as far as I know, and I don't think Django could move to Python 3 support without at least the currently-supported databases.

I think it's better to ask whether it's appropriate to switch the backend library while doing the Python version jump. In my opinion it's not given the stability MySQLdb has shown over the years. At least I'm not seeing reasons to get rid of it.

Well, it sounds like moving Django to Python 3, even with MySQLdb support, would necessitate a simultaneous upgrade in MySQLdb anyway -- if there's no official version right now that support both 2 and 3, then we would have to upgrade both at the same time. Appreciated, though, that MySQLdb has a longer history, but it would still be at least some new code.

And I wasn't trying to get rid of it, rather to support both of them at the same time, given that they have the same interface, and only a few internal differences, which can be smoothed over in module initialization.

 

> 2. Is this the right approach? I know that in the past, we have had multiple backends for PostgreSQL, in different modules under django.db.backends, but in this case, the modules are almost 100% compatible, except for the module names themselves, and the calling conventions for the data conversions. It feels more like the situation with JSON libraries, where we just load whichever one is available, and present the same outward api to the developer.

IMO it's not, database backends vary in the way to speak to the database, so if -- and only if it's really needed to introduce another backend for MySQL -- it should be independent from the current one.

> 3. Code speaks, I know -- what's the best way to share this? I've sent Vinay a patch, but that's for his Py3k branch, and might not get the audience that something like this needs. I'm doing it for Py3k support, but ideally it would work just as well under Python 2.x. Should I set up a public repository somewhere, or just open a ticket and attach patches?

I asked Martin von Löwis who maintains the Py3K branch in Django official SVN repo to review Vinay's changes and merge them, as long as you manage to get patches in line with their work I think we're fine.
 

Jannis

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To post to this group, send email to django-d...@googlegroups.com.
To unsubscribe from this group, send email to django-develop...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

[Vinay Sajip]

On Dec 7, 8:38 pm, Ian Clelland <clell...@gmail.com> wrote:
>
> 3. Code speaks, I know -- what's the best way to share this? I've sent
> Vinay a patch, but that's for his Py3k branch, and might not get the
> audience that something like this needs. I'm doing it for Py3k support, but
> ideally it would work just as well under Python 2.x. Should I set up a
> public repository somewhere, or just open a ticket and attach patches?
>
> Thoughts? Flames?

Ian,

I have a clone of Dāvis' repo which supports MySQLdb on Python 3:

https://github.com/vsajip/MySQL-for-Python-3

(forked from https://github.com/davispuh/MySQL-for-Python-3)

I mentioned in a message to you that I couldn't use it because it used
str.format rather than % formatting, but actually that was easy enough
to patch. I didn't have to make the Django changes called for in your
patch, and the test suite is running now with the MySQLdb backend. I
got some warnings for utf-8 bytestrings as fixtures were loaded, but
it's run the first 275 or so tests without errors or failures so I'm
going to let it run to see how far it gets. I will push my changes to
the above repo soon.

Regards,

Vinay Sajip

[Ian Clelland]

On Wed, Dec 7, 2011 at 1:07 PM, Alex Gaynor <alex....@gmail.com> wrote:

I'm saying database backends need badly to me refactored to split up these two seperate concerns.

Agreed, then. Is there a ticket for this already? (I don't see anything in the DB component, looking for 'backend' anywhere in the summary/keywords/description, but I will allow that my trac-fu may be weak)

I'm looking into what it would take to separate these concerns.

[Alex Gaynor]

--

You received this message because you are subscribed to the Google Groups "Django developers" group.
To post to this group, send email to django-d...@googlegroups.com.
To unsubscribe from this group, send email to django-develop...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.

No, there isn't a ticket for this.

[Vinay Sajip]

On Dec 7, 8:38 pm, Ian Clelland <clell...@gmail.com> wrote:

> Thoughts?

The Django test run using MySQLdb at

https://github.com/vsajip/MySQL-for-Python-3

completed with 24 failures, 9 errors, so your PyMySQL results are
better at 5 failures, 2 errors. I will dig in a little further into
these issues. Details at https://gist.github.com/1446201 - total run
time was 9737 secs, BTW, but that is quite possibly down to
differences between our machines.

Regards,

Vinay Sajip

[Vinay Sajip]

On Dec 7, 8:38 pm, Ian Clelland <clell...@gmail.com> wrote:

> Thoughts?

A further update: of my 24 failures, 17 are in multiple_database, as
are 3 failures. But I get the same errors with unpatched Django 2.x
running with the standard 2.x MySQLdb adapter, so it might be
something to do with my MySQL configuration.

Regards,

Vinay Sajip

[Ian Clelland]

There also seem to be a number of unicode-related errors there (mixed collations; unrecognized characters) -- Could that be related to the lines in your patched mysql/base.py:

            if not PY3:

                kwargs['use_unicode'] = True

use_unicode used to be unconditional on the database connection; now you've removed it, but just for Python 3.

[Vinay Sajip]

On Dec 8, 6:46 am, Ian Clelland <clell...@gmail.com> wrote:

> There also seem to be a number of unicode-related errors there (mixed
> collations; unrecognized characters) -- Could that be related to the lines
> in your patched mysql/base.py:
>
>             if not PY3:
>                 kwargs['use_unicode'] = True
>
> use_unicode used to be unconditional on the database connection; now you've
> removed it, but just for Python 3.

I removed it because the Python 3 adapter doesn't recognise the kwarg;
I thought it might be that under Python 3 it would be implicitly true.
Possibly I need to check the detail of where that value is used in the
2.x adapter and see what the 3.x adapter does in that area.

Regards,

Vinay Sajip

[Vinay Sajip]

On Dec 8, 6:46 am, Ian Clelland <clell...@gmail.com> wrote:
>

> use_unicode used to be unconditional on the database connection; now you've
> removed it, but just for Python 3.
>

I looked into it a little further - the use_unicode flag is just used
in the 2.x constructor to set default converters to return unicode
instead of str. This would be implicitly always unicode in the 3.x
adapter.

I also looked further into one of the collation errors -
test_serialize_unicode. From what I can see, the query is converted to
utf-8 bytes and passed to MySQL, which returns the error. I can't see
where the Swedish collation it refers to comes from. Will continue to
investigate ...

Regards,

Vinay Sajip

[Vinay Sajip]

On Dec 8, 6:46 am, Ian Clelland <clell...@gmail.com> wrote:
>

> There also seem to be a number of unicode-related errors there (mixed
> collations; unrecognized characters) -- Could that be related to the lines

I'm also getting the same errors on 2.x with the 2.x adapter on
unpatched Django - so it might well be to do with my MySQL
installation (stock 5.1.58 for Ubuntu 64-bit).

It might be worth trying with my latest Django repo and my patched
MySQLdb on your installation, if you have the time and inclination...

BTW are you on IRC? If so, what nickname do you go by?

Regards,

Vinay Sajip

[Ian Clelland]

On Wed, Dec 7, 2011 at 11:40 PM, Vinay Sajip <vinay...@yahoo.co.uk> wrote:

On Dec 8, 6:46 am, Ian Clelland <clell...@gmail.com> wrote:
>

> There also seem to be a number of unicode-related errors there (mixed
> collations; unrecognized characters) -- Could that be related to the lines

I'm also getting the same errors on 2.x with the 2.x adapter on
unpatched Django - so it might well be to do with my MySQL
installation (stock 5.1.58 for Ubuntu 64-bit).

Could certainly be. The swedish collation you are seeing ought to be the compiled-in default, if nothing else has been specified for the database or the table.

It might be worth trying with my latest Django repo and my patched
MySQLdb on your installation, if you have the time and inclination...

I'll see what I can do about that, if I can get it to compile

 

BTW are you on IRC? If so, what nickname do you go by?

Not all the time; I just fired it up, though. You can find me as 'clelland' on freenode 

[Karen Tracey]

On Thu, Dec 8, 2011 at 2:40 AM, Vinay Sajip <vinay...@yahoo.co.uk> wrote:

On Dec 8, 6:46 am, Ian Clelland <clell...@gmail.com> wrote:
>

> There also seem to be a number of unicode-related errors there (mixed
> collations; unrecognized characters) -- Could that be related to the lines

I'm also getting the same errors on 2.x with the 2.x adapter on
unpatched Django - so it might well be to do with my MySQL
installation (stock 5.1.58 for Ubuntu 64-bit).

For all the Django test suite tests to pass, you must have a test database that uses the utf-8 encoding, not latin1. (The tests uses characters that have no encoding in latin1.) See the note under: https://docs.djangoproject.com/en/1.3/internals/contributing/#using-another-settings-module. If your MySQL install hasn't been configured to use utf8 instead of latin1 by default then you can set TEST_CHARSET to get the test database created with the necessary encoding.

Karen

[Tom Evans]

On Wed, Dec 7, 2011 at 8:38 PM, Ian Clelland <clel...@gmail.com> wrote:
> Hi, all ---
>
> This is a proposal to add support in Django for PyMySQL[1] as an optional
> replacement for MySQLdb.
>

> …

>
> PyMySQL is a pure python implementation of PEP 249 for MySQL, and supports
> Python 2.4 - 3.2, and MySQL 4.1 and higher. It is nearly a
> plug-in-replacement for MySQLdb  (although there are a few compatibility
> issues that I have tried to take care of in Django backend code)
>

Hi Ian

Have you checked any performance testing comparing PyMySQL with MySQLdb?

Ideally, instead of just a single run, could we have some a/b
performance testing? At $JOB we use ministat[1] to statistically judge
a difference, we normally do a minimum of 6 runs of each in this order
- abababaaabbb. Record the times in a text file for each option, and
give to ministat to calculate min/max/median/mean/standard deviation
and calculate the confidence of a difference in the results.

An example with made up data (hope you have proportional fonts):

x mysqldb
+ pymysql
+--------------------------------------------------------------------------+
| + |
|x x x + x x * + + +|
| |____________A____|M_____|___________AM_________________| |
+--------------------------------------------------------------------------+
N Min Max Median Avg Stddev
x 6 2870 3200 3110 3046.6667 127.22683
+ 6 3050 3600 3300 3291.6667 190.83151
Difference at 95.0% confidence
245 +/- 208.615
8.04158% +/- 6.84733%
(Student's t, pooled s = 162.178)

Doing benchmarks is good, but having confidence in them is better.
There's a good post by Zed Shaw (well, it's a bit sweary and ranty) on
this topic as well[2]. If you aren't blessed with using FreeBSD, there
is a linux port of ministat here[3].

If you like, point me at some code and I'll run some benchmarks.

Cheers

Tom

[1] http://ivoras.net/blog/tree/2009-12-02.using-ministat.html
[2] http://zedshaw.com/essays/programmer_stats.html
[3] https://github.com/codemac/ministat

[Jens Diemer]

Am 07.12.2011 21:38, schrieb Ian Clelland:
> PyMySQL is a pure python implementation of PEP 249 for MySQL, and supports
> Python 2.4 - 3.2, and MySQL 4.1 and higher.

Another goal of PyMySQL would be to use Django + MySQL with PyPy, isn't it?

See also:
https://groups.google.com/group/django-users/browse_thread/thread/cbef429d014c1ad9/

--

Mfg.

Jens Diemer

----
http://www.jensdiemer.de

[Ian Clelland]

On Thursday, December 8, 2011, Jens Diemer <python...@jensdiemer.de> wrote:
> Am 07.12.2011 21:38, schrieb Ian Clelland:
>>
>> PyMySQL is a pure python implementation of PEP 249 for MySQL, and supports
>> Python 2.4 - 3.2, and MySQL 4.1 and higher.
>
> Another goal of PyMySQL would be to use Django + MySQL with PyPy, isn't it?
>

You know, I hadnt thought of it, but that would definitely go a long way towards making that possible!

Ill see if I can polish up a proposal for splitting up the backend code into SQL and driver code, but I'll try to get a separate pymysql backend up in a repository somewhere for people to try out as well.

Ian

> See also: https://groups.google.com/group/django-users/browse_thread/thread/cbef429d014c1ad9/
>
> --
>
> Mfg.
>
> Jens Diemer
>
>
> ----
> http://www.jensdiemer.de
>

> --
> You received this message because you are subscribed to the Google Groups "Django developers" group.
> To post to this group, send email to django-d...@googlegroups.com.
> To unsubscribe from this group, send email to django-develop...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
>
>

[Ian Clelland]

On Thu, Dec 8, 2011 at 7:46 AM, Ian Clelland <clel...@gmail.com> wrote:

On Thursday, December 8, 2011, Jens Diemer <python...@jensdiemer.de> wrote:
> Am 07.12.2011 21:38, schrieb Ian Clelland:
>>
>> PyMySQL is a pure python implementation of PEP 249 for MySQL, and supports
>> Python 2.4 - 3.2, and MySQL 4.1 and higher.
>
> Another goal of PyMySQL would be to use Django + MySQL with PyPy, isn't it?
>

You know, I hadnt thought of it, but that would definitely go a long way towards making that possible!

Ill see if I can polish up a proposal for splitting up the backend code into SQL and driver code, but I'll try to get a separate pymysql backend up in a repository somewhere for people to try out as well.

There is now a PyMySQL backend available on Github --  https://github.com/clelland/django-mysql-pymysql

It leverages a lot of the code in django.db.backends.mysql; importing it where it can, and duplicating it where it has to. I hope to have a proposal by the weekend for splitting out the database adapter code from the backends, and then this project can get a lot lighter :)

If you use my patches to PyMySQL, at https://github.com/clelland/PyMySQL, then the full django unit test suite (from trunk) will pass under Python 2.6.7. There are some character set handling issues with the upstream version at the moment.

(And at this point, I'm veering dangerously close to django-users territory; If needed, I'll continue this thread with an announcement there)