本篇文章主要介绍了 python 操作 mysql 数据库的相关知识,具有很好的参考价值。下面跟着小编一起来看下吧
Python 是一种面向对象、解释型计算机程序设计语言,由 Guido van Rossum 于 1989 年底发明,第一个公开发行版发行于 1991 年。Python 语法简洁而清晰,具有丰富和强大的类库。它常被昵称为胶水语言,它能够把用其他语言制作的各种模块(尤其是 C/C++)很轻松地联结在一起。
一、数据库基本操作
1. 想允许在数据库写中文,可在创建数据库时用下面命令
- create database zcl charset utf8;
2. 查看 students 表结构
- 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。现在懒了哈哈~~
二、python 连接数据库
python3 不再支持 mysqldb。其替代模块是 PyMySQL。本文的例子是在 python3.4 环境。
1. 安装 pymysql 模块
- pip3 install pymysql
2. 连接数据库,插入数据实例
- import pymysql
- #生成实例,连接数据库zcl
- conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', 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"))
- conn.commit() #实例提交命令
- 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 |
- +----+------+-----+-----+-------------+------+
- rows in set
3. 获取数据
- import pymysql
- conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', 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)
- conn.commit()
- 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'))
- ()
三、事务回滚
事务回滚是在数据写到数据库前执行的,因此事务回滚 conn.rollback() 要在实例提交命令 conn.commit() 之前。只要数据未提交就可以回滚,但回滚后 ID 却是自增的。请看下面的例子:
插入 3 条数据 (注意事务回滚):
- import pymysql
- #连接数据库zcl
- conn=pymysql.connect(host='127.0.0.1', user='root', passwd='root', 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() #事务回滚
- conn.commit() #实例提交命令
- 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 |
- +----+------+--------+-----+-------------+------+
- 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 |
- +----+-------+--------+-----+-------------+------+
- rows in set
总结:虽然事务回滚了,但 ID 还是自增了,不会因回滚而取消,但这不影响数据的一致性(底层的原理我不清楚~)
四、批量插入数据
- import pymysql
- #连接数据库zcl
- conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', 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() #事务回滚
- conn.commit() #实例提交命令
- 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 |
- +----+-------+--------+-----+-------------+------+
- rows in set
- 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 |
- | 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 |
- +----+-------+--------+-----+-------------+------+
- rows in set
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持 PHPERZ!
来源: http://www.phperz.com/article/17/0321/326066.html