Django配置MySQL数据库

在部署 Django开发环境(linux) 后，我们可以开发Django。但是，默认使用的数据库是sqlite，和生产环境往往不同。所以，我们通常会把Django数据库后端替换成MySQL。

但是，需要注意的是，我们不能把数据库访问账号密码直接配置在 manage.py 中，也不能把包含账号信息提交到git仓库中，所以需要有一定的配置方法来实现数据库连接。

准备数据库

  • 使用以下方式创建应用数据库:

    create database myappdb character set utf8;
create user myapp@'%' identified by 'MyPass';
grant all privileges on myappdb.* to myapp@'%';
flush privileges;

安装mysqlclient模块

在安装 Python virtualenv 之后，通过 pip 安装 mysqlclient 模块:

pip install mysqlclient

如果出现报错:

ERROR: Command errored out with exit status 1:
 command: /home/admin/venv3/bin/python -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-3m5z471x/mysqlclient_891b537df843484ba930dc9520c76710/setup.py'"'"'; __file__='"'"'/tmp/pip-install-3m5z471x/mysqlclient_891b537df843484ba930dc9520c76710/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-pip-egg-info-1mbvlvf_
     cwd: /tmp/pip-install-3m5z471x/mysqlclient_891b537df843484ba930dc9520c76710/
Complete output (15 lines):
/bin/sh: mysql_config: command not found
/bin/sh: mariadb_config: command not found
/bin/sh: mysql_config: command not found
mysql_config --version
mariadb_config --version
mysql_config --libs

则需要安装 mysql 开发包，例如 mariadb-devel ，安装以后系统会有 mysql_config 工具:

需要提前安装 mysql-devel 或 mariadb-devel 才能执行 pip install mysqlclient
# yum install mysql-devel
yum install mariadb-devel

备注

在 Django开发环境(linux) 对于 pip install mysqlclient 有处理案例

警告

我最近的实践发现在CentOS 7环境提供的是 MariaDB，此时编译安装 mysqlclient 模块始终报错，参考 How to connect Python programs to MariaDB ，看起来社区采用了新的 mariadb 来连接MariaDB，我以为 mysqlclient 已经不能兼容。 实际上并不是这样 ， mysqlclient 需要采用 MariaDB 的较高版本才能正常编译。我验证 安装MariaDB v10.11 则编译安装没有任何问题。

解决 mysqlclient 模块安装

警告

我这里的挫折实际上是因为我采用了CentOS(aliOS 7.2)发行版内置的 MariaDB v5.5 导致的，浪费了我两天时间。实际上，当重新 安装MariaDB v10.11 之后问题引刃而解。

这次遇到一个报错，和之前 Django配置MySQL数据库 不同:

pip 安装 mysqlclient 报错
Collecting mysqlclient (from -r requirements.txt (line 9))
  Using cached mysqlclient-2.2.0.tar.gz (89 kB)
  Installing build dependencies ... done
  Getting requirements to build wheel ... error
  error: subprocess-exited-with-error

  × Getting requirements to build wheel did not run successfully.
  │ exit code: 1
  ╰─> [25 lines of output]
      Trying pkg-config --exists mysqlclient
      Command 'pkg-config --exists mysqlclient' returned non-zero exit status 1.
      Trying pkg-config --exists mariadb
      Command 'pkg-config --exists mariadb' returned non-zero exit status 1.
      Traceback (most recent call last):
        File "/home/admin/onesre_venv3/lib/python3.11/site-packages/pip/_vendor/pyproject_hooks/_in_process/_in_process.py", line 353, in <module>
          main()
        File "/home/admin/onesre_venv3/lib/python3.11/site-packages/pip/_vendor/pyproject_hooks/_in_process/_in_process.py", line 335, in main
          json_out['return_val'] = hook(**hook_input['kwargs'])
                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        File "/home/admin/onesre_venv3/lib/python3.11/site-packages/pip/_vendor/pyproject_hooks/_in_process/_in_process.py", line 118, in get_requires_for_build_wheel
          return hook(config_settings)
                 ^^^^^^^^^^^^^^^^^^^^^
        File "/tmp/pip-build-env-92qfmlql/overlay/lib/python3.11/site-packages/setuptools/build_meta.py", line 355, in get_requires_for_build_wheel
          return self._get_build_requires(config_settings, requirements=['wheel'])
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        File "/tmp/pip-build-env-92qfmlql/overlay/lib/python3.11/site-packages/setuptools/build_meta.py", line 325, in _get_build_requires
          self.run_setup()
        File "/tmp/pip-build-env-92qfmlql/overlay/lib/python3.11/site-packages/setuptools/build_meta.py", line 341, in run_setup
          exec(code, locals())
        File "<string>", line 154, in <module>
        File "<string>", line 48, in get_config_posix
        File "<string>", line 27, in find_package_name
      Exception: Can not find valid pkg-config name.
      Specify MYSQLCLIENT_CFLAGS and MYSQLCLIENT_LDFLAGS env vars manually
      [end of output]

  note: This error originates from a subprocess, and is likely not a problem with pip.
error: subprocess-exited-with-error

× Getting requirements to build wheel did not run successfully.
│ exit code: 1
╰─> See above for output.

note: This error originates from a subprocess, and is likely not a problem with pip.

这里可以看到 pkg-config --exists mysqlclient 和 pkg-config --exists mariadb 都是返回 1 (失败)。我手工执行了一下，确实 echo $? 显示 1

仔细一看，原来这个 pkg-config 是操作系统默认的 /usr/bin/pkg-config 。这里通过 rpm -qf /usr/bin/pkg-config 可以看出是属于 pkgconfig-0.27.1-4.1.alios7.x86_64 ，显然不会获得正确的包信息

PyMySQL / mysqlclient / README.md 提供了一个线索:

定制编译时指定环境变量
export MYSQLCLIENT_CFLAGS=`pkg-config mysqlclient --cflags`
export MYSQLCLIENT_LDFLAGS=`pkg-config mysqlclient --libs`
pip install mysqlclient

既然由于操作系统的 pkg-config 无法正常工作，那么该如何设置环境变量呢?

我找了自己部署的一台 Fedora 开发环境( 吐槽一下公司魔改的CentOS 社区CentOS 7确实存在这个问题)，可以看到:

在正确的环境中执行 pkg-config 获取环境变量
$ pkg-config mariadb --cflags
-I/usr/include/mysql
$ pkg-config mariadb --libs
-lmariadb -ldl -lm -lssl -lcrypto -lz

  • 所以修正安装方法:

设置正确 mysqlclient 环境变量
export MYSQLCLIENT_CFLAGS="-I/usr/include/mysql"
export MYSQLCLIENT_LDFLAGS="-lmariadb -ldl -lm -lssl -lcrypto -lz"

  • 然后再次执行就可以完成 不过我再次执行安装报错似乎不兼容:

提供了mysqlient的编译环境变量，但是编译报错
  Building wheel for mysqlclient (pyproject.toml) ... error
  error: subprocess-exited-with-error

  × Building wheel for mysqlclient (pyproject.toml) did not run successfully.
  │ exit code: 1
  ╰─> [49 lines of output]
      # Options for building extention module:
        extra_compile_args: ['-I/usr/include/mysql', '-std=c99']
        extra_link_args: ['-lmariadb', '-ldl', '-lm', '-lssl', '-lcrypto', '-lz']
        define_macros: [('version_info', (2, 2, 0, 'final', 0)), ('__version__', '2.2.0')]
      running bdist_wheel
      running build
      running build_py
      creating build
      creating build/lib.linux-x86_64-cpython-311
      creating build/lib.linux-x86_64-cpython-311/MySQLdb
      copying src/MySQLdb/cursors.py -> build/lib.linux-x86_64-cpython-311/MySQLdb
      copying src/MySQLdb/times.py -> build/lib.linux-x86_64-cpython-311/MySQLdb
      copying src/MySQLdb/_exceptions.py -> build/lib.linux-x86_64-cpython-311/MySQLdb
      copying src/MySQLdb/release.py -> build/lib.linux-x86_64-cpython-311/MySQLdb
      copying src/MySQLdb/connections.py -> build/lib.linux-x86_64-cpython-311/MySQLdb
      copying src/MySQLdb/__init__.py -> build/lib.linux-x86_64-cpython-311/MySQLdb
      copying src/MySQLdb/converters.py -> build/lib.linux-x86_64-cpython-311/MySQLdb
      creating build/lib.linux-x86_64-cpython-311/MySQLdb/constants
      copying src/MySQLdb/constants/FIELD_TYPE.py -> build/lib.linux-x86_64-cpython-311/MySQLdb/constants
      copying src/MySQLdb/constants/CLIENT.py -> build/lib.linux-x86_64-cpython-311/MySQLdb/constants
      copying src/MySQLdb/constants/FLAG.py -> build/lib.linux-x86_64-cpython-311/MySQLdb/constants
      copying src/MySQLdb/constants/ER.py -> build/lib.linux-x86_64-cpython-311/MySQLdb/constants
      copying src/MySQLdb/constants/CR.py -> build/lib.linux-x86_64-cpython-311/MySQLdb/constants
      copying src/MySQLdb/constants/__init__.py -> build/lib.linux-x86_64-cpython-311/MySQLdb/constants
      running egg_info
      writing src/mysqlclient.egg-info/PKG-INFO
      writing dependency_links to src/mysqlclient.egg-info/dependency_links.txt
      writing top-level names to src/mysqlclient.egg-info/top_level.txt
      reading manifest file 'src/mysqlclient.egg-info/SOURCES.txt'
      reading manifest template 'MANIFEST.in'
      adding license file 'LICENSE'
      writing manifest file 'src/mysqlclient.egg-info/SOURCES.txt'
      copying src/MySQLdb/_mysql.c -> build/lib.linux-x86_64-cpython-311/MySQLdb
      running build_ext
      building 'MySQLdb._mysql' extension
      creating build/temp.linux-x86_64-cpython-311
      creating build/temp.linux-x86_64-cpython-311/src
      creating build/temp.linux-x86_64-cpython-311/src/MySQLdb
      gcc -pthread -Wsign-compare -DNDEBUG -g -fwrapv -O3 -Wall -fPIC "-Dversion_info=(2, 2, 0, 'final', 0)" -D__version__=2.2.0 -I/home/admin/onesre_venv3/include -I/usr/local/include/python3.11 -c src/MySQLdb/_mysql.c -o build/temp.linux-x86_64-cpython-311/src/MySQLdb/_mysql.o -I/usr/include/mysql -std=c99
      src/MySQLdb/_mysql.c: In function ‘_mysql_ConnectionObject_Initialize’:
      src/MySQLdb/_mysql.c:536:13: warning: implicit declaration of function ‘mysql_optionsv’; did you mean ‘mysql_options’? [-Wimplicit-function-declaration]
        536 |             mysql_optionsv(&(self->connection), MYSQL_OPT_SSL_ENFORCE, (void *)&enforce_tls);
            |             ^~~~~~~~~~~~~~
            |             mysql_options
      src/MySQLdb/_mysql.c:536:49: error: ‘MYSQL_OPT_SSL_ENFORCE’ undeclared (first use in this function)
        536 |             mysql_optionsv(&(self->connection), MYSQL_OPT_SSL_ENFORCE, (void *)&enforce_tls);
            |                                                 ^~~~~~~~~~~~~~~~~~~~~
      src/MySQLdb/_mysql.c:536:49: note: each undeclared identifier is reported only once for each function it appears in
      error: command '/usr/local/bin/gcc' failed with exit code 1
      [end of output]

  note: This error originates from a subprocess, and is likely not a problem with pip.
  ERROR: Failed building wheel for mysqlclient

这个问题我最后做了多次安装对比，发现是旧版本 mariadb 的bug，当采用 安装MariaDB 采用社区仓库并安装 MariaDB 10.11 之后，这个问题引刃而解。

配置Django连接数据库

  • django一般有2种方式获取数据库连接信息:

    • 直接读取 /etc/my.cnf (当前Django官方推荐) : 例如这里可以创建一个 myapp.cnf 配置文件，对应上文创建的 myappdb 数据库访问账号

    • 使用环境变量

Django使用MySQL Client配置(推荐)

现在Django推荐采用直接读取操作系统安装的MySQL客户端配置来完成数据库连接配置。例如，通常我们的数据库访问配置 /etc/my.cnf 内容如下:

[client]
database = NAME
user = USER
password = PASSWORD
default-character-set = utf8

  • 修改 myapp/settings.py 数据库配置:

    DATABASES = {
    'default': {
         'ENGINE': 'django.db.backends.mysql',
         'OPTIONS': {
             'read_default_file': '/etc/onesredb.cnf',
         },
    }
}

Django使用数据库环境变量

django支持从环境变量中读取配置，所以可以将密码相关变量保存到环境中。对于使用Python virtualenv，可以在进入虚拟环境的最后激活配置 postactivate 中设置环境，并在 predeactivate 文件中 unset 环境变量。

  • 修订django的 myapp/settings.py 将:

    DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    }
}

修改成:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': get_env_variable('DATABASE_NAME'),
        'USER': get_env_variable('DATABASE_USER'),
        'PASSWORD': get_env_variable('DATABASE_PASSWORD'),
        'HOST': '',
        'PORT': '',
    }
}

  • 在 myapp/settings.py 开头加上:

    from django.core.exceptions import ImproperlyConfigured

def get_env_variable(var_name):
    try:
        return os.environ[var_name]
    except KeyError:
        error_msg = "Set the %s environment variable" % var_name
        raise ImproperlyConfigured(error_msg)

  • 最后在virtualenv虚拟机环境 venv3/bin/postactive 中加上数据库设置:

    export DATABASE_NAME='myappdb'
export DATABASE_USER='myapp'
export DATABASE_PASSWORD='MyPass'

  • 并在virtualenv虚拟机退出配置 venv3/bin/predeactivate 配置加上:

    unset DATABASE_NAME
unset DATABASE_USER
unset DATABASE_PASSWORD

数据库迁移

在完成上述两种数据库连接配置之一后，执行以下命令进行数据库migration:

python manage.py migrate

最后测试应用启动:

python manage.py runserver

参考