Python连接MySQL数据库

一、PyMySQL安装

pip install pymysql    # cmd中安装(命令行操作)

二、连接数据库

1、基本操作

# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(
    host='数据库ip地址',
    port='端口号',
    user='用户名',
    password='密码',
    database='数据库名',
    charset='utf8'  # 不要用utf-8
)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 得到一个可以执行SQL语句并且将结果作为字典返回的光标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 定义要执行的SQL语句(创建表结构)
sql = '''
create table userinfo(
id int auto_increment primary key,
username varchar(20) not null unique,
password varchar(20) not null
) engine=innodb default charset=utf8;
'''
# 执行SQL语句
cursor.execute(sql)
# 关闭光标对象
cursor.close()
# 关闭数据库连接
conn.close()

 

2、SQL注入

　　2.1 什么是SQL注入？

　　　　用户输入的内容有恶意的SQL语句，后端拿到用户输入的内容不做检测直接做字符串拼接，得到一个和预期不一致的SQL语句

　　2.2 如何解决SQL注入？

　　　　对用户输入的内容做检测

　　　　pymysql 模块内置了这种检测，我们只需要让pymysql帮我们拼接SQL语句

　　　　ret = cursor.execute(sql, [name, pwd])　　# 让pymysql 模块帮我们拼接SQL语句，执行SQL语句

'''
Mysql SQL注入
'''
import pymysql

# 获取用户输入
name = input('请输入用户名：')
pwd = input('请输入密码：')

# 1.连接数据库，得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='yang',
    database='day43',
    charset='utf8'
)
# 2.获取光标
cursor = conn.cursor()
# 3.执行SQL语句
sql = "select * from userinfo where username='%s' and password='%s';" % (name, pwd)
print(sql)
ret = cursor.execute(sql)
# 关闭
cursor.close()
conn.close()
if ret:
    print('登录成功')
else:
    print('登录失败')
'''
如果用户输入:(正确的用户名' -- 然后密码随便输入，也可以成功登录),因为--表示注释，相当于把后面的密码给注释掉了
或者用户输入:(错误的用户名' or 1=1 -- 然后密码随便输入，也可以成功登录)
所以用普通的字符串拼接是很危险的
'''

# 解决方法：用pymysql帮我们拼接SQL语句
import pymysql

# 获取用户输入
name = input('请输入用户名：')
pwd = input('请输入密码：')

# 1.连接数据库，得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='yang',
    database='day43',
    charset='utf8'
)
# 2.获取光标
cursor = conn.cursor()
# 3.执行SQL语句
sql = "select * from userinfo where username=%s and password=%s;"   # 按照pymysql模块的写法定义好占位符
print(sql)
ret = cursor.execute(sql, [name, pwd])  # 让pymysql模块帮我们拼接SQL语句，执行sql语句
# 关闭
cursor.close()
conn.close()
if ret:
    print('登录成功')
else:
    print('登录失败')

3、增删改查操作

　　增

'''
向userinfo表插入一条数据
'''
import pymysql

# 1.连接数据库，得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='yang',
    database='day43',
    charset='utf8'
)
# 2.获取光标
cursor = conn.cursor()
# 3.执行SQL语句
sql = "insert into userinfo(username, password) values (%s,%s);"cursor.execute(sql, ['石晓', '123'])
# 涉及操作数据库的，一定要提交
conn.commit()
# 关闭
cursor.close()
conn.close()

 

插入数据失败回滚

在执行增删改操作时，如果不想提交前面的操作，可以使用 rollback() 回滚取消操作。

'''
向userinfo表插入一条数据
'''
import pymysql

# 1.连接数据库，得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='yang',
    database='day43',
    charset='utf8'
)
# 2.获取光标
cursor = conn.cursor()
# 3.执行SQL语句
sql = "insert into userinfo(username, password) values (%s,%s);"
try:
    cursor.execute(sql, ['石晓', '123'])
    # 涉及操作数据库的，一定要提交
    conn.commit()
except Exception:
    # 有异常，回滚事务
    conn.rollback()
# 关闭
cursor.close()
conn.close()

获取插入数据的ID(关联操作时会用到)

'''
向userinfo表插入一条数据
'''
import pymysql

# 1.连接数据库，得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='yang',
    database='day43',
    charset='utf8'
)
# 2.获取光标
cursor = conn.cursor()
# 3.执行SQL语句
sql = "insert into userinfo(username, password) values (%s,%s);"
try:
    cursor.execute(sql, ['石晓', '123'])
    # 涉及操作数据库的，一定要提交
    conn.commit()
    # 提交之后，获取刚插入的数据的ID
    last_id = cursor.lastrowid
except Exception:
    # 有异常，回滚事务
    conn.rollback()
# 关闭
cursor.close()
conn.close()

批量执行

'''
向userinfo表批量插入数据
'''
import pymysql

# 1.连接数据库，得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='yang',
    database='day43',
    charset='utf8'
)
# 2.获取光标
cursor = conn.cursor()
# 3.执行SQL语句
sql = "insert into userinfo(username, password) values (%s,%s);"
data = [('何思浩','123'),('王瑞臣','123'),('老王','123')]
try:
    cursor.executemany(sql, data)
    # 涉及操作数据库的，一定要提交
    conn.commit()
except Exception:
    # 有异常，回滚事务
    conn.rollback()
# 关闭
cursor.close()
conn.close()

　　删

'''
从userinfo表删掉一条数据
'''
import pymysql

# 1.连接数据库，得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='yang',
    database='day43',
    charset='utf8'
)
# 2.获取光标
cursor = conn.cursor()
# 3.执行SQL语句
sql = "delete from userinfo where username=%s;"
cursor.execute(sql, ['alex'])
# 4.提交
conn.commit()
# 5.关闭
cursor.close()
conn.close()

　　改

'''
从userinfo把其中一个用户的密码改成789
'''
import pymysql

# 1.连接数据库，得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='yang',
    database='day43',
    charset='utf8'
)
# 2.获取光标
cursor = conn.cursor()
# 3.执行SQL语句
sql = "update userinfo set password=%s where username=%s"
cursor.execute(sql, ['789', 'taibai'])
# 4.涉及操作数据库的，一定要提交
conn.commit()
# 5.关闭
cursor.close()
conn.close()

　　查

'''
从userinfo查询数据
'''
import pymysql

# 1.连接数据库，得到一个连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='yang',
    database='day43',
    charset='utf8'
)
# 2.获取光标
cursor = conn.cursor()
# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 字典类型

# 3.执行SQL语句
sql = "select * from userinfo;"
cursor.execute(sql)
ret = cursor.fetchall()  # 查看全部数据
print(ret)
ret = cursor.fetchone()  # 查看单条数据
print(ret)
ret = cursor.fetchone()  # 查看单条数据
print(ret)
ret = cursor.fetchmany(3)  # 指定数量查看
print(ret)

# 4.关闭
cursor.close()
conn.close()

 　　进阶用法

# 移动光标位置
cursor.scroll(0, mode='absolute')   # 绝对位置，让光标移动到哪就到哪
cursor.scroll(-1, mode='relative')  # 相对位置，负数则向前移动，正数向后移动