How to use mysql-connector-python 2.1.5 on Django 1.10?

MySQL Connector/Python currently not available from PyPI

When you want use mysql-connector-python as db backend in Django, the first thing you noticed is that the latest version of mysql-connector-python on PyPI is 2.0.4, but official version is 2.1.5.

The official developer explained this.(But why not host mysql-connector-python on PyPI?!).

so, you can bypass this problem. Add follow line into requirements.txt:

https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.5.tar.gz#egg=mysql-connector-python

when you configured Connector/Python Django Backend, and run python manage.py migrate , you will meet error:

DatabaseError: Data truncated for column ‘applied’ at row 1

This is because mysql.connector.django cannot handle datetime with timezone.

You need patch 93c7b38.

when you fix this, you must drop all table, and re-run python manage.py migrate, you will meet another error:

  File "/path/to/venv/lib/python3.5/site-packages/mysql/connector/django/operations.py", line 223, in bulk_insert_sql
    return "VALUES " + ", ".join([items_sql] * num_values)
TypeError: can't multiply sequence by non-int of type 'tuple'

can’t multiply sequence by non-int of type ‘tuple’

This is because bulk_insert_sql ‘s third argument changed from int to list of placeholder rows, since Django 1.9.

You need another patch, patch 8dab00b. (Of cause, we doesn’t drop support of Django 1.6)


Now you can use mysql-connector-python 2.1.5 on Django 1.10, if you meet another problem, post the comment here!

Django的MySQL Driver配置

之前写过一篇文章《Django@Python3添加MySQL/MariaDB支持》,现在Django对MySQL的支持已经很好了,现在就说一说最佳实践吧。

PEP 249规定了Python的数据库API。MySQL主要有三种API实现:

  • MySQLdb 是Andy Dustman开发的、使用原生代码/C语言绑定的驱动,它已经开发了数十年。
  • mysqlclient 是MySQLdb的一个支持Python3的fork,并且可以无缝替换调MySQLdb。mysqlclient目前是MySQL在Django下的推荐选择。
  • MySQL Connector/Python 是Oracle写的,纯Python实现的客户端库。

以上所有的驱动都是线程安全的,且提供了连接池。MySQLdb 是唯一一个不支持Python3的。

如果你使用mysqlclient

settings.py中的配置如下:

# Database
# https://docs.djangoproject.com/en/1.10/ref/settings/#databases

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', #数据库引擎
        'NAME': 'test',                       #数据库名
        'USER': 'root',                       #用户名
        'PASSWORD': 'root',                   #密码
        'HOST': '',                           #数据库主机,默认为localhost
        'PORT': '',                           #数据库端口,MySQL默认为3306
        'OPTIONS': {
            'autocommit': True,
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
        },
    }
}

第14行主要是为了防止警告:

(mysql.W002) MySQL Strict Mode is not set for database connection 'default'

当然,要在requirements.txt中添加对mysqlclient的依赖:

mysqlclient==1.3.10

然后运行python manage.py migrate :

$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  。。。。。。

如果你使用MySQL Connector/Python

这么长时间了,Oracle官方的connector还是不太好用。

首先,一些补丁需要打:patch 93c7b38, patch 8dab00b要打上。

其次,这个包无法从PyPI安装了,只能从下载链接安装。

如果你要使用MySQL Connector/Python,首先,在requirements.txt中声明依赖:

https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.5.tar.gz#egg=mysql-connector-python

其次,在settings.py里面关于数据库的配置如下:

# Database
# https://docs.djangoproject.com/en/1.10/ref/settings/#databases

DATABASES = {
    'default': {
        'ENGINE': 'mysql.connector.django',   #数据库引擎
        'NAME': 'test',                       #数据库名
        'USER': 'root',                       #用户名
        'PASSWORD': 'root',                   #密码
        'HOST': 'localhost',
        'OPTIONS': {
            'autocommit': True,
        },
    }
}

然后运行python manage.py migrate