[ Python ] MySQL的使用

 

一、Python DB API

没有Python DB API之前，接口程序混乱

Python DB API：Python访问数据库的统一接口规范

 

1.1 数据库连接对象connect

• 连接对象：建立Python客户端与数据库的网络连接
• 创建方法：pymysqldb.connect(参数)

参数名	类型	说明
host	字符串	服务器地址
port	数字	服务器端口号
user	字符串	用户名
passwd	字符串	密码
db	字符串	数据库名称
charset	字符串	连接编码

• connect对象支持的方法：

方法名	说明
cursor()	使用该连接创建并返回游标
commit()	提交当前事务
rollback()	回滚当前事务
close()	关闭连接

 

1.2 游标对象cursor

• 游标对象：用于执行查询和获取结果
• cursor对象支持的方法：

参数名	说明
execute(opt[,args])	执行一个数据库查询和命令
fetchone()	取的结果集的下一行
fetchmany(size)	获取结果集的下几行
fetchall()	获取结果集中剩下的所有行
rowcount()	最近一次execute返回数据的行数或影响行数
close()	关闭游标对象

 

二、SQL语句操作

2.1 创建数据库表

# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("drop table if exists tablename")

# 使用预处理语句创建表
sql_create = """create table tablename (
		first_name char(20) not null,
		last_name char(20) not null)"""
cursor.execute(sql_create)	    

 

2.2 查

# SQL 查询语句
sql_select = "select * from tablename"
try:
   # 执行SQL语句
   cursor.execute(sql_select)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      # 打印结果
      print ("fname=%s,lname=%s" % \
             (fname, lname))
except:
   print ("Error: unable to fetch data")

 

2.3 增删改

# SQL 插入语句
sql_insert = """insert into tablename(first_name,last_name)
		values("First","Last")"""
try:
    # 执行sql语句
    cursor.execute(sql_insert)
    # 提交到数据库执行
    conn.commit()
except:
    # 如果发生错误则回滚
    conn.rollback()		

# SQL 更新语句
sql_update = "update tablename set firstname = 'F' where lastname = 'Last'"
try:
   # 执行SQL语句
   cursor.execute(sql_update)
   # 提交到数据库执行
   conn.commit()
except:
   # 发生错误时回滚
   conn.rollback()

# SQL 删除语句
sql_delete = "delete from tablename where firstname = 'F'"
try:
   # 执行SQL语句
   cursor.execute(sql_delete)
   # 提交修改
   conn.commit()
except:
   # 发生错误时回滚
   conn.rollback()

 

2.3 事务

事务：访问和更新数据库的一个程序执行单元

• 原子性：事务中包括的诸操作要么都做，要么都不做
• 一致性：事务必须使数据库从一致性状态变到另一个一致性状态
• 隔离性：一个事务的执行不能被其他事务干扰
• 持久性：事务一旦提交，它对数据库的改变就是永久性的。即connect.commit()一旦成功，前面所有的操作被持久化的存储在数据库，不会出现数据丢失的情况。

开发中怎样使用事务？

• 关闭自动commit：设置conn.autocommit(False)
• 正常结束事务：conn.commit()
• 异常结束事务：conn.rollback()

try:
    cursor.execute(sql_insert)
    print(cursor.rowcount)    # 执行SQL语句后影响的行数
    cursor.execute(sql_update)
    print(cursor.rowcount)
    cursor.execute(sql_delete)
    print(cursor.rowcount)
    conn.commit()    # 提交事务
except:
    # 发生错误时回滚,保证一组操作要么同时执行，要么同时不执行
    conn.rollback()

 

三、开发数据库程序流程

1. 导入模块

import MySQLdb    # 出现报错
>> ModuleNotFoundError: No module named 'MySQLdb'

网上说 MySQLdb 只适用于 python 2.x ，python 3.x 中的替代模块为 pymysql

import pymysql
print(pymysql)    # 测试模块是否导入成功

2. 创建connect对象，获取cursor
3. 使用cursor执行SQL
4. 使用cursor获取数据、判断执行状态
5. 提交事务 或者 回滚事务
6. 关闭cursor、关闭connect

访问数据库流程完整代码如下：

# 导入模块
import pymysql

# 创建connect对象，获取cursor
conn = pymysql.connect(host="localhost",port=3306,user="root",passwd="123456",db="mysql_db")    # 分别对应host、port、user、passwd、db
cursor = conn.cursor()

try:
    # 使用cursor执行SQL语句
    sql_create = """create table StuInfo(
    		    stuNo char(20) not null,
    		    stuName char(20) not null)"""
    sql_insert = """insert into StuInfo
    		    values('2018212121','钱一')"""
    sql_select = "select * from StuInfo"
    cursor.execute(sql_create)
    cursor.execute(sql_insert)
    cursor.execute(sql_select) 
    
    # 使用cursor获取数据、判断执行状态
    results = cursor.fetchall()
    
    # 提交事务
    conn.commit()
    print(results)

except:
    # 发生错误,执行回滚
    conn.rollback()

# 关闭cursor、connect
cursor.close()
conn.close()

>> (('2018212121', '钱一'),)

Navicat可视化数据库运行结果截图：