Python操作MySQL数据库01

查询
# 1.导包
import pymysql
# 2.创建连接对象
# host,port,user,password,database,charset
conn = pymysql.connect(host="localhost",
                       port=3306,
                       user="root",
                       password="Abcd_1234",
                       database="test2",
                       charset="utf8")
# 3.获取游标，目的就是执行SQL语句
cursor = conn.cursor()
# 准备sql语句，具体写法与workbench里面的写法一样
#sql = "SELECT * FROM student where name = '%s'; " % "黄蓉' or 1=1 or '"
# 准备sql语句，防止sql注入
sql = "SELECT * FROM student where name = %s;"
# 4.执行sql语句
cursor.execute(sql)
# 获取查询结果，返回的数据类型是一个元组
# row = curor.fetchone()
# print(row)
# 返回的数据类型是一个元组，其中元组里面的每条数据还是元组
result = cursor.fetchall()
for row in result:
    print(row)
print(sql)
# 5. 关闭游标
cursor.close()
# 6.关闭连接
conn.close()

增删改操作
# 1.导包
import pymysql
# 2.创建连接对象
# host,port,user,password,database,charset
conn = pymysql.connect(host="localhost",
                       port=3306,
                       user="root",
                       password="Abcd_1234",
                       database="test2",
                       charset="utf8")
# 3.获取游标，目的就是执行SQL语句
curor = conn.cursor()
# 准备sql语句，具体写法与workbench里面的写法一样
#插入
# sql = "insert into student values(4,'Peter', '男',29); "
# 修改
# sql = "update student set age=33 where id =4;"
# 修改
sql = "delete from  student  where id =3;"
# 4.执行sql语句
try:
    curor.execute(sql)
    # 提交修改的数据到数据库
    conn.commit()
except Exception as e:
    # 数据回滚
    conn.rollback()
finally:
    # 5. 关闭游标
    curor.close()
    # 6.关闭连接
    conn.close()

参数化操作（插入）
# 1.导包
import pymysql
# 2.创建连接对象
# host,port,user,password,database,charset
conn = pymysql.connect(host="localhost",
                       port=3306,
                       user="root",
                       password="Abdd_1234",
                       database="test2",
                       charset="utf8")
# 3.获取游标，目的就是执行SQL语句
curor = conn.cursor()
# 准备sql语句，具体写法与workbench里面的写法一样
sql = "insert into student(id ,name,gender,age) values(%s, %s, %s, %s); "
# sql = "update student set age=33 where id =2;"
# sql = "delete from  student  where id =3;"
# 4.执行sql语句
try:
  curor.execute(sql, [5, '司马懿', '男', 76])
    # 提交修改的数据到数据库
    conn.commit()
except Exception as e:
    # 数据回滚
    conn.rollback()
finally:
    # 5. 关闭游标
    curor.close()
    # 6.关闭连接
    conn.close()