一、创建单表
models.py
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from
__future__
import
unicode_literals
from
django.db
import
models
class
UserInfo(models.Model):
USER_TYPE_LIST
=
(
(
1
,
"F"
),
(
2
,
"M"
),
)
name
=
models.CharField(max_length
=
32
,primary_key
=
True
)
user_type
=
models.IntegerField(choices
=
USER_TYPE_LIST,default
=
1
)
ctime
=
models.DateTimeField(auto_now
=
True
)
uptime
=
models.DateTimeField(auto_now_add
=
True
)
email
=
models.EmailField(max_length
=
32
,null
=
True
)
email_default
=
models.EmailField(max_length
=
32
,default
=
"admin@163.com"
)
ip
=
models.GenericIPAddressField(protocol
=
'both'
,null
=
True
,blank
=
True
)
img
=
models.ImageField(null
=
True
,blank
=
True
,upload_to
=
"upload"
)
def
__unicode__(
self
):
return
self
.name
|
创建数据库单表效果如下:
创建用户:
再次查看表数据:
二、创建表之一对多，运用外键models.ForeignKey("xxx")
models.py
Pepole(models.Model):
name = models.CharField(=)
country = models.CharField(=)
Property(models.Model):
size = models.CharField(=)
weight = models.CharField(=)
length = models.CharField(=)
两张表Pepole和Property通过外键models.ForeignKey(Pepole)产生关联
默认通过pepole表的id字段产生关联，property表生成color_id来存放pepole表的id，具体如下:
如果我们在pepole表内生成数据，则会出现如下id:
同时在property表中可以看到关联的项可选数字为1、2、3、4
那一般什么时候用外键呢？比如我们要创建一个业务线，同时也要创建一个主机，但是主机隶属于某个业务线中的一部分，所以这个时候我们可以采取外键的方式创建一对多表，代码如下:
class Business(models.Model):
name = models.CharField(max_length=16)
class Host(models.Model):
hostname = models.CharField(max_length=32)
或者
class Business(models.Model):
name = models.CharField(max_length=16)
class Host(models.Model):
hostname = models.CharField(max_length=32)
也可以通过指定的字段进行绑定
class Business(models.Model):
nid = models.AutoField(primary_key=True)
name = models.CharField(max_length=16,unique=True)
class Host(models.Model):
hostname = models.CharField(max_length=32)
business = models.ForeignKey('Business',to_field='nid')
表Business
表Host
表Host关联字段
三、创建表之多对多，运用models.ManyToManyField('xxxx')
UserGroup(models.Model):
group_name = models.CharField(=)
User(models.Model):
name = models.CharField(=)
email= models.EmailField(=)
user_to_group = models.ManyToManyField()
一个用户可以属于多个用户组，一个用户组可以包含多个用户，建立起多对多的关联关系
UserGroup表:
User表
关联关系:
通过两张表的user_id和usergroup_id来创建关联表user_user_to_group
即通过两张表的代码，系统自动帮忙创建第三张表（关联表user_user_to_group）
四、数据库的常用操作
# 增
①
# models.Tb1.objects.create(c1='xx', c2='oo') 增加一条数据，可以接受字典类型数据 **kwargs
②
# obj = models.Tb1(c1='xx', c2='oo')
# obj.save()
③
dic = {'c1':'xx','c2':'oo'}
models.Tb1.objects.create(**dic)
# 查
#
# models.Tb1.objects.get(id=123) # 获取单条数据，不存在则报错（不建议）
# models.Tb1.objects.all() # 获取全部
# models.Tb1.objects.all().first()# 取第一条数据
# models.Tb1.objects.filter(name='seven') # 获取指定条件的数据
# 删
#
# models.Tb1.objects.filter(name='seven').delete() # 删除指定条件的数据
# 改
①
# models.Tb1.objects.filter(name='seven').update(gender='0') # 将指定条件的数据更新，均支持 **kwargs,支持字典类型数据
②
# obj = models.Tb1.objects.get(id=1)
# obj.c1 = '111'
# obj.save() # 修改单条数据
查询例子:
models.py
SimpleModel(models.Model):
username = models.CharField(=)
password = models.CharField(=)
查询操作home.py
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
def
index(request):
dic
=
{
'username'
:
'pythoner'
,
'password'
:
'123!@#'
}
models.SimpleModel.objects.create(
*
*
dic)
ret
=
models.SimpleModel.objects.
all
()
#获取所有的数据
print
ret
#是一个对象的列表[<SimpleModel: SimpleModel object>],
print
type
(ret)
#输出结果为django的一个QuerySet类型，<class 'django.db.models.query.QuerySet'>
print
ret.query
#输出一个select查询语句
ret
=
models.SimpleModel.objects.
all
().values(
'username'
)
#只获取某一个列数据，这里获取username列
print
ret,
type
(ret)
#这里获取的每一项数据类型是一个字典
#[{'username':'u'alex''}] <class 'django.db.models.query.ValueQuerySet'>
ret
=
models.SimpleModel.objects.
all
().values_list(
'username'
)
#这里每一项数据类型就是一个元组
print
ret,
type
(ret)
#[(u'alex',)]<class 'django.db.models.query.ValueList'>
obj
=
HomeForm.ImportForm()
return
render(request,
'home/index.html'
,{
'obj'
:obj})
|
get data from file
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
#!/usr/bin/env python
# -*- coding:utf-8 -*-
__author__
=
'ryan'
from
django
import
forms
class
ImportForm(forms.Form):
HOST_TYPE_LIST
=
(
(
1
,
'物理机'
),
(
2
,
'虚拟机'
)
)
host_type
=
forms.IntegerField(
widget
=
forms.Select(choices
=
HOST_TYPE_LIST)
)
hostname
=
forms.CharField(widget
=
forms.PasswordInput())
import
json
dic
=
((
1
,
"abc"
),(
2
,
"abcd"
),(
3
,
"abcdef"
))
f
=
open
(
'db_admin'
,
'w'
)
f.write(json.dumps(dic))
f.close()
fr
=
open
(
"db_admin"
)
data
=
fr.read()
data_tuple
=
json.loads(data)
#从文件中获取数据，后期将该部分内容改成sql语句查询结果就成了从数据库中获取数据
admin
=
forms.IntegerField(
widget
=
forms.Select(choices
=
data_tuple)
)
def
__init__(
self
,
*
args,
*
*
kwargs):
super
(ImportForm,
self
).__init__(
*
args,
*
*
kwargs)
#执行父类的构造方法
import
json
fr
=
open
(
"db_admin"
)
data
=
fr.read()
data_tuple
=
json.loads(data)
self
.fields[
'admin'
].widget.choice
=
data_tuple
|
get data from database
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
#!/usr/bin/env python
# -*- coding:utf-8 -*-
__author__
=
'ryan'
from
django
import
forms
from
app01
import
models
class
ImportForm(forms.Form):
HOST_TYPE_LIST
=
(
(
1
,
'物理机'
),
(
2
,
'虚拟机'
)
)
host_type
=
forms.IntegerField(
widget
=
forms.Select(choices
=
HOST_TYPE_LIST)
)
hostname
=
forms.CharField(widget
=
forms.PasswordInput())
import
json
dic
=
((
1
,
"abc"
),(
2
,
"abcd"
),(
3
,
"abcdef"
))
f
=
open
(
'db_admin'
,
'w'
)
f.write(json.dumps(dic))
f.close()
fr
=
open
(
"db_admin"
)
data
=
fr.read()
data_tuple
=
json.loads(data)
#从文件中获取数据，后期将该部分内容改成sql语句查询结果就成了从数据库中获取数据
admin
=
forms.IntegerField(
widget
=
forms.Select(choices
=
data_tuple)
)
def
__init__(
self
,
*
args,
*
*
kwargs):
super
(ImportForm,
self
).__init__(
*
args,
*
*
kwargs)
self
.fields[
'admin'
].widget.choice
=
models.SimpleModel.objects.
all
().values_list(
'id'
,
'username'
)
|
五、数据库的进阶操作
# 获取个数
# models.Tb1.objects.filter(name='seven').count()
# 大于，小于
# models.Tb1.objects.filter(id__gt=1) # 获取id大于1的值
# models.Tb1.objects.filter(id__lt=10) # 获取id小于10的值
# models.Tb1.objects.filter(id__lt=10, id__gt=1) # 获取id大于1 且 小于10的值
# in
# models.Tb1.objects.filter(id__in=[11, 22, 33]) # 获取id等于11、22、33的数据
# models.Tb1.objects.exclude(id__in=[11, 22, 33]) # not in
# contains
# models.Tb1.objects.filter(name__contains="ven")
# models.Tb1.objects.filter(name__icontains="ven") # icontains大小写不敏感
# models.Tb1.objects.exclude(name__icontains="ven")
# range
# models.Tb1.objects.filter(id__range=[1, 2]) # 范围bettwen and
# 其他类似
# startswith，istartswith, endswith, iendswith,
# order by
# models.Tb1.objects.filter(name='seven').order_by('id') # asc
# models.Tb1.objects.filter(name='seven').order_by('-id') # desc
# limit 、offset
# models.Tb1.objects.all()[10:20]
# group by
from django.db.models import Count, Min, Max, Sum
# models.Tb1.objects.filter(c1=1).values('id').annotate(c=Count('num'))
# SELECT "app01_tb1"."id", COUNT("app01_tb1"."num") AS "c" FROM "app01_tb1" WHERE "app01_tb1"."c1" = 1 GROUP BY "app01_tb1"."id"
