shell模式
1、概述
一般操作数据库表的时候都是通过views.py文件进行操作的
在view.py文件中操作表单
操作表都是操作model.py里面的对象
2、基本数据访问
2.1、查询数据1
进入到shell模式
终端输入:
python3 manage.py shell
导入sign应用下面的molders.py中的Event、Guest两张表
终端输入:
from sign.models import Event,Guest
利用table.objects.all()查询相对应数据库表的数据
终端输入:
>>> Event.objects.all()
<QuerySet [<Event: 糖猴子的发布会>, <Event: 糖糖>]>
>>> Guest.objects.all()
<QuerySet [<Guest: 张三>]>
2.2、插入数据
因为发布会涉及到时间,需要引包datetime模块
定义一个变量e1--在数据库里面暂时看不到相对应的数据
保存e1变量保存到数据库里面
终端输入
>>> from datetime import datetime
>>> e1 = Event(id = 3,name = '新建的发布会',limit = '3000',status = False,address = '普陀',start_time = datetime(2017,8,10,14,0,0))
>>> e1.save()
数据库页面展示
保存数据库信息的时候,警告信息如下(世界时相关)
>>> e1.save()
/usr/local/lib/python3.6/site-packages/django/db/models/fields/__init__.py:1451: RuntimeWarning: DateTimeField Event.start_time received a naive datetime (2017-08-10 14:00:00) while time zone support is active.
RuntimeWarning)
解决方案:需要退出shell模式之后,将setting.py文件中的 USE_TZ = False 修改成 USE_TZ = False即可
>>> quit()
monkey@MacBook-Pro-7 /usr/local/lib/python3.6/site-packages/guest python3 manage.py shell
Python 3.6.3 (default, Oct 17 2017, 00:27:06)
[GCC 4.2.1 Compatible Apple LLVM 8.1.0 (clang-802.0.42)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>>
>>> from sign.models import Event,Guest
>>>
>>> from datetime import datetime
>>>
>>> e1 = Event(id = 6,name = '新建的发布会4',limit = '3000',status = False,address = '普陀4',start_time = datetime(2017,8,13,14,0,0))
>>> e1.save()
>>>
数据库截图如下:
2.3、查询数据2
get方法
只支持精确查找
>>> e2 = Event.objects.get(name = "新建的发布会4")
>>> e2
<Event: 新建的发布会4>
>>> e2.address
'普陀4'
模糊搜索的保存
查询的数据不存在也会报错
>>> Event.objects.get(name = '发布会')
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/usr/local/lib/python3.6/site-packages/django/db/models/manager.py", line 85, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 380, in get
self.model._meta.object_name
sign.models.DoesNotExist: Event matching query does not exist.
filter方法
模糊搜索的时候直接输入的话会返回为空
Event.objects.filter(name = '发布会')
<QuerySet []>
不直接支持模糊查询,如果需要模糊搜索,需要在name后面加上__contains
Event.objects.filter(name__contains = '发布会')
<QuerySet [<Event: 糖猴子的发布会>, <Event: 新建的发布会>, <Event: 新建的发布会4>]>
2.4、删除数据
利用delete的方法删除数据
>>> e3 = Event.objects.get(name = '新建的发布会')
>>> e3.delete()
(1, {'sign.Guest': 0, 'sign.Event': 1})
>>>
>>> Event.objects.all()
<QuerySet [<Event: 糖猴子的发布会>, <Event: 糖糖>, <Event: 新建的发布会4>]>
>>>
2.5、更新数据
根据属性的唯一性,event_id来查询到相对应的数据
g1.realname = 'lisi' 更新嘉宾表中的数据
保存相对应更新的数据
>>> g1 = Guest.objects.get(event_id ='1')
>>>
>>> g1.realname = 'lisi'
>>> g1.save()
>>>
>>> Guest.objects.all()
<QuerySet [<Guest: lisi>]>
3、mysql相关操作姿势
3.1、安装mysql
安装步骤可参照:http://www.jianshu.com/p/fd3aae701db9
安装完毕mysql
终端输入:mysql -u root -p(需要登陆密码,首次输入安装页面给出的密码)
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 777
Server version: 5.7.20
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
3.2、查看当前数据库下面的所有库
进入到mysql环境,终端输入:show databases
如下代码,如果你一直用的都是安装时带的密码,所提示你重设代码
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
终端输入:mysql> set password = password('123456'); 设置密码
mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.02 sec)
再次终端输入:show databases就会显示当前数据库下面的所有库
3.3、使用库并且展示库下面的所有表
mysql> use sys; #选择使用数据库
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; #显示数据库下面的所有表
3.4、查看端口号
mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.01 sec)
3.5、退出mysql,终端输入:ifconfig | grep "inet " | grep -v 127.0.0.1 (Mac不仅仅需要输入的是ifconfig)查看IP地址
monkey@MacBook-Pro-7 ~ ifconfig | grep "inet " | grep -v 127.0.0.1
inet xx.xx.xx.xxx(ip地址) netmask 0xffffff00 broadcast 10.12.49.255
3.6、本地登陆
进入到mysql命令
终端输入:MySQL -uroot -p123456 创建登陆账号
monkey@MacBook-Pro-7 ~ mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 802
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
创建远程登陆用户并授权
mysql> grant all PRIVILEGES on test_db.* to root@'XX.XX.XX.XXX'(IP地址) identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
用mysql客户端 输入host(IP地址)、port、用户名、密码登陆即可
3.7、本地创建数据库表
4、修改Django数据库配置
4.1、setting文件下面添加mysql数据库连接信息
#配置mysql数据库链接
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'HOST': '127.0.0.1', #IP地址
'PORT': '3306', #端口号
'NAME': 'dev_guest', #表名
'USER': 'root', #账户
'PASSWORD': '123456', #密码
'OPTIONS': {
'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
},
}
}
4.2、__init__文件中,引入pysql来代替mysqldb
import pymysql
pymysql.install_as_MySQLdb()
5、切换后数据库的生成
将sqlite数据库切换成mysql之后,原先的数据都没有了,需要重新生成数据。
生成相对应的数据库表