Django笔记10：sqlite使用合集
2021-08-14 本文已影响0人
_百草_
0. cmd进入sqlite模式
C:\testing\pro_0814>>sqlite3 pro_0814/db.sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite>
1. 查看数据库表
sqlite> .tables
app_0814_blogarticles auth_user_user_permissions
auth_group django_admin_log
auth_group_permissions django_content_type
auth_permission django_migrations
auth_user django_session
auth_user_groups
sqlite>
2. 查看表结构
sqlite> pragma table_info(app_0814_blogarticles);
0|id|integer|1||1
1|title|varchar(300)|1||0
2|text|text|1||0
3|publish|datetime|1||0
4|author_id|integer|1||0
sqlite>
3. sql查询
sqlite> select * from app_0814_blogarticles; # sql语句查看表数据
1|web add|text add|2021-08-14 07:37:53|1
sqlite>
4. 设置表头是否展示
sqlite> . header on --help
Usage: .headers on|off
sqlite> . header on # 展示表头
sqlite>
5. 设置输出模式
sqlite> .mode column # 设置输出模式 行
sqlite> select * from app_0814_blogarticles;
id title text publish author_id
---------- ---------- ---------- ------------------- ----------
1 web add text add 2021-08-14 07:37:53 1
sqlite>
sqlite> .mode list # 设置输出模式 列表
sqlite> select * from app_0814_blogarticles;
id|title|text|publish|author_id
1|web add|text add|2021-08-14 07:37:53|1
sqlite>
其他输出模式汇总
sqlite> .help mode
.mode MODE ?TABLE? Set output mode
MODE is one of:
ascii Columns/rows delimited by 0x1F and 0x1E
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by "|"
quote Escape answers as for SQL
tabs Tab-separated values
tcl TCL list elements
sqlite>
- ascii输出模式
sqlite> .mode ascii
sqlite> select * from auth_user;
idpasswordlast_loginis_superuserusernamelast_nameemailis_staffis_activedate_joinedfirst_name1pbkdf2_sha256$260000$zLOUzikgIvSbXleHDMIiFv$mffGkz
TLNnDGpalaKCNgiUSi/jfVpihKrOZRvroO5LM=1wlh123@qq.com112021-12-23 09:03:54.925752sqlite>
sqlite>
- csv模式输出
sqlite> .mode csv
sqlite> select * from auth_user;
id,password,last_login,is_superuser,username,last_name,email,is_staff,is_active,date_joined,first_name
1,pbkdf2_sha256$260000$zLOUzikgIvSbXleHDMIiFv$mffGkzTLNnDGpalaKCNgiUSi/jfVpihKrOZRvroO5LM=,,1,wlh,"",123@qq.com,1
,1,"2021-12-23 09:03:54.925752",""
- html模式输出
sqlite> .mode html
sqlite> select * from auth_user;
<TR><TH>id</TH>
<TH>password</TH>
<TH>last_login</TH>
<TH>is_superuser</TH>
<TH>username</TH>
<TH>last_name</TH>
<TH>email</TH>
<TH>is_staff</TH>
<TH>is_active</TH>
<TH>date_joined</TH>
<TH>first_name</TH>
</TR>
<TR><TD>1</TD>
<TD>pbkdf2_sha256$260000$zLOUzikgIvSbXleHDMIiFv$mffGkzTLNnDGpalaKCNgiUSi/jfVpihKrOZRvroO5LM=</TD>
<TD></TD>
<TD>1</TD>
<TD>wlh</TD>
<TD></TD>
<TD>123@qq.com</TD>
<TD>1</TD>
<TD>1</TD>
<TD>2021-12-23 09:03:54.925752</TD>
<TD></TD>
</TR>
sqlite>
- insert 模式输出
sqlite> .mode insert
sqlite> select * from auth_user;
INSERT INTO "table"(id,password,last_login,is_superuser,username,last_name,email,is_staff,is_active,date_joined,first_name) VALUES(1,'pbkdf2_sha256$260000$
zLOUzikgIvSbXleHDMIiFv$mffGkzTLNnDGpalaKCNgiUSi/jfVpihKrOZRvroO5LM=',NULL,1,'wlh','','123@qq.com',1,1,'2021-12-23 09:03:54.925752','');
sqlite>
- line模式输出
sqlite> .mode line
sqlite> select * from auth_user;
id = 1
password = pbkdf2_sha256$260000$zLOUzikgIvSbXleHDMIiFv$mffGkzTLNnDGpalaKCNgiUSi/jfVpihKrOZRvroO5LM=
last_login =
is_superuser = 1
username = wlh
last_name =
email = 123@qq.com
is_staff = 1
is_active = 1
date_joined = 2021-12-23 09:03:54.925752
first_name =
sqlite>
- quote 输出模式
sqlite> .mode quote
sqlite> select * from auth_user;
'id','password','last_login','is_superuser','username','last_name','email','is_staff','is_active','date_joined','first_name'
1,'pbkdf2_sha256$260000$zLOUzikgIvSbXleHDMIiFv$mffGkzTLNnDGpalaKCNgiUSi/jfVpihKrOZRvroO5LM=',NULL,1,'wlh','','123@qq.com',1,1,'2021-12-23 09:03:54.925752',
''
sqlite>
- tabs 输出模式
sqlite> .mode tabs
sqlite> select * from auth_user;
id password last_login is_superuser username last_name email is_staff is_active date_joined first_name
1 pbkdf2_sha256$260000$zLOUzikgIvSbXleHDMIiFv$mffGkzTLNnDGpalaKCNgiUSi/jfVpihKrOZRvroO5LM= 1 wlh 123@qq.com 1
1 2021-12-23 09:03:54.925752 sqlite>
sqlite>
- tcl 输出模式
sqlite> .mode tcl
sqlite> select * from auth_user;
"id" "password" "last_login" "is_superuser" "username" "last_name" "email" "is_staff" "is_active" "date_joined" "first_name"
"1" "pbkdf2_sha256$260000$zLOUzikgIvSbXleHDMIiFv$mffGkzTLNnDGpalaKCNgiUSi/jfVpihKrOZRvroO5LM=" "" "1" "wlh" "" "123@qq.com" "1" "1" "2021-12-23 09:03:54.92
5752" ""
sqlite>
6. 退出
方法1：ctrl+C快捷键
方法2：
sqlite> .exit
C:\testing\pro_0814>
交互模式
交互模式可以对数据库进行增删改查
python manage.py shell # 将django引入当前交互模式
C:\Users>cd C:\testing\py\PycharmProjects\pro_0819
C:\testing\py\PycharmProjects\pro_0819>python manage.py shell
Python 3.9.4 (tags/v3.9.4:1f2e308, Apr 6 2021, 13:40:21) [MSC v.1928 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from django.contrib.auth.models import User # 导入django默认对象User
>>> from app_0819.models import ClassRecord # 导入app_label/model.py中写的类
>>> user = User.objects.get(username="wlh") # 获取User数据模型中username=wlh的对象或数据库中
>>>
注：auth_user表数据
sqlite> select * from auth_user;
id password last_login is_superuser username last_name email is_staff is_active date_joined first_name
---------- --------- -------------------------- ------------ ---------- ---------- ---------- ---------- ---------- -------------------------- ----------
1 pbkdf2_sha25 2021-08-19 07:22:19.075322 1 wlh 123@qq.com 1 1 2021-08-19 05:04:00.950531
2 pbkdf2_sha25 0 user2 0 1 2021-08-19 07:37:55.761859
sqlite>
- 读取对象属性
# user对象已导入
>>> user.username
'wlh'
>>> user.email
'123@qq.com'
>>> user.password
'pbkdf2_sha25'
>>> user.id
1
>>> type(user)
<class 'django.contrib.auth.models.User'>
>>> records = ClassRecord.objects.all() # 读取所有记录
>>> records
<QuerySet [<ClassRecord: ClassRecord object (1)>, <ClassRecord: ClassRecord object (2)>, <ClassRecord: ClassRecord object (3)>, <ClassRecord: ClassRecord object (4)>, <ClassRecord: ClassRecord object (5)>]>
>>> for i in records:
... print(i.class_desc) # 注意：前面需要添加tab，后面回车
... # 再次回车
安全测试报告
4
3
4
4
>>>
上述是多个ClassRecord类的实例组成的序列对象；查询方式在Django中称为QuerySet.
