Django 与 MySQL 对接

环境和目标

mysql直接安装在Mac下

django在virtualenv中运行

已经在virtualenv下安装django，并使用sqlite3搭建过简单的网站；单独使用过mysql，这次的目的是进行virtualenv下的django与系统环境下的MySQL对接。

步骤

在 MySQL 中创建新数据库 和 账号，并授权

在MySQL创建一个新数据库web

mysql> create database web;
Query OK, 1 row affected (0.01 sec)

设置为当前使用的数据库

mysql> use web
Database changed
mysql> show tables;
Empty set (0.00 sec)

创建django访问web数据库的账号，并在数据库mysql的表user中检查

mysql> create user webproject@localhost identified by 'web';
ERROR 1396 (HY000): Operation CREATE USER failed for 'webproject'@'localhost'

mysql> create user 'webproject@localhost' identified by 'web';
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| sys                |
| test               |
| web                |
+--------------------+
7 rows in set (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> select * from user where User='webproject';
+-----------+------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User       | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | webproject | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *7F3BF7031A324F9FA79930B44A098C84FA3FBB97 | N                | 2015-11-28 10:31:31   |              NULL | N              |
+-----------+------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
1 row in set (0.00 sec)

切回数据库web

mysql> use web
Database changed

对webproject@localhost开放访问数据库web中所有表的所有权限

mysql> grant all privileges on web.* to webproject@localhost;
Query OK, 0 rows affected (0.00 sec)

刷新权限

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

创建新 django 项目，设置参数，进行对接

进入virtualenv环境

➜  django  source bin/activate

创建项目web

➜  project  django-admin startproject web
➜  project  ls
web
➜  project  web
➜  web  ls
manage.py web

修改settings.py文件：和数据库对接的参数、时区

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'web',
        'USER': 'webproject',
        'PASSWORD': 'web',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}

# TIME_ZONE = 'UTC'
TIME_ZONE = 'Asia/Shanghai'

或者这样

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {
            'read_default_file': '/path/to/my.cnf',
        },
    }
}


# my.cnf
[client]
database = web
user = webproject
password = web
default-character-set = utf8

• connecting-to-the-database
• databases
• setting-django-up-to-use-mysql

使用Git进行版本管理

➜  web  git init
➜  web git:(master) ✗ git add .
➜  web git:(master) ✗ git commit -m 'init django site'

生成迁移策略文件

➜  web git:(master) ✗ python manage.py makemigrations
Traceback (most recent call last):

django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named MySQLdb

MySQL 有三个接口程序，官网资料

这里按报错，是因为还没有安装与 MySQL 对接的接口程序，官方的接口程序中，前两个都在PyPI中发布了，可以通过 pip 进行安装，第三个没试过。

先按照提示用 mysqldb。

➜  web git:(master) ✗ pip install MysqL-python
Collecting MysqL-python
  Downloading MySQL-python-1.2.5.zip (108kB)
    100% |████████████████████████████████| 110kB 8.3kB/s
    Complete output from command python setup.py egg_info:
    sh: mysql_config: command not found
    Traceback (most recent call last):
      File "<string>", line 20, in <module>
      File "/private/var/folders/36/tdbs35wx1sx4d6nm4hrmwy0h0000gn/T/pip-build-mIngAC/MysqL-python/setup.py", line 17, in <module>
        metadata, options = get_config()
      File "setup_posix.py", line 43, in get_config
        libs = mysql_config("libs_r")
      File "setup_posix.py", line 25, in mysql_config
        raise EnvironmentError("%s not found" % (mysql_config.path,))
    EnvironmentError: mysql_config not found

    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /private/var/folders/36/tdbs35wx1sx4d6nm4hrmwy0h0000gn/T/pip-build-mIngAC/MysqL-python

又报错，找不到mysql_config

向 环境变量添加mysql_config的路径

PATH=$PATH:/usr/local/mysql/bin/:

设置为全局变量

export PATH

再次安装MysqL-python

➜  web git:(master) ✗ pip install MysqL-python
Collecting MysqL-python
  Using cached MySQL-python-1.2.5.zip
Building wheels for collected packages: MysqL-python
  Running setup.py bdist_wheel for MysqL-python
  Stored in directory: /Users/chao/Library/Caches/pip/wheels/8c/0d/11/d654cad764b92636ce047897dd2b9e1b0cd76c22f813c5851a
Successfully built MysqL-python
Installing collected packages: MysqL-python
Successfully installed MysqL-python-1.2.5

或者，可以安装mysqlclient

➜  web git:(master) ✗ pip install mysqlclient
Collecting mysqlclient
Installing collected packages: mysqlclient
Successfully installed mysqlclient-1.3.7

再次生成策略文件

➜  web git:(master) ✗ python manage.py makemigrations
No changes detected

执行策略文件中的策略

➜  web git:(master) ✗ python manage.py migrate
Operations to perform:
  Synchronize unmigrated apps: staticfiles, messages
  Apply all migrations: admin, contenttypes, auth, sessions
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
  Installing custom SQL...
Running migrations:
  Rendering model states... DONE
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying sessions.0001_initial... OK

表 生成成功。django会自动创建一些自身需要的表。

mysql> show tables;
+----------------------------+
| Tables_in_web              |
+----------------------------+
| auth_group                 |
| auth_group_permissions     |
| auth_permission            |
| auth_user                  |
| auth_user_groups           |
| auth_user_user_permissions |
| django_admin_log           |
| django_content_type        |
| django_migrations          |
| django_session             |
+----------------------------+
10 rows in set (0.00 sec)

创建站点管理员账号，运行服务器，登陆站点后台

创建一个django的管理员账号

➜  web git:(master) ✗ python manage.py createsuperuser
Username (leave blank to use 'chao'): admin
Email address: admin@example.com
Password:
Password (again):
Superuser created successfully.

可以看到 中添加了对应的一条

mysql> select * from auth_user;
+----+-------------------------------------------------------------------------------+------------+--------------+----------+------------+-----------+-------------------+----------+-----------+----------------------------+
| id | password                                                                      | last_login | is_superuser | username | first_name | last_name | email             | is_staff | is_active | date_joined                |
+----+-------------------------------------------------------------------------------+------------+--------------+----------+------------+-----------+-------------------+----------+-----------+----------------------------+
|  1 | pbkdf2_sha256$20000$Hr0nUzl4tsNy$SKvpb5EzjyIOGldrtb07xQpKa5VY9/OPw50q2neRMT0= | NULL       |            1 | admin    |            |           | admin@example.com |        1 |         1 | 2015-11-28 03:36:03.513564 |
+----+-------------------------------------------------------------------------------+------------+--------------+----------+------------+-----------+-------------------+----------+-----------+----------------------------+
1 row in set (0.00 sec)

运行服务器

➜  web git:(master) ✗ python manage.py runserver
Performing system checks...

System check identified no issues (0 silenced).
November 28, 2015 - 11:58:35
Django version 1.8.6, using settings 'web.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CONTROL-C.
[28/Nov/2015 11:58:50] "GET / HTTP/1.1" 200 1767

在浏览器中通过http://127.0.0.1:8000/访问，可以看到信息

![Uploading 屏幕快照 2015-11-28 下午12.00.40_322750.png . . .]](http://upload-images.jianshu.io/upload_images/413992-09720f35b6a594c7.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

登陆管理后台

http://127.0.0.1:8000/admin/

输入创建的用户名和密码，登陆

屏幕快照 2015-11-28 下午12.02.45.png

对数据库进行操作

ORM

• 创建一个条目

插入一条，并写入数据库

>>> from blog.models import Blog
>>> b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.')
>>> b.save()

或者

create(**kwargs)

p = Person.objects.create(first_name="Bruce", last_name="Springsteen")

• 修改、保存

>>> b5.name = 'New name'
>>> b5.save()

• 增加多个条目

>>> john = Author.objects.create(name="John")
>>> paul = Author.objects.create(name="Paul")
>>> george = Author.objects.create(name="George")
>>> ringo = Author.objects.create(name="Ringo")

或

>>> entry.authors.add(john, paul, george, ringo)

• all()

all_entries = Entry.objects.all()

• filter(**kwargs)

Entry.objects.filter(pub_date__year=2006)

等同于

SELECT * FROM blog_entry WHERE pub_date__year = '2006';

• exclude(**kwargs)

• get(**kwargs)，获取单个目标

Entry.objects.get(pk=1)

• 分片

Entry.objects.all()[:5]，返回头五个对象，(LIMIT 5)

Entry.objects.all()[5:10]，偏移五，获取五个

• order_by

按某一列的字母顺序排列

>>> Entry.objects.order_by('headline')[0]

• get_or_create(defaults=None, **kwargs)

obj, created = Person.objects.get_or_create(first_name='John', last_name='Lennon', defaults={'birthday': date(1940, 10, 9)})

• update_or_create(defaults=None, **kwargs)

• count()

Entry.objects.count()

等同于

SELECT COUNT(*)

• delete()

>>> blogs = Blog.objects.all()

# 删除所有的博客和条目对象
>>> blogs.delete()

• save()

ORM 查询

查询

SQL 语句

创建模型

class Person(models.Model):
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    birth_date = models.DateField(...)

• raw(raw_query, params=None, translations=None)

Person.objects.raw('SELECT * FROM myapp_person')

等同于

Person.objects.all()

first_person = Person.objects.raw('SELECT * FROM myapp_person')[0] 索引

• 直接执行自定义的 SQL

from django.db import connection

def my_custom_sql(self):
    cursor = connection.cursor()

    cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])

    cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
    row = cursor.fetchone()

    return row

raw