pymysql

参数（会导致SQL注入）
 

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    user = "root",
    password= "root",
    host= "127.0.0.1",
    port= 3306,
    database= "test"
)

# 创建游标对象
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)

name = input("请输入用户名")
password = input("请输入密码")
# id = input("请输入id")

# 准备sql

# 参数传递 方式一
#sql = "select * from t_user where name = '"+name+"' and  password = '"+password+"'"
#sql = "select * from t_user where id = "+id
#sql = "select * from t_user where id = "+id+" and name = '"+name+"'"

# 参数传递 方式二
sql = "select * from t_user where name = '%s' and password = '%s'"%(name,password)
print(sql)

# 执行sql语句，返回结果总条数
resultCount = cur.execute(sql)

#查询数据内容
result = cur.fetchall()

print(resultCount)
print(result)

if resultCount:
    print("登录成功")
else:
    print("用户名或密码错误")

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

防止SQL注入：
 

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    user = "root",
    password= "root",
    host= "127.0.0.1",
    port= 3306,
    database= "test"
)

# 创建游标对象
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)

name = input("请输入用户名")
password = input("请输入密码")
# id = input("请输入id")

# 准备sql

# 参数传递 方式二
# sql = "select * from t_user where name = %s and password = %s"
# 执行sql语句，返回结果总条数
# resultCount = cur.execute(sql,(name,password))


# sql = "select * from t_user where name = %s and password = %s"
# 执行sql语句，返回结果总条数
# resultCount = cur.execute(sql,[name,password])


sql = "select * from t_user where name = %(name)s and password = %(pass)s"
# 执行sql语句，返回结果总条数
resultCount = cur.execute(sql,{"name":name,"pass":password})

print(sql)
#查询所有数据内容
result = cur.fetchall()


print(result)

if resultCount:
    print("登录成功")
else:
    print("用户名或密码错误")

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

DQL:
 

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    user = "root",
    password= "root",
    host= "127.0.0.1",
    port= 3306,
    database= "test"
)

# 创建游标对象
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)


# 准备sql
sql = "select * from t_user"
# 执行sql语句，返回结果总条数
resultCount = cur.execute(sql)

#查询所有数据内容
# result = cur.fetchall()


# 按照顺序查询某一条内容
# result = cur.fetchone()
# result2 = cur.fetchone()
# result3 = cur.fetchone()
# result4 = cur.fetchone()
# print(result)
# print(result2)
# print(result3)
# print(result4)

# 查询指定数量的内容
result = cur.fetchmany(5)
print(result)

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

DML:

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    user = "root",
    password= "root",
    host= "127.0.0.1",
    port= 3306,
    database= "test"
)

# 创建游标对象
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)

# name = input("请输入用户名")
# password = input("请输入密码")
# address = input("请输入地址")

# 准备增加sql
# sql = "insert into t_user values (null,%s,%s,%s)"
# # 执行sql语句，返回结果总条数
# resultCount = cur.execute(sql,[name,password,address])


# 准备删除sql
# sql = "delete from t_user where id = %s"
# # # 执行sql语句，返回结果总条数
#
# resultCount = cur.execute(sql,[5])


# 准备修改sql
sql = "update t_user set password = %s,name = %s,address = %s where id = %s"
# # 执行sql语句，返回结果总条数

resultCount = cur.execute(sql,["789789","王五","某某某开发公司",3])
print(resultCount)


if resultCount:
    print("修改成功")
else:
    print("添加失败")

conn.commit()

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