天天看点
返回

Python 深入浅出 - PyMySQL 操作 MySQL 数据库

不知不觉 2017 就已经结束了，2018 元旦快乐。

回顾 2017 ，真是碌碌无为；希望 2018 勿忘初心，好好努力，早日实现新年愿望：

  • 提升自身技术，坚持把 Java 后端技术学好；
  • 轻松购买 MBP，而不会觉得价格贵；
  • 努力赚钱，买车；
  • 妹子；

祝自己好运~~~~

Python 的数据库接口标准时 Python DB-API，大多数 Python 数据库接口都遵循这个标准。为了兼容 Python 3，本文使用 PyMySQL 库进行操作 MySQL 数据库。

Python DB-API 使用流程

  1. 引入 API 模块
  2. 获取与数据库的连接
  3. 执行 SQL 语句和存储过程。
  4. 关闭数据库连接

PyMySQL 介绍

PyMySQL 是 Python 连接 MySQL 数据库服务器的接口，他实现了 Python 数据库 API v2.0,并包含了一个纯 Python 的 MySQL 客户端库。

PyMySQL参考文档：http://pymysql.readthedocs.io

安装 PyMySQL

C:\Users\Administrator> pip install PyMySQL

数据库简单操作

连接数据库之前，先使用 MySQL 客户端 SQLyog 手动创建好数据库和表：

# 创建数据库
CREATE DATABASE db_study

# 创建表
CREATE TABLE tb_student
(
    id INT NOT NULL AUTO_INCREMENT,
    `name` CHAR() NOT NULL,
    gender CHAR() DEFAULT NULL,
    age INT DEFAULT NULL,
    score INT DEFAULT NULL,
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8

此例查询数据库版本号：

# 0. import db
import pymysql
# 1. open database connection
db = pymysql.connect('localhost','root','root','db_study')

# 2. prepare a cursor object using cursor() method
cursor  = db.cursor()

# 3. execute SQL query using execute() method
cursor.execute('SELECT VERSION()')

# 4. fetch a single row using fetchone() method
version = cursor.fetchone()

print('database version : %s' % version)

# 5. disconnect from db server
db.close()

输出结果：

database version :

insert 操作 

# 0. import db
import pymysql
# 1. open database connection
db = pymysql.connect('localhost','root','root','db_study')

# 2. prepare a cursor object using cursor() method
cursor  = db.cursor()

# 3. execute SQL command
sql = "insert into tb_student(name,gender,age,score) values('mike','F',21,90 )"
print('sql = %s' % sql)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

print('insert sucess')

# 4. disconnect from db server
db.close()

输出结果：

sql = insert into tb_student(name,gender,age,score) values('mike','F',, )
insert sucess

select 操作

游标 Cursor 对象的属性和方法 描述
fetchone() 它用来获取使用游标对象来查询表时，返回的结果集对象中的下一行数据
fetchall() 它用来获取使用游标对象来查询表时，返回的结果集对象中的所有行数据
rowcount 只读属性，返回 execute() 方法影响的行数 
# 0. import db
import pymysql
# 1. open database connection
db = pymysql.connect('localhost','root','root','db_study')

# 2. prepare a cursor object using cursor() method
cursor  = db.cursor()

# 3. execute SQL command
sql_select = 'SELECT * FROM tb_student'
print(sql_select)
try:
    cursor.execute(sql_select)
    res  = cursor.fetchone()
    # cursor.scroll(0,'absolute')  # scroll 移动游标的位置,mode = 'relative' 或 'absolute'
    print('fetchone ===> id = %s,name = %s,gender = %s,age = %d,score = %d ' % (res[],res[],res[],res[],res[]))
    print('rowcount = %d' % cursor.rowcount)
    result = cursor.fetchall()
    for row in result:
        id = row[]
        name = row[]
        gender = row[]
        age = row[]
        score = row[]
        print('id = %s,name = %s,gender = %s,age = %d,score = %d' % (id,name,gender,age,score))
except:
    import traceback
    traceback.print_exc()
    print('execute error')

# 4. disconnect from db server
db.close()

输出结果：

SELECT * FROM tb_student
fetchone ===> id = ,name = mike,gender = F,age = ,score =  
rowcount = 
id = ,name = jane,gender = M,age = ,score =

数据库表中是有两条数据，但是上面代码中先使用了 cursor.fetchone() 获取一个数据，再使用 cursor.fetchall() 获取所有行数据，导致获取所有行时，只获取了剩下行的数据，这是因为在 fetchone() 之后，游标已经处于第 1 行的位置上，而不是第 0 行，要想 fetchall() 获取所有行数据，则需要将游标重新移动到第 0 行位置上，即使用 cursor.scroll(0,’absolute’) ,放开上面代码注释即可。

输出结果：

SELECT * FROM tb_student
fetchone ===> id = ,name = mike,gender = F,age = ,score =  
rowcount = 
id = ,name = mike,gender = F,age = ,score = 
id = ,name = jane,gender = M,age = ,score =

update 操作

将 age = 24 的记录的 score 增加 5 分

# 0. import db
import pymysql
# 1. open database connection
db = pymysql.connect('localhost','root','root','db_study')

# 2. prepare a cursor object using cursor() method
cursor  = db.cursor()
# 3. execute SQL command
sql_update = 'UPDATE tb_student SET score = score + 5 WHERE age = 24 '
print('sql_update = %s' % sql_update)
try:
    cursor.execute(sql_update)
    db.commit()
    print('update success')
except:
    db.rollback()
    print('update error')
# 4. disconnect from db server
db.close()

操作后的结果：

delete 操作 

# 0. import db
import pymysql

# 1. open database connection
db = pymysql.connect('localhost', 'root', 'root', 'db_study')

# 2. prepare a cursor object using cursor() method
cursor = db.cursor()

# 3. execute SQL command
# delete row
sql_delete = 'DELETE FROM tb_student WHERE age < 24'
print('sql_delete = %s' % sql_delete)
try:
    cursor.execute(sql_delete)
    db.commit()
    print('delete success')
except:
    db.rollback()
    print('delete error')
print('delete rowcount = %d' % cursor.rowcount)

# query all row
sql_select = 'SELECT * FROM tb_student'
print('sql_select = %s' % sql_select)
try:
    cursor.execute(sql_select)
    print('select rowcount = %d' % cursor.rowcount)
    result = cursor.fetchall()
    for row in result:
        id = row[]
        name = row[]
        gender = row[]
        age = row[]
        score = row[]
        print('id = %s,name = %s,gender = %s,age = %d,score = %d' % (id, name, gender, age, score))
except:
    import traceback
    traceback.print_exc()
    print('execute error')

# 4. disconnect from db server
db.close()

输出结果：

sql_delete = DELETE FROM tb_student WHERE age < 
delete success
delete rowcount = 
sql_select = SELECT * FROM tb_student
select rowcount = 
id = ,name = jane,gender = M,age = ,score =

执行事务

事务是确保数据一致性的一种机制，事务具有四大属性：

  • 原子性： 指事务包含的所有操作要么全部成功，要么全部失败回滚。
  • 一致性： 事务应确保数据库的状态从一个一致状态转变成另一个一致状态，一致状态的含义是数据库中的数据应满足完整性约束。
  • 隔离性 : 多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
  • 持久性： 一个事务一旦提交，它对数据库的修改应该永久保存在数据库中。
操作 描述
db.commit() 提交事务
db.rollback() 回滚事务

移动游标

移动游标操作 描述
cursor.scroll(1,mode = ‘relative’) 相对于当前位置移动
cursor.scroll(2,mode = ‘absolute’) 相对于绝对位置移动
深入浅出 Python Python
上一篇: java读取中文乱码解决方法
下一篇: pymysql执行有参数的in语句

继续阅读