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)‘‘‘运行正常