一. 安装 pyMySQL 模块
pip install pymysql
二, 导入需要的模块
import pymysql
三. 构建数据库属性
host = ‘localhost’
username = ‘root’
password = ‘root’
db_name = ‘test’
四. 写 sql 语句
insert_table_sql = ”’insert into user values (null ,’lw’,’555222000′)”’
find_table_sql = ”’select * from user”’
delete_table_sql = ”’delete from user where user_id={user_id}”’
五. 使用 pymysql 连接数据库
conn = pymysql.connect(host=host,
user=username,
password=password,
db=db_name)
六. 执行 SQL 语句
try:
with conn.cursor() as cursor:
# 插入数据
# cursor.execute(insert_table_sql.format(username=’ll’, password=’123′))
# conn.commit()
# 删除
# cursor.execute(delete_table_sql.format(user_id=’3′))
# pymysql 删除数据时, 如果, 没有提交, 数据库中数据不变, 但是查询到的东西没有
# 要删除的那条数据
# conn.commit()
# 修改数据库
cursor.execute(update_table_sql.format(user_id=5))
# 如果修改没有提交, 在代码查看到的数据已经修改, 单数数据库中的数据没有修改
conn.commit()
# 查询全部数据
cursor.execute(find_table_sql)
result = cursor.fetchall()
print(result)
finally:
conn.close()
七. 防 sql 注入
修改插入数据 sql 语句为:
insert_table_sql = ”’insert into user(user_id,user_name,password) values (%S ,%S,%S)”’
执行代码修改为
cursor.execute(insert_table_sql, (1, ‘ll’, ‘123’))
conn.commit()
运行后报错
Traceback (most recent call last):
File “D:/creator/pythonProject/0002.py”, line 55, in <module>
save_code()
File “D:/creator/pythonProject/0002.py”, line 33, in save_code
cursor.execute(insert_table_sql, (1, ‘ll’, ‘123’))
File “D:\creator\pythonProject\venv\lib\site-packages\pymysql\cursors.py”, line 168, in execute
query = self.mogrify(query, args)
File “D:\creator\pythonProject\venv\lib\site-packages\pymysql\cursors.py”, line 147, in mogrify
query = query % self._escape_args(args, conn)
ValueError: unsupported format character ‘S’ (0x53) at index 54
错误原因: 字符占位符写错的应该是 %s 而不是 $S s 应该小写
修改语句为
insert_table_sql = ”’insert into user(user_id,user_name,password) values (%s ,%s,%s)”’
运行正常