Python3操作MySQL数据库

使用PyMySQL

使用Python3操作MySQL数据库我们需要安装三方库PyMySQL，安装方式如下：

$ pip install pymysql

数据库操作步骤为：

1. 建立数据库连接：conn = pymysql.connect()
2. 从连接建立操作游标：cur = conn.cursor()，或创建字典格式结果游标cur = conn.cursor(pymysql.cursors.DictCurser)
3. 使用游标执行sql（读/写）：cur.execute(sql)
4. 获取结果（读）/ 提交更改（写）： cur.fetchall()/conn.commit()
5. 关闭游标及连接：cur.close();conn.close()

游标cursor是指向数据缓冲区的一个变量，可以逐条的访问数据库执行结果集。PyMySQL中需要通过游标来执行sql和获取结果。

基本使用

import pymysql
# 1. 建立连接
conn = pymysql.connect(host='127.0.0.1',
                    port=3306,
                    user='root',
                    passwd='123456',   # password也可以
                    db='api_test',
                    charset='utf8')   # 如果查询有中文需要指定数据库编码        
# 2. 从连接建立游标（有了游标才能操作数据库）
cur = conn.cursor()
# 3. 查询数据库（读）
cur.execute("select * from user where name='张三'")
# 4. 获取查询结果
result = cur.fetchall()
print(result)
# 3. 更改数据库（写）
cur.execute("delete from user where name='李四'")
# 4. 提交更改
conn.commit()  # 注意是用的conn不是cur
# 5. 关闭游标及连接
cur.close()
conn.close()

数据库查询操作

使用cur.execute(), 执行数据库查询后无返回的是影响的行数，而非查询结果。我们要使用cur.fetchone()/cur.fetchmany()/cur.fetchall()来获取查询结果

• cur.fetchone()：获取一条数据（同时获取的数据会从结果集删除），返回元祖
• cur.fetchmany(3)：获取多条数据，返回嵌套元祖(('张三','123456'),('李四','123456'),("王五","123456"))
• cur.fetchall()：获取所有数据，返回嵌套元祖，(('张三','123456'),)

注： 获取完数据后，数据会从数据集中删除，再次获取获取不到，如：

cur.execute(select * from user where name='张三')
print(cur.fetchone()) # 取走一个结果 得到： ('张三','123456')  
print(cur.fetchone()) # 结果：None  因为上一步已经把结果取玩了
print(cur.fetchall()) # 结果：()
所以我们需要重复使用查询结果时，需要将查询结果赋给某个变量
cur.execute(select * from user where name='张三')
result = cur.fetchall()
print(result)  # 结果： ('张三','123456')
print(result)  # 结果： ('张三','123456')

注：如果想要获取字段名和值组合的字典形式的结果，可以使用字典游标 cur = conn.cursor(pymysql.cursors.DictCurser)，这样
cur.fetchone()获取到的是字典形式的结果,cur.fetchmany()和cur.fetchall()获取到的是列表嵌套字典。

数据修改操作

执行修改数据库的操作后不立即生效，使用连接conn.commit()提交后才生效，支持事物及回滚

try:
    cur.execute("insert into user (name,password) values ('张三', '123456')")
    cur.execute("insert into user (name, passwd) values ('李四'), '123456'") # 此处sql出错
    conn.commit()  # 使用连接提交所有更改
except Exception as e:
    conn.rollback()  # 回滚所有更改（注意用的是conn)
    print(str(e))

数据库操作封装

import pymysql
# 获取连接方法
class DB(object)
    def get_conn(self):
        self.conn = pymysql.connect(host='127.0.0.1',
                           port=3306,
                           user='test',
                           passwd='123456',  
                           db='api_test',
                           charset='utf8‘# 如果查询有中文，需要指定测试集编码
                           autocommit=True  # 使用autocommit
                           )
       self.cur = self.conn.cursor(pymysql.cursors.DictCursor)  # 使用字典类型的查询结果
   
# 封装数据库查询操作
def query (self, sql):
    self.cur.execute(sql)  # 执行sql
    result = cur.fetchall()  # 获取所有查询结果, 字典格式
return result  # 返回结果
def execute(self, sql):
    try:
        cur.execute(sql)  # 执行sql
except Exception as e:
        conn.rollback()  # 回滚
def  close(self):
    self.cur.close()  # 关闭游标
    self.conn.close()  # 关闭连接

封装后使用方法如下：

from db import DB
db1 = DB()
print(db1.query(“select * from user where name=‘张三’;”）
print(db.change(“insert into user (‘name’,’passwd’) values (‘张四’,’123456);”)

参考：PyMySQL最新文档