各位用户为了找寻关于python操作mysql数据库的资料费劲了很多周折。这里教程网为您整理了关于python操作mysql数据库的相关资料，仅供查阅，以下为您介绍关于python操作mysql数据库的详细内容

一、数据库基本操作

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. 连接数据库，插入数据实例

? 1 2 3 4 5 6 7 8 9 10 11 12 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)

查看结果:

? 1 2 3 4 5 6 7 8 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. 获取数据

? 1 2 3 4 5 6 7 8 9 10 11 12 13 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 2 3 (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条数据(注意事务回滚):

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 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表不会增加数据!

? 1 2 3 4 5 6 7 8 9 10 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行代码注释。

? 1 2 3 4 5 6 7 8 9 10 11 12 13 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还是自增了，不会因回滚而取消，但这不影响数据的一致性（底层的原理我不清楚~）

四、批量插入数据

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 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终端显示:

? 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 34 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

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持服务器之家！

原文链接：http://www.cnblogs.com/0zcl/p/6477042.html