mysql python操作

import pymysql
.
.
.
1.连接数据库

connection = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        db='test',
        cursorclass=pymysql.cursors.DictCursor
    )

2.游标
cursor = connection.cursor()

3. 结构化查询语句使用

conn = get_db_connection()
with conn.cursor() as cursor:
    table = 'class'
    sql = f"SELECT * FROM {table} limit 10"
    cursor.execute(sql)
    result = cursor.fetchall()
    print(result)
conn.close()

4.sql注入

with conn.cursor() as cursor:
    table = 'class'
    id = input("请输入班级id: ")
    sql = f"SELECT * FROM {table} WHERE cid={id} "
    
    cursor.execute(sql)
    result = cursor.fetchone()
    print(result)

当输入如下数据时，发生了我们不想要的行为

请输入班级id: 100 or 1=1
{'cid': 1, 'caption': '三年二班'}

• 采取如下方式防止sql注入

with conn.cursor() as cursor:
    name = input("请输入新的班级名称: ")
    sql = f"insert into {table} (caption) values (%s)"
    
    cursor.execute(sql, (name,))#使用参数化查询防止SQL注入
    conn.commit()
    print("插入成功")

5.excute方法`

sql = f"insert into {table} (caption) values (%s)"
cursor.executemany(sql, [(name1,),(name2,)])  #执行多条语句