Python之PyMySQL操作详解

详解"/>

Python之PyMySQL操作详解

一、PyMysql简介

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

Django中也可以使用PyMySQL连接MySQL数据库。

二、PyMySQL操作详解

2.1 PyMySQL安装

pip install pymysql

2.2 PyMySQL应用

2.2.1 基本使用

# coding=utf8import sys
import pymysql
import time# 连接database
conn = pymysql.connect(host="192.168.106.210", user="developer",password="developer",database="jw",charset="utf8")
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 定义要执行的SQL语句
sql = """
CREATE TABLE user (
id INT auto_increment PRIMARY KEY ,
name CHAR(10) NOT NULL UNIQUE,
age TINYINT NOT NULL
)ENGINE=innodb DEFAULT CHARSET=utf8;
"""
# 执行SQL语句
cursor.execute(sql)
# 关闭光标对象
cursor.close()
# 关闭数据库连接
conn.close()

2.2.2 防止SQL注入

# coding=utf8import sys
import pymysql
import timename = input("姓名:>>")
age = input("年龄:>>")
# 连接数据库
conn = pymysql.connect(host="192.168.106.210", user="developer",password="developer",database="jw",charset="utf8")
# 获取光标，输入SQL语句并执行
cursor = conn.cursor()# 自己拼接字符串，容易造成SQL注入问题
sql1 = "select * from user where name='%s' and age='%s';"%(name,age)
ret1 = cursor.execute(sql1)# 让pymysql来拼接，防止SQL注入
sql2 = "select * from user where name=%s and age=%s;"
ret2 = cursor.execute(sql2,[name,age])# 关闭光标和连接
cursor.close()
conn.close()
print(ret2)

2.2.3 用事务处理数据库操作

# coding=utf8import sys
import pymysql
import time# 连接数据库
conn = pymysql.connect(host="192.168.106.210", user="developer",password="developer",database="jw",charset="utf8")
# 获取光标，输入SQL语句并执行
cursor = conn.cursor()# 写SQL语句（ignore 忽略已存在的数据，保证批量添加全部执行）
sql = "INSERT IGNORE INTO user(name,age) VALUES(%s,%s);"
name1 = "王芳"
age1 = 29
name2 = "刘广"
age2 = 31
try:# 单条添加#cursor.execute(sql, [name1, age1])# 多条添加cursor.executemany(sql, ((name1, age1),(name2, age2)))# 把修改提交到数据库connmit()
except Exception as e:conn.rollback()  # 执行SQL语句有问题或提交有异常都回滚cursor.close()
conn.close()

2.2.4 动态获取数据

# coding=utf8import sys
import pymysql
import time# 连接数据库
conn = pymysql.connect(host="192.168.106.210", user="developer",password="developer",database="jw",charset="utf8")
# 获取光标，输入SQL语句并执行
cursor = conn.cursor()# 查询数据
sql = """ 
SELECT * FROM user LIMIT 2,10; 
"""
cursor.execute(sql)# 获取所有查询到的结果
ret1 = cursor.fetchall()
print(ret1)# 从查询语句中获取一条查询结果
# ret2 = cursor.fetchone()
# print(ret2)# 获取相应的行数
# ret3 = cursor.fetchmany(2)
# print(ret3)# 返回执行的sql语句
# ret4 = cursor.mogrify(sql)
# print(ret4)connmit()cursor.close()
conn.close()