django之数据库的配置与操作

数据库配置 创建数据库 配置 settings.py DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'XXX', 'USER': 'XXX', 'PASSWORD': 'xxx', 'HOST': 'localhost', 'PORT': 3306, } } 修改 __init__.py，更改 mysqldb 为 pymysql import pymysql pymysql.install_as_MySQLdb() 操作表 创建表、修改表、删除表 >python manage.py makemigrations >python manage.py migrate 操作行 增加 models.UserGroup.objects.create(title='销售部') models.UserInfo.objects.create(username='root', password='123', usergroup_id=1) 删除 usergroups = models.UserGroup.objects.filter(id=1).delete() 改变 usergroups = models.UserGroup.objects.filter(id=1).update(title='公关部') from django.db.models import F models.UserInfo.objects.all().update(age=F('age')+1) # update userinfo set age = age + 1; 查找 一般查找 userlist = models.UserGroup.objects.all()#.first() for row in userlist: print(row.id, row.title) userlist = models.UserGroup.objects.filter(id=1, title='aaa') # and userlist = models.UserGroup.objects.exclude(id=1) # != userlist = models.UserGroup.objects.filter(id__gt=1) # > userlist = models.UserGroup.objects.filter(id__lt=1) # < 进阶查找 # 连表查找 # usertype是userinfo的fk字段 obj = models.UserInfo.objects.all().first() print(obj.name, obj.age, obj.usertype_id, obj.usertype.title) # (正向)连表查找 obj = models.UserType.objects.all().first() print(obj.id, obj.title, obj.userinfo_set.all()) # (反向) print(obj.id, obj.title, obj.userinfo_set.filter(name='xx')) # 过滤 # 获取的类型 models.UserInfo.objects.all() # 返回UserInfo类型的QuerySet数组 models.UserInfo.objects.all().values('id', 'name') # 返回字典类型的QuerySet数组 models.UserInfo.objects.all().values_list('id', 'name') # 返回元组类型的QuerySet数组 # 排序 Order By models.UserInfo.objects.all().order_by('id') # 升序 models.UserInfo.objects.all().order_by('-id') # 降序 models.UserInfo.objects.all().order_by('id', 'name') # 多列排序 # 分组 Group By ... Having ... from django.db.models import Count, Sum, Max, Min, Avg v = models.UserInfo.objects.filter(id__gt=2).values('usertype_id').annotate(xxxx=Count('id')).filter(xxxx__gt=2) # 注意前后filter表示的含义不同 print(v.query) # 查看生成的SQL语句 # select usertype_id, count(id) as xxxx from userinfo where id > 2 group by usertype_id having count(id) > 2; # in; between ... and ...; gt lt gte lte ...; models.UserInfo.objects.filter(id__in=[1,2,3]) # in models.UserInfo.objects.filter(id__range=[1,3]) # between...and... models.UserInfo.objects.filter(id__gt=1) # > models.UserInfo.objects.filter(id__lt=1) # < models.UserInfo.objects.filter(id__gte=1) # >= models.UserInfo.objects.filter(id__lte=1) # <= # not ... models.UserInfo.objects.exclude(id__in=[1,2,3]) # in models.UserInfo.objects.exclude(id__range=[1,3]) # between...and... models.UserInfo.objects.exclude(id__gt=1) # > models.UserInfo.objects.exclude(id__lt=1) # < models.UserInfo.objects.exclude(id__gte=1) # >= models.UserInfo.objects.exclude(id__lte=1) # <= # like % models.UserInfo.objects.filter(name__startswith='a') # like '%a' models.UserInfo.objects.filter(name__endswith='a') # like 'a%' models.UserInfo.objects.filter(name__contains='a') # like '%a%' # not like % models.UserInfo.objects.exclude(name__startswith='a') # not like '%a' models.UserInfo.objects.exclude(name__endswith='a') # not like 'a%' models.UserInfo.objects.exclude(name__contains='a') # not like '%a%' # and condition = {'id': 1, 'name': 'root'} models.UserInfo.objects.filter(**condition) # 不推荐 # and or混合，对象方式 (不推荐) from django.db.models import Q models.UserInfo.objects.filter(Q(id=1) | Q(id=2)) # or models.UserInfo.objects.filter(Q(id=1) & Q(id=2)) # and # and or混合，方法方式 (推荐) q1 = Q() q1.connector = 'OR' q1.children.append(('id', 1)) q1.children.append(('id', 10)) q1.children.append(('id', 9)) q2 = Q() q2.connector = 'OR' q2.children.append(('c1', 1)) q2.children.append(('c1', 10)) q2.children.append(('c1', 9)) con = Q() con.add(q1, 'AND') con.add(q2, 'AND') models.UserInfo.objects.filter(con) # select id, name, (select ...) as n from xb; models.UserInfo.objects.all().extra( select={ 'n': 'select ... where id > %s and id < %s', 'm': 'select ... where id = %s and id = %s', }, select_params=[1, 3, 4, 5]) # where (select ... ) models.UserInfo.objects.all().extra( where=["id=%s or id=2", "name=%s"], # and params=[1, 'alex']) # select * from app01_userinfo, app01_usertype; models.UserInfo.objects.all().extra( tables=['app01_usertype'], where=['app01_usertype.id = app01_userinfo.user'] ) 分页 obj = models.UserInfo.objects.all()[0:10]