如何使用python对数据库(mysql)进行操作-Python教程-PHP中文网

如何使用python对数据库(mysql)进行操作

迷茫

发布： 2017-03-25 16:51:22

原创

1883人浏览过

一、数据库基本操作

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中文网其它相关文章！