一、数据库基本操作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=&＃39;127.0.0.1&＃39;, user=&＃39;root&＃39;, passwd=&＃39;root&＃39;, db=&＃39;zcl&＃39;)
#生成游标，当前实例所处状态
cur = conn.cursor()
#插入数据
reCount = cur.execute(&＃39;insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)&＃39;,(&＃39;Jack&＃39;,&＃39;man&＃39;,25,1351234,"CN"))
reCount = cur.execute(&＃39;insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)&＃39;,(&＃39;Mary&＃39;,&＃39;female&＃39;,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 |
+----+------+-----+-----+-------------+------+
2 rows in set
3. 获取数据
import pymysql
cOnn= pymysql.connect(host=&＃39;127.0.0.1&＃39;, user=&＃39;root&＃39;, passwd=&＃39;root&＃39;, db=&＃39;zcl&＃39;)
cur = conn.cursor()
reCount = cur.execute(&＃39;select* from students&＃39;)
res = cur.fetchone() #获取一条数据
res2 = cur.fetchmany(3) #获取3条数据
res3 = cur.fetchall() #获取所有(元组格式)
print(res)
print(res2)
print(res3)
conn.commit()
cur.close()
conn.close()
输出:
(1, &＃39;zcl&＃39;, &＃39;man&＃39;, 22, &＃39;15622341234&＃39;, None)
((2, &＃39;alex&＃39;, &＃39;man&＃39;, 30, &＃39;15622341235&＃39;, None), (5, &＃39;Jack&＃39;, &＃39;man&＃39;, 25, &＃39;1351234&＃39;, &＃39;CN&＃39;), (6, &＃39;Mary&＃39;, &＃39;female&＃39;, 18, &＃39;1341234&＃39;, &＃39;USA&＃39;))
()
三、事务回滚
事务回滚是在数据写到数据库前执行的，因此事务回滚conn.rollback()要在实例提交命令conn.commit()之前。只要数据未提交就可以回滚，但回滚后ID却是自增的。请看下面的例子:
插入3条数据(注意事务回滚):
import pymysql
#连接数据库zcl
cOnn=pymysql.connect(host=&＃39;127.0.0.1&＃39;, user=&＃39;root&＃39;, passwd=&＃39;root&＃39;, db=&＃39;zcl&＃39;)
#生成游标，当前实例所处状态
cur=conn.cursor()
#插入数据
reCount=cur.execute(&＃39;insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)&＃39;, (&＃39;Jack&＃39;, &＃39;man&＃39;, 25, 1351234, "CN"))
reCount=cur.execute(&＃39;insert into students(name, sex, age, tel, nal) values(%s,%s,%s,%s,%s)&＃39;, (&＃39;Jack2&＃39;, &＃39;man&＃39;, 25, 1351234, "CN"))
reCount=cur.execute(&＃39;insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)&＃39;, (&＃39;Mary&＃39;, &＃39;female&＃39;, 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 |
+----+------+--------+-----+-------------+------+
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还是自增了，不会因回滚而取消，但这不影响数据的一致性（底层的原理我不清楚~）
四、批量插入数据
import pymysql
#连接数据库zcl
cOnn= pymysql.connect(host=&＃39;127.0.0.1&＃39;, user=&＃39;root&＃39;, passwd=&＃39;root&＃39;, db=&＃39;zcl&＃39;)
#生成游标，当前实例所处状态
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(&＃39;insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)&＃39;, 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 |
+----+-------+--------+-----+-------------+------+
7 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 |
+----+-------+--------+-----+-------------+------+
12 rows in set
学完的东西要及时总结，有些东西忘记了阿~_~
以上就是如何使用python对数据库(mysql)进行操作的详细内容，更多请关注 第一PHP社区 其它相关文章！
京公网安备 11010802041100号