Python - MySQL 之 pymysql模块

1、PyMySQL的安装

pymysql是在 Python3版本中用于连接 mysql服务器的一个库，Python2中是使用mysqldb。

pip3 install PyMySQL

2、使用python操作mysql数据库

(1)、python连接mysql数据库

import pymysql

db = pymysql.connect("数据库ip","用户","密码","数据库")    # 打开数据库连接
cursor = db.cursor()                                     # 得到一个可以执行SQL语句的光标对象
cursor.execute("SELECT VERSION()")                       # 使用 execute() 方法执行 SQL 查询
data = cursor.fetchone()                                 # 使用 fetchone() 方法获取单条数据
print ("Database version : %s " % data)
db.close()                                               # 关闭数据库连接

(2)、 创建表操作

import pymysql 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
cursor.execute(sql)
 
# 关闭数据库连接
db.close()

(3)、对数据进行增删改查

import pymysql

# 一、打开数据库连接
conn=pymysql.connect(host='localhost',user='testuser',password="test123",database='TESTDB')

# 二、获取游标
cur = conn.cursor(pymysql.cursors.DictCursor)  # cur游标 cur数据库操作符（相当于文件操作符-- 文件句柄f）

# 三、对数据的增删改查

# 查
ret = cur.execute('select * from books')   # 使用 execute() 方法执行 SQL 查询； execute方法中放mysql查询语句；ret 影响行数，在这里表示查到的数据行数
for i in range(ret):       # ret为数据行数
    row1 = cur.fetchone()   # 每次取一条数据
    print(row1)
row2 = cur.fetchmany(3)    # 按照指定参数取n条
print(row2)
row3 = cur.fetchall()       # 取所有，过于浪费内存
print(row3)

#   增     改     删      提交        (增删改相似，使用相应的mysql语句即可)
# insert update delete  conn.commit()
cur = conn.cursor()     # cur游标 cur数据库操作符
cur.execute('insert into books values("学python从开始到放弃","cai","家里蹲大学出版社",50,"2019-6-1")')     # 在execute方法中放增删改相关的mysql语句
conn.commit()     # 进行提交，提交到数据库执行

(4)、操作文件

## file文件 文件内容
'''
学python从开始到放弃|cai|家里蹲大学出版社|50|2018-7-1
学mysql从开始到放弃|ling|城乡工业出版社|60|2018-6-3
学html从开始到放弃|liu|城乡工业出版社|20|2018-4-1
学css从开始到放弃|liangliang|城乡工业出版社|120|2018-5-2
学js从开始到放弃|yongliang|城乡工业出版社|100|2018-7-30
'''

import pymysql
conn=pymysql.connect(host='localhost',user='testuser',password="test123",database='TESTDB')
cur = conn.cursor()
sql = 'insert into books values(%s,%s,%s,%s,%s)'
with open('file',encoding='utf-8') as f:
    for line in f:
        try:
            lst = line.strip().split('|')
            cur.execute(sql,lst)   # execute('insert into books values(%s,%s,%s,%s,%s)'["学python从开始到放弃","cai","家里蹲大学出版社",50,"2018-7-1")'])
            conn.commit()
        except:
            conn.rollback()  # 如果发生错误则回滚
cur.close()    # 关闭游标
conn.close()	# 关闭连接

3、SQL注入

# 登陆注册 + 数据库    表 userinfo
import pymysql
conn=pymysql.connect(host='localhost',user='testuser',password="test123",database='TESTDB')
cur = conn.cursor()
name = input('user:')
passwd = input('password:')
sql = "select * from userinfo where username = %s and password = %s;"
print(sql)
ret = cur.execute(sql,(name,passwd))
if ret:   # ret = 1
    print('登陆成功')
else:    # ret = 0
    print('登陆失败')

# sql注入问题
select * from userinfo where username = 'xxx' or 1=1 ;       # -- ' and password =

小结：

1.建立连接conn
2.获取游标 cur
3.执行 sql execute(sql , (可迭代类型的参数集))
	sql是查   --- 只涉及文件的读操作
		fetchone()
		fetchmany(n)
		fetchall()
	增删改 --- 涉及到文件的写操作
		conn.commit()
4.关闭游标和连接
	cur.close()
	conn.close()