后端开发|Python教程
python，数据库，MYSQL，操作数据库
后端开发-Python教程
A、数据库基本操作
门户网站源码带手机版,vscode自动写注释,linux编写ubuntu,tomcat不报错停止,爬虫公主,php正则 表格,深圳seo培训推广工具,免费网站源码怎么用,wap h5页面模板下载lzw
1. 想允许在数据库写中文，可在创建数据库时用下面命令
易语言取随机数源码,Vscode保存不了怎么办,ubuntu保存只读,tomcat无法自动启动,爬虫ajax 视频,php字符串去重复,福建省seo优化排名推广lzw
create database zcl charset utf8;
2. 查看students表结构
协同办公管理系统 源码,vscode如何设置空格,ubuntu字符开机,怎么 添加tomcat,sqlite可以远程么,前端有什么框架可以导入3d,家里卧室黑色的爬虫是什么,php获取指定内容,枣庄seo优化企业,css美化网站,网页二维码 代码下载,dw静态网页模板下载lzw
desc students;
3. 查看创建students表结构的语句
show create table students;
4. 删除数据库
drop database zcl;
5. 创建一个新的字段
alter table students add column nal char(64);
PS: 本人是很讨厌上面这种“简单解释+代码”的博客。其实我当时在mysql终端写了很多的实例，不过因为当时电脑运行一个看视频的软件，导致我无法Ctrl+C/V。现在懒了哈哈~~
B、python连接数据库
python3不再支持mysqldb。其替代模块是PyMySQL。本文的例子是在python3.4环境。
1. 安装pymysql模块
pip3 install pymysql
2. 连接数据库，插入数据实例
import pymysql#生成实例，连接数据库zclconn = pymysql.connect(host=127.0.0.1, user= oot, passwd= oot, db=zcl)#生成游标，当前实例所处状态cur = conn.cursor()#插入数据reCount = cur.execute(insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s),(Jack,man,25,1351234,"CN"))reCount = cur.execute(insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s),(Mary,female,18,1341234,"USA"))mit() #实例提交命令cur.close()conn.close()print(reCount)
查看结果:
mysql> select* from students;+----+------+-----+-----+-------------+------+| id | name | sex | age | tel | nal |+----+------+-----+-----+-------------+------+| 1 | zcl | man | 22 | 15622341234 | NULL || 2 | alex | man | 30 | 15622341235 | NULL |+----+------+-----+-----+-------------+------+2 rows in set
3. 获取数据
import pymysqlconn = pymysql.connect(host=127.0.0.1, user= oot, passwd= oot, db=zcl)cur = conn.cursor()reCount = cur.execute(select* from students)res = cur.fetchone() #获取一条数据res2 = cur.fetchmany(3) #获取3条数据res3 = cur.fetchall()#获取所有(元组格式)print(res)print(res2)print(res3)mit()cur.close()conn.close()
输出:
(1, zcl, man, 22, 15622341234, None)((2, alex, man, 30, 15622341235, None), (5, Jack, man, 25, 1351234, CN), (6, Mary, female, 18, 1341234, USA))()
C、事务回滚
事务回滚是在数据写到数据库前执行的，因此事务回滚conn.rollback()要在实例提交命令mit()之前。只要数据未提交就可以回滚，但回滚后ID却是自增的。请看下面的例子:
插入3条数据(注意事务回滚):
import pymysql#连接数据库zclconn=pymysql.connect(host=127.0.0.1, user= oot, passwd= oot, db=zcl)#生成游标，当前实例所处状态cur=conn.cursor()#插入数据reCount=cur.execute(insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s), (Jack, man, 25, 1351234, "CN"))reCount=cur.execute(insert into students(name, sex, age, tel, nal) values(%s,%s,%s,%s,%s), (Jack2, man, 25, 1351234, "CN"))reCount=cur.execute(insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s), (Mary, female, 18, 1341234, "USA"))conn.rollback()#事务回滚mit() #实例提交命令cur.close()conn.close()print(reCount)
未执行命令前与执行命令后(包含回滚操作)(注意ID号): 未执行上面代码与执行上面代码的结果是一样的!!因为事务已经回滚，故students表不会增加数据!
mysql> select* from students;+----+------+--------+-----+-------------+------+| id | name | sex | age | tel | nal |+----+------+--------+-----+-------------+------+| 1 | zcl | man | 22 | 15622341234 | NULL || 2 | alex | man | 30 | 15622341235 | NULL || 5 | Jack | man | 25 | 1351234| CN || 6 | Mary | female | 18 | 1341234| USA |+----+------+--------+-----+-------------+------+4 rows in set
执行命令后(不包含回滚操作):只需将上面第11行代码注释。
mysql> select* from students;+----+-------+--------+-----+-------------+------+| id | name | sex | age | tel | nal |+----+-------+--------+-----+-------------+------+| 1 | zcl | man | 22 | 15622341234 | NULL || 2 | alex | man | 30 | 15622341235 | NULL || 5 | Jack | man | 25 | 1351234| CN || 6 | Mary | female | 18 | 1341234| USA || 10 | Jack | man | 25 | 1351234| CN || 11 | Jack2 | man | 25 | 1351234| CN || 12 | Mary | female | 18 | 1341234| USA |+----+-------+--------+-----+-------------+------+7 rows in set
总结：虽然事务回滚了，但ID还是自增了，不会因回滚而取消，但这不影响数据的一致性（底层的原理我不清楚~）
D、批量插入数据
import pymysql#连接数据库zclconn = pymysql.connect(host=127.0.0.1, user= oot, passwd= oot, db=zcl)#生成游标，当前实例所处状态cur = conn.cursor()li = [ ("cjy","man",18,1562234,"USA"), ("cjy2","man",18,1562235,"USA"), ("cjy3","man",18,1562235,"USA"), ("cjy4","man",18,1562235,"USA"), ("cjy5","man",18,1562235,"USA"),]#插入数据reCount = cur.executemany(insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s), li)#conn.rollback() #事务回滚mit() #实例提交命令cur.close()conn.close()print(reCount)
pycharm下输出: 5
mysql终端显示:
mysql> select* from students;#插入数据前+----+-------+--------+-----+-------------+------+| id | name | sex | age | tel | nal |+----+-------+--------+-----+-------------+------+| 1 | zcl | man | 22 | 15622341234 | NULL || 2 | alex | man | 30 | 15622341235 | NULL || 5 | Jack | man | 25 | 1351234| CN || 6 | Mary | female | 18 | 1341234| USA || 10 | Jack | man | 25 | 1351234| CN || 11 | Jack2 | man | 25 | 1351234| CN || 12 | Mary | female | 18 | 1341234| USA |+----+-------+--------+-----+-------------+------+7 rows in setmysql> mysql> select* from students;#插入数据后+----+-------+--------+-----+-------------+------+| id | name | sex | age | tel | nal |+----+-------+--------+-----+-------------+------+| 1 | zcl | man | 22 | 15622341234 | NULL || 2 | alex | man | 30 | 15622341235 | NULL || 5 | Jack | man | 25 | 1351234| CN || 6 | Mary | female | 18 | 1341234| USA || 10 | Jack | man | 25 | 1351234| CN || 11 | Jack2 | man | 25 | 1351234| CN || 12 | Mary | female | 18 | 1341234| USA || 13 | cjy | man | 18 | 1562234| USA || 14 | cjy2 | man | 18 | 1562235| USA || 15 | cjy3 | man | 18 | 1562235| USA || 16 | cjy4 | man | 18 | 1562235| USA || 17 | cjy5 | man | 18 | 1562235| USA |+----+-------+--------+-----+-------------+------+12 rows in set
学完的东西要及时总结，有些东西忘记了阿~_~
