1. 安装pymysql
使用PyCharm安装:点击file >> setting >> project,在当前工程的虚拟环境下安装 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20210715142248324.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMTQzNDY5,size_16,color_FFFFFF,t_70) 使用pip命令安装:pip3 install pymysql==1.0.2
2. 导入pymysql
import pymysql
3. 1查询数据库操作
conn = pymysql.connect(host='localhost', user='root', passwd='mysql',
db='insight', port=3306, charset='utf8')
cursor = conn.cursor()
sql = '''
SELECT id, stu_name, gender, age, address, phone, others
FROM `students`
WHERE id = '1';
'''
cursor.execute(sql)
result = cursor.fetchall()
print(result )
cursor.close()
conn.close()
3.2 修改数据库操作
conn = pymysql.connect(host='localhost', user='root', passwd='mysql',
db='insight', port=3306, charset='utf8')
cursor = conn.cursor()
sql = "delete FROM students where id='1'";
try:
cursor.execute(sql)
conn.commit()
except Exception as e:
conn.rollback()
print("数据库修改出错,原因为:", e)
finally:
cursor.close()
conn.close()
3.3 防止sql注入
sql注入:用户提交带有恶意的数据与sql语句进行字符串方式的拼接,影响sql语句的语义,产生数据泄露的现象。 防止措施:sql语句参数化。
sql = "SELECT * FROM students WHERE stu_name = %s;"
cursor.execute(sql,("xx",))
sql = "insert into students(id, stu_name) values (%s, %s);"
cursor.execute(sql, ["1", "xx"])
|