Pymysql

pip -V   查看当前的pip版本

python -m pip install --upgrade pip   升级pip版本

pip list   查看当前python解释器环境中安装的第三方包和版本

pip install pymysql   安装pymysql

import pymysql

# 获取用户输入
name = input('请输入用户名：')
pwd = input('请输入密码：')
# 连接数据库，得到一个连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', database='a', charset='utf8')   # ip，端口，用户名，密码，用到的库，编码方式
# 获取光标
cursor = conn.cursor()
# 得到sql语句
sql = "select * from userinfo where username='%s' and password='%s';" % (name, pwd)
# 使用光标对象执行sql语句
ret = cursor.execute(sql)
# 关闭
cursor.close()
conn.close()
# 得到结果
if ret:
    print('登录成功')
else:
    print('登录失败')

import pymysql

name = input('请输入用户名：')
pwd = input('请输入密码：')
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', database='a', charset='utf8')
cursor = conn.cursor()
sql = "select * from userinfo where username='%s' and password='%s';" % (name, pwd)
ret = cursor.execute(sql)
cursor.close()
conn.close()
if ret:
    print('登录成功')
else:
    print('登录失败')
#问题：
# 使用格式化输出时用户输入用户名+单引号+空格+--+空格会跳过密码验证
# 或者随便一个用户名+单引号+空格+or+1=1+空格+--+空格也会跳过验证

import pymysql
name = input('请输入用户名：')
pwd = input('请输入密码：')
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', database='a', charset='utf8')
cursor = conn.cursor()
sql = "select * from userinfo where username=%s and password=%s;"
# 按照pymysql模块的写法定义好占位符
ret = cursor.execute(sql, [name, pwd])   #让pymysql模块帮我们拼接执行
cursor.close()
conn.close()
if ret:
    print('登录成功')
else:
    print('登录失败')

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', database='a',charset='utf8')
cursor = conn.cursor()
sql = "insert into userinfo(username, password) values(%s,%s);"
ret = cursor.execute(sql, ['qwer', 'df'])
conn.commit()   #涉及到操作数据库的，一定要提交
cursor.close()
conn.close()

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', database='a', charset='utf8')
cursor = conn.cursor()
sql = "delete from userinfo where username=%s;"
ret = cursor.execute(sql, ['qwer'])   # 让pymysql帮我们拼接并执行
conn.commit()   # 提交提交提交commit
cursor.close()
conn.close()

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', database='a', charset='utf8')
cursor = conn.cursor()
sql = "update userinfo set password=%s where username=%s;"
ret = cursor.execute(sql, ['123', 'wang'])
conn.commit()
cursor.close()
conn.close()

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', database='a', charset='utf8')
cursor = conn.cursor()
sql = 'select * from userinfo;'
cursor.execute(sql)
ret = cursor.fetchall()   #打印所有
ret = cursor.fetchone()   #打印一个
ret = cursor.fetchmany(1)    #打印1个  ((1, 'wang', '123'),)
print(ret)
cursor.close()
conn.close()
# 结果是元组的形式打印所有的结果


import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123', database='a', charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)   # 设置成字典
sql = 'select * from userinfo;'
cursor.execute(sql)
ret = cursor.fetchmany(1)
print(ret)   #[{'id': 1, 'username': 'wang', 'password': '123'}]
ret = cursor.fetchone()
print(ret)   # {'id': 1, 'username': 'wang', 'password': '123'}
cursor.scroll(0, mode='absolute')
print(cursor.fetchone())  #绝对位置，你让光标在哪就在哪
cursor.scroll(-1, mode='relative')
print(cursor.fetchone())  #相对位置，基于现在的位置移动，-1表示往前移动一行
cursor.close()
conn.close()
# 结果是字典的形式打印所有的结果，超过不报错

在执行增删改操作时，如果不想提交前面的操作，可以使用 rollback() 回滚取消操作。
# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);"
username = "wang"
age = 18
try:
    # 执行SQL语句
    cursor.execute(sql, [username, age])
    # 提交事务
    conn.commit()
except Exception as e:
    # 有异常，回滚事务
    conn.rollback()
cursor.close()
conn.close()

import pymysql


# 1. 连接数据库,得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='day43',
    charset='utf8'
)
# 2. 获取光标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3. 执行SQL语句
# 3.1 得到SQL语句
sql = "insert into userinfo(username, password) values (%s, %s)"  # 按照pymysql模块的写法定义好占位符
# 3.2 使用光标对象执行SQL语句
cursor.execute(sql, ['wang', 'wang123'])  # 让pymysql模块帮我们拼接sql语句，执行SQL语句

# 创建一个出版社和书籍
sql1 = "insert into p(name) values ('北大出版社');"
cursor.lastrowid  # 获取刚才插入到数据库的id值
sql2 = "insert into book(title, p_id) values (%s, %s)"


conn.rollback()  # 回滚
conn.commit()
# 关闭
cursor.close()
conn.close()

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123',database='a')
cursor=conn.cursor()

sql='insert into userinfo(username,password) values("xxx","123");'
rows=cursor.execute(sql)
print(cursor.lastrowid) #在插入语句后查看

conn.commit()

cursor.close()
conn.close()