pymysql 使用

一.安装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)'''

运行正常