一、PyMySQL介绍
PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库,Python2中则使用mysqldb。
Django中也可以使用PyMySQL连接MySQL数据库。
pip install pymysql #安装pymysql
二、连接数据库
注意事项
在进行本文以下内容之前需要注意:
- 你有一个MySQL数据库,并且已经启动。
- 你有可以连接该数据库的用户名和密码
- 你有一个有权限操作的database
基本使用
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() # 定义要执行的SQL语句 sql = """ CREATE TABLE USER1 ( id INT auto_increment PRIMARY KEY , name CHAR(10) NOT NULL UNIQUE, age TINYINT NOT NULL )ENGINE=innodb DEFAULT CHARSET=utf8; """ # 执行SQL语句 cursor.execute(sql) # 关闭光标对象 cursor.close() # 关闭数据库连接 conn.close()
返回字典格式数据
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句并且将结果作为字典返回的游标 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)#重点在加入括号的这行参数 # 定义要执行的SQL语句 sql = """ CREATE TABLE USER1 ( id INT auto_increment PRIMARY KEY , name CHAR(10) NOT NULL UNIQUE, age TINYINT NOT NULL )ENGINE=innodb DEFAULT CHARSET=utf8; """ # 执行SQL语句 cursor.execute(sql) # 关闭光标对象 cursor.close() # 关闭数据库连接 conn.close()
注意:
charset=“utf8”,编码不要写成"utf-8"
三、增删改查操作
增加
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);" username = "Alex" age = 18 # 执行SQL语句 cursor.execute(sql, [username, age]) # 提交事务 conn.commit() cursor.close() conn.close()
插入数据失败回滚
在执行增删改操作时,如果不想提交前面的操作,可以使用 rollback() 回滚取消操作。
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);" username = "Alex" age = 18 try: # 执行SQL语句 cursor.execute(sql, [username, age]) # 提交事务 conn.commit() except Exception as e: # 有异常,回滚事务 conn.rollback() cursor.close() conn.close()
获取插入数据的ID
(关联操作时会用到)重点!
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);" username = "Alex" age = 18 try: # 执行SQL语句 cursor.execute(sql, [username, age]) # 提交事务 conn.commit() # 提交之后,获取刚插入的数据的ID 重点在此处lastrowid last_id = cursor.lastrowid except Exception as e: # 有异常,回滚事务 conn.rollback() cursor.close() conn.close()
批量执行
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);" data = [("Alex", 18), ("Egon", 20), ("Yuan", 21)] try: # 批量执行多条插入SQL语句 executemany cursor.executemany(sql, data) # 提交事务 conn.commit() except Exception as e: # 有异常,回滚事务 conn.rollback() cursor.close() conn.close()
删除
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() sql = "DELETE FROM USER1 WHERE id=%s;" try: cursor.execute(sql, [4]) # 提交事务 conn.commit() except Exception as e: # 有异常,回滚事务 conn.rollback() cursor.close() conn.close()
修改
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() # 修改数据的SQL语句 sql = "UPDATE USER1 SET age=%s WHERE name=%s;" username = "Alex" age = 80 try: # 执行SQL语句 cursor.execute(sql, [age, username]) # 提交事务 conn.commit() except Exception as e: # 有异常,回滚事务 conn.rollback() cursor.close() conn.close()
查询
查询单条数据
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() # 查询数据的SQL语句 sql = "SELECT id,name,age from USER1 WHERE id=1;" # 执行SQL语句 cursor.execute(sql) # 获取单条查询数据 fetchone ret = cursor.fetchone() cursor.close() conn.close() # 打印下查询结果 print(ret)
查询多条数据
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() # 查询数据的SQL语句 sql = "SELECT id,name,age from USER1;" # 执行SQL语句 cursor.execute(sql) # 获取多条查询数据fetchall ret = cursor.fetchall() cursor.close() conn.close() # 打印下查询结果 print(ret)
进阶用法
# 可以获取指定数量的数据 cursor.fetchmany(3) # 光标按绝对位置移动1 cursor.scroll(1, mode="absolute") # 光标按照相对位置(当前位置)移动1 cursor.scroll(1, mode="relative")
四、ORM(对象关系映射)
orm:
类 ----> 表
对象实例 ----> 表的记录
1、对django模型的配置:
- settings的配置: INSTALLED_APPS = [ 'django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'app01.apps.App01Config', 注意查看有无新建的app ] - 更改一下数据库的配置: DATABASES = { # 'default': { # 'ENGINE': 'django.db.backends.sqlite3', # 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'), # } 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 数据库名称, 'USER': 'root', 'PASSWORD': '', 'CHARSET': 'utf8', 'HOST': '127.0.0.1', 'PORT':3306 } } - init.py: import pymysql pymysql.install_as_MySQLdb()
2、运行如下命令(创建表时):
python3 manage.py makemigrations ====> 生成数据库文件
python3 manage.py migrate ====> 执行生成的数据库文件
3、 Django中 app 中 models.py 如下示例(创建表)
from django.db import models # Create your models here. class classes(models.Model): id = models.AutoField(primary_key=True) classname = models.CharField(max_length=32, null=False, default='') class students(models.Model): #不写id orm 会自动生成一个 id字段 student_name = models.CharField(max_length=32, null=False, default='') classes = models.ForeignKey('classes', on_delete=None) class test(models.Model): age = models.IntegerField() class teacher(models.Model): tname = models.CharField(max_length=32, null=False, default='') class teacher2class(models.Model): teacher = models.ForeignKey('teacher', on_delete=None) classes = models.ForeignKey('classes', on_delete=None) #多对多创建另外一种方式 classes = models.ManyToManyField('classes') #不创建关联表 在teacher表加这个字段,Django会自动生成第三张表:PS:建议使用上面的方式,语义直白
4、 Django 中 app 中 views 如下示例
1、添加数据
# models.表对象.objects.方法 models.classes.objects.create(classname='高二1班') li = [ models.classes(classname='高二5班'), models.classes(classname='高二3班'), models.classes(classname='高二6班'), ] models.classes.objects.bulk_create(li)
2、查询数据
from django.shortcuts import render, HttpResponse from app01 import models # Create your views here. # 获取所有的数据=====> select * from classses; res = models.classes.objects.all() #<QuerySet [ <classes: classes object (1)>, <classes: classes object (2)>, <classes: classes object (3)>, <classes: classes object (4)> ]> for item in res: print(item.id , item.classname) #获取classname ====> select classname from classses; #<QuerySet [ {'classname': '高二1班'}, {'classname': '高二2班'}, {'classname': '高二3班'}, {'classname': '高二4班'} ]> res = models.classes.objects.values('classname').all() print(res)#列表字典格式 for item in res: print(item['classname']) #value_list: <QuerySet [('高二1班',), ('高二2班',), ('高二3班',), ('高二4班',)]> res = models.classes.objects.values_list('classname').all() print(res) 2.1 取第一条 ---> select * from classes limit 1; res = models.classes.objects.first() print(res.classname) 2.2 根据条件查询数据 res = models.classes.objects.filter(id=4)
3. 删除 ---> delete from classes where id=1;
models.classes.objects.filter(id=4).delete()
4. 更新
models.classes.objects.filter(id=3).update(classname='高二10班')
5. 一对多添加
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
models.students.objects.create(student_name='王刚蛋', classes_id=1) models.students.objects.create(student_name='王铁锤', classes_id=2) models.students.objects.create(student_name='二狗', classes_id=3) models.students.objects.create(student_name='刘dd', classes_id=1) models.students.objects.create(student_name='狗剩子', classes_id=2) models.students.objects.create(student_name='铁蛋', classes_id=3) models.students.objects.create(student_name='xxxx', classes_id=3)
6.正向查询
# 外键(FK) classes_id 代指 ---> classes表里面的一行记录 # sql :select * from classes left join students on claess.id =stdents.classid; # res.query:SELECT `app01_students`.`id`, `app01_students`.`student_name`, `app01_students`.`classes_id` FROM `app01_students` res = models.students.objects.all() for item in res: print(item.id, item.student_name, item.classes.classname)#此处classes为建表时的外键名称
7. 反向查询
#高二1班有多少学生? obj.表名_set.all() sql : select * from classes where classname='高二1班' 7.1先查询班级表 班级名为高二1班的 res = models.classes.objects.filter(classname='高二1班').first() 7.2再查询学生表中所有符合的 info = res.students_set.all() # select * from students where classes_id = res['id'] print(info) for item in info: print(item.student_name)
8. __ 神奇的双下划线
res = models.students.objects.filter(classes__classname='高二1班').all() #相当于执行下面的sql语句,连表查询妙用 print(res.query) #SELECT `app01_students`.`id`, `app01_students`.`student_name`, `app01_students`.`classes_id` FROM `app01_students` INNER JOIN `app01_classes` ON (`app01_students`.`classes_id` = `app01_classes`.`id`) WHERE `app01_classes`.`classname` = 高二1班 for item in res: print(item.student_name)
9. 获取个数 ---> count() ==> select count(*) from students
cnt = models.classes.objects.count() print(cnt)
10. 大于,小于
greater then 大于 res = models.classes.objects.filter(id__gt=1) #大于 res = models.classes.objects.filter(id__gte=1)#大于等于 res = models.classes.objects.filter(id__lt=1) #小于 res = models.classes.objects.filter(id__lte=1)#小于等于 sql :select * from classes where id < 10 and id > 1 res = models.classes.objects.filter(id__lt=10, id__gt=1) # 获取id小于10 且 大于1的值
11. in 判断在不在
exclude : 除去xxx ,还剩xxxx res = models.classes.objects.filter(id__in=[11, 22, 33]) # 获取id等于11、22、33的数据 res = models.classes.objects.exclude(id__in=[11, 22, 33]) # not in
12. isnull
res = models.classes.objects.filter(id__isnull=True)
13. contains 模糊查询
select * from stundets where student_name like '%二%'; contains 相当于 like startswith == > like 'ven%',istartswith, endswith ===> like '%ven', iendswith, res = models.classes.objects.filter(classname__contains="ven") res = models.classes.objects.filter(classname__icontains="ven") # icontains大小写不敏感(不区分大小写) models.classes.objects.exclude(classname__icontains="ven") #判断字符中没有ven 的数据
14. range 范围
res = models.classes.objects.filter(id__range=[1, 2]) # 范围bettwen and
15. order by 排序
models.classes.objects.filter(name='seven').order_by('id') # asc 升序 models.Tb1.objects.filter(name='seven').order_by('-id') # desc 降序
16. group by 分组查询
from django.db.models import Count, Min, Max, Sum res = models.students.objects.values('classes_id').annotate(c=Count('id')) #根据classes_id分组 SELECT `app01_students`.`classes_id`, COUNT(`app01_students`.`id`) AS `c` FROM `app01_students` GROUP BY `app01_students`.`classes_id` ORDER BY NULL
17. limit、offset 切片
res = models.students.objects.all()[10:20] #从第10行开始取:取20条 print(res.query)
18. F F() 的实例可以在查询中引用字段,来比较同一个 model 实例中两个不同字段的值。
from django.db.models import F #对id=1的人 在age字段上 数据+1 models.students.objects.filter(id=1).update(age=F('age') + 1) #对两个字段的值做比较和操作
19. Q 执行更复杂的查询(或例如OR语句),你可以使用Q对象
from django.db.models import F, Q Q(nid=8) | Q(nid__gt=10) res = models.students.objects.filter(Q(id=8) | Q(id__gt=10)) #取id=8 或者 id>10 的数据 print(res.query) # SELECT `app01_students`.`id`, `app01_students`.`student_name`, `app01_students`.`classes_id` FROM `app01_students` WHERE (`app01_students`.`id` = 8 OR `app01_students`.`id` > 10) Q(Q(nid=8) | Q(nid__gt=10)) & Q(caption='root') #取id=8 或者 id>10 并且 名字为root 的 数据 res = models.students.objects.filter(Q( Q(id=8) | Q(id__gt=10)) & Q(student_name='root')) print(res.query)
20. 执行原生SQL Django的orm无法满足时
from django.db import connection, connections #一个连接默认数据库,一个可以选择数据库 cursor = connection.cursor() #connection会去settings找数据库配置 cursor = connections['xxx'].cursor() #这是连接另一个主机的方式 cursor.execute("""SELECT * from auth_user where id = %s""", [1]) res = cursor.fetchone() . .
21. 多对多查询
1. 反向查询: res = models.teacher.objects.filter(tname='eagon').first() 1、obj.表类名小写_set.all() info = res.teacher2class_set.all() #查询老师名为eagon 所代课的 班级名 2、在models中的表类的ForeignKey('teacher', on_delete=None,related_name = 'classes') info = res.classes.all() for item in info: item.classes.classname 2. 神器的双下滑线查询 res = models.teacher2class.objects.filter(teacher__tname='eagon') for item in res: item.classes.classname
最后补充 Djangoadmin 的配置 (admin是Django自带的一个后台管理系统)
-
admin.py中,注册表
-
admin.site.register(models.表名)
-
-
urls 中
-
url('admin', admin.site.urls)
-
-
命令行创建
-
python3 manage.py createsupperuser
-