Python使用pymysql模块操作数据库实现增删改查

Python3.x 如果想连接MySQL需要安装 pymysql 模块。

pip install pymysql

参数

pymysql.Connect()参数说明
host(str):      MySQL服务器地址
port(int):      MySQL服务器端口号
user(str):      用户名
passwd(str):    密码
db(str):        数据库名称
charset(str):   连接编码

connection对象支持的方法
cursor()        使用该连接创建并返回游标
commit()        提交当前事务
rollback()      回滚当前事务
close()         关闭连接

cursor对象支持的方法
execute(op)     执行一个数据库的查询命令
fetchone()      取得结果集的下一行
fetchmany(size) 获取结果集的下几行
fetchall()      获取结果集中的所有行
rowcount()      返回数据条数或影响行数
close()         关闭游标对象

首先在连接数据库之前，先创建一个交易表，方便测试 pymysql 的功能：

DROP TABLE IF EXISTS `trade`;

CREATE TABLE `trade` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(6) NOT NULL COMMENT '用户真实姓名',
  `account` varchar(11) NOT NULL COMMENT '银行储蓄账号',
  `saving` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '账户储蓄金额',
  `expend` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '账户支出总计',
  `income` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '账户收入总计',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO `trade` VALUES (1,'乔布斯','18012345678',0.00,0.00,0.00);

python程序实现

import pymysql.cursors

# 连接数据库
def connect_to_db():
    connect = pymysql.Connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='3306',
        db='trade',
        charset='utf8'
    )
    return connect

# 插入数据
def insert_data(name, account, saving):
    connect = connect_to_db()
    cursor = connect.cursor()
    sql = "INSERT INTO trade (name, account, saving) VALUES (%s, %s, %s)"
    data = (name, account, saving)
    cursor.execute(sql, data)
    connect.commit()
    cursor.close()
    connect.close()

# 修改数据
def update_data(account, saving):
    connect = connect_to_db()
    cursor = connect.cursor()
    sql = "UPDATE trade SET saving = %s WHERE account = %s"
    data = (saving, account)
    cursor.execute(sql, data)
    connect.commit()
    cursor.close()
    connect.close()

# 查询数据
def select_data(account):
    connect = connect_to_db()
    cursor = connect.cursor()
    sql = "SELECT name, saving FROM trade WHERE account = %s"
    data = (account,)
    cursor.execute(sql, data)
    results = cursor.fetchall()
    cursor.close()
    connect.close()
    return results

# 删除数据
def delete_data(account):
    connect = connect_to_db()
    cursor = connect.cursor()
    sql = "DELETE FROM trade WHERE account = %s LIMIT 1"
    data = (account,)
    cursor.execute(sql, data)
    connect.commit()
    cursor.close()
    connect.close()

# 测试插入数据
insert_data('101', '1390000000x', 10000)
print('成功插入数据')

# 测试修改数据
update_data('1390000000x', 8888)
print('成功修改数据')

# 测试查询数据
results = select_data('1390000000x')
for row in results:
    print("Name: {}\tSaving: {:.2f}".format(row[0], row[1]))

# 测试删除数据
delete_data('1390000000x')
print('成功删除数据')