我们想要在我们的mython程序中使用mysql，首先需要安装pymysql模块，安装方式可以使用cmd命令安装，
pip3.x install pymysql
首先在我们连接数据库之前先创建一个user表，方便测试功能；
CREATE TABLE `user`(
id TINYINT AUTO_INCREMENT KEY,
name CHAR(15) NOT NULL UNIQUE,
password VARCHAR(30) NOT NULL
)ENGINE=INNODB CHARSET=UTF8;
INSERT user (name,password)
VALUES
('moud','123'),
('linda','123'),
('tom','123');
2 使用Python脚本实现增删改查和事务处理，源码如下：
# encoding:utf-8
# Author:"richie"
# Date:9/14/2017
import pymysql conn = pymysql.connect(
host = 'localhost',
user= 'root',
password='',
database='test',
charset='utf8') # 获取游标
cursor = conn.cursor() # 插入数据
sql = """INSERT IGNORE INTO user (name,password) VALUES (%s,%s)"""
data = ('mimi','')
# cursor.execute(sql,data)
conn.commit()
print('成功插入',cursor.rowcount,'条数据') # REPLACE works exactly like INSERT sql = """REPLACE INTO user (name,password) VALUES (%s,%s)"""
data = ('mimi','')
cursor.execute(sql,data)
conn.commit()
print('成功插入',cursor.rowcount,'条数据')
# 修改数据 sql = """UPDATE user SET password=%s WHERE id=%s"""
data = ('',1)
cursor.execute(sql,data)
conn.commit()
print('成功修改',cursor.rowcount,'条数据') # 查询数据
sql = """SELECT name,password FROM user WHERE id = %s"""
data = (2,)
cursor.execute(sql,data)
for row in cursor.fetchall():
print("name:%s\tpasswd:%s" % row)
print('共查找出',cursor.rowcount,'条数据') # # 删除数据
sql = """DELETE FROM user WHERE id = %s"""
data = (1,)
cursor.execute(sql,data)
conn.commit()
print('成功删除',cursor.rowcount,'条数据') cursor.close()
conn.close()