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
使用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 . . .]](https://img.haomeiwen.com/i413992/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()
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
网友评论