PyMySQL 笔记

安装

pip install pymysql

查询

import pymysql

# 连接数据库
connect = pymysql.connect(host='localhost', port=3306, user='root', password='1234', db='send',
                          charset='utf8')
# 获取游标
cursor = connect.cursor()

# 执行sql
sql = "select * from employee"
cursor.execute(sql)

# 获取结果，fetchall 全部，fetchone 一行，fetchmany 多行
result = cursor.fetchone() # 返回是一个元组

print(result)

# 关闭连接
cursor.close()
connect.close()

新增

import pymysql

# 连接数据库
connect = pymysql.connect(host='139.129.233.191', port=3306, user='root', password='ch201688', db='cable',
                          charset='utf8')
# 获取游标
cursor = connect.cursor()

# 执行sql
sql = "insert into employee values(1, 'Tom', 10, 5000)"
result = cursor.execute(sql)

if result:
    connect.commit()  # 提交
else:
    connect.rollback()  # 回滚

print(result)  # 1，表示受影响行数

# 关闭连接
cursor.close()
connect.close()

防止SQL注入

使用 %s 预编译

# 获取游标
cursor = connect.cursor()

# 执行sql
sql = "select * from user where username = %s and password = %s"
# 参数化
params = ('admin', 'admin')
cursor.execute(sql, params)