Python：连接MySQL数据库

00下载pymysql

pip install pymysql

01查询操作

# 导入pymysql包
import pymysql


# 创建连接对象
conn = pymysql.connect(host="localhost", port=3306, user="root", password="mysql", database="python_test_1", charset="utf8")
# 获取游标对象
cs = conn.cursor()

# pymysql完成数据的查询操作
sql = "select * from students;"
# 这里获取的是sql影响的行数
# content = cs.execute(sql)
# print(content)
cs.execute(sql)
content = cs.fetchone()
print(content)
content = cs.fetchall()
print(content)

# 关闭游标和连接
cs.close()
conn.close()

02增删改操作

# 导入pymysql包
import pymysql


# 创建连接对象
conn = pymysql.connect(host="localhost", port=3306, user="root", password="mysql", database="python_test_1", charset="utf8")
# 获取游标对象
cs = conn.cursor()

# 增加数据
# sql = "insert into students(name) values('老王')"
# cs.execute(sql)
# 删除数据
# sql = "delete from students where id=18"
# cs.execute(sql)
# 修改数据
sql = "update students set name='老王' where id=1;"
cs.execute(sql)

# pymysql完成数据的查询操作
sql = "select * from students;"
# for循环显示数据
cs.execute(sql)
content = cs.fetchall()
for i in content:
    print(i)

# 提交操作
conn.commit()

# 关闭游标和连接
cs.close()
conn.close()

03SQL注入

# 导入pymysql包
import pymysql


# 创建连接对象
conn = pymysql.connect(host="localhost", port=3306, user="root", password="mysql", database="python_test_1", charset="utf8")
# 获取游标对象
cs = conn.cursor()

# 不安全的方式
# 根据id查询学生信息
# find_name = input("请输入您要查询的学生姓名:")
# sql = "select * from students where name='%s'" % find_name
# # 显示所有的数据
# cs.execute(sql)
# content = cs.fetchall()
# for i in content:
#     print(i)

# 安全的方式
# 根据id查询学生信息
find_name = input("请输入您要查询的学生姓名:")
sql = "select * from students where name=%s"
# 显示所有的数据
cs.execute(sql, [find_name])
content = cs.fetchall()
for i in content:
    print(i)

# 关闭游标和连接
cs.close()
conn.close()