使用python操作mysql数据库

一、pymysql的使用

1.首先在python中安装pymysql模块(CMD窗口命令下)。

pip install pymsql
安装完成后导入
import pymysql

2.pyysql 连接数据库的必要参数： 主机、端口、用户名。密码、数据库

注意：pymysql不能提供创建数据库的服务，数据库要提前创建

3.连接步骤：

​ -1. 建立数据库连接对象 conn
​ -2. 通过conn创建操作mysql的 cursor(游标对象)
​ -3. 编写sql语句交给 cursor 执行
​ -4. 如果是查询，也要通过cursor对象，获取结果
​ -5. 操作完毕，端口操作与连接

-1. 建立数据库连接对象 conn

conn = pymysql.connect(user='用户名',passwd='密码'，database='数据库名')
# 例 conn = pymysql.connect(user='root', passwd='root', database='oldboy')

-2. 通过conn创建操作mysql的 cursor(游标对象)

注：游标不设置参数，查询的结果就是数据元组，数据没有标识性
设置pymysql.cursor.DictCursor，查询的结果是字典，key是表的字段

cursor = conn.cursor(pymysql.cursors.DictCursor)

-3. 编写sql语句交给 cursor 执行

创建表

sql1='create table t1(id int,x int,y int)'
cursor,execute(sql1)

增

sql2 = 'insert into t1 values(%s, %s, %s)'

# execute一次增加1条记录
cursor.execute(sql2,(1,10,100)).
cursor.execute(sql2,(1,10,100))

# 重点：在创建conn对象时，不设置autocommit，默认开启事务，增删改操作不会直接映射到数据库中
# 需要执行conn.commit() 动作
conn.commit()

# executemany一次增加多条记录
cursor.executemany(sql2,[(3,30,300),(4,40,400)])
conn.commit()

删

sq3 = 'delete from t1 where id=%s'
cursor.execute(sql3,4)
conn.commit()

改

sql4 = 'update t1 set y=666 where id=%s'
cursor.execute(sql4,4)
conn.commit()

查

sql5 = 'select * from t1'
row = cursor.execute(sql5) # 返回值是受影响的行数
print(row)

# -4.如果是查询，通过 cursor对象、获取结果
# fetchone() 偏移一条取出，fetchmany(n) 偏移n条取出，fetchall(偏移全部取出)
r1 = cursor.fetchone()
print(r1)
r2 = cursor.fetchone()
print(r2)
r3 = cursor.fetchmany(1)
print(r3)
r4 = cursor.fetchcall()
print(r4)

# -5操作完毕后。端口操作与连接
cursor.close()
conn.close()

---

二、游标操作

import pymysql
from pymysql.cursors import DictCursor

# 1 建立数据库连接
conn = pymysql.connect(user='tomjoy',passwd='123456',db='db1')
# 2 通过conn创建操作sql的游标对象
cursor = conn.cursor(DictCursor)
# 3.编写sql交给 cursor 执行
sql = 'select * from t1 '
# 4 如果是查询，通过cursor对象 获取结果
row = cursor.execute(sql)
if row:
    r1 = cursor.fetchmany(2)
    print(r1)

    # 操作游标
    # cursor.scroll(0,'absolute') # absolute绝对偏移，游标重置，从头开始偏移
    cursor.scroll(-2,'relative') # relative相对偏移，游标在当前位置进行左右偏移

    r2 = cursor.fetchone()
    print(r2)

# 5.操作完毕，端口操作与连接
cursor.close()
conn.close()

---

三、pymysql事务

import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='tomjoy',passwd='123456',db='db2')
cursor = conn.cursor(DictCursor)

try:
    sql = 'create table t3(id int, name char(4), money int )'
    row = cursor.execute(sql)
    print(row)
except:
    print('表已创建')
    pass

# 空表才插入
row = cursor.execute('select * from t3')
if not row:
    sql = 'insert into t3 values(%s,%s,%s)'
    row = cursor.executemany(sql,[(1,'tom',10),(2,'BOb',20)])
    conn.commit()

# 可能会出现异常的sql
'''
try:
    sql1 = 'update t3 set money=money-1 where name="tom"'
    cursor.execute(sql1)
    sql2 = 'update t3 set money=money+1 where name="Bob"'
    cursor.execute(sql2)
except:
    print('转账执行异常')
    conn.rollback()
else:
    print('转账成功')
    conn.commit()
'''

# 以上方法还是有漏洞的，如果对方账户不是在该表的，sql语句正确还是会执行，导致数据的丢失。如下例子可以解决这个问题
try:
    sql1 = 'update t3 set money=money-1 where name="tom"'
    r1 = cursor.execute(sql1)
    sql2 = 'update t3 set money=money+1 where name="Bob"'
    r2 = cursor.execute(sql2)
except:
    print('转账执行异常')
    conn.rollback()
else:
    print('转账没有异常')
    if r1 == 1 and r2 == 1:
        print('转账成功')
        conn.commit()
    else:
        print('对方账户有误')
        conn.rollback()

---

四、sql注入

import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='tomjoy',passwd='123456',db='db2')
cursor = conn.cursor(DictCursor)

try:
    sql = 'create table user(id int, name char(4),password char(6))'
    row = cursor.execute(sql)
    print(row)
except:
    print('表已创建')
    pass

# 空表才插入
row = cursor.execute('select * from user')
if not row:
    sql = 'insert into user values(%s,%s,%s)'
    row = cursor.executemany(sql,[(1,'tom','123'),(2,'BOb','456')])
    conn.commit()


# 用户登陆
usr = input('usr: ')
pwd = input('pwd: ')

# 自己拼接参数一定有sql注入，将连接的占位填充交给pymysql

# 以下写法是 有漏洞的
'''
sql = 'select * from user where name="%s" and password=%s' %(usr,pwd)
row = cursor.execute(sql)
if row:
    print('登陆成功')
else:
    print('登陆失败')
'''
# 分析：
# 当我们知道用户名时：
# 输入用户时：
# tom => select * from user where name="tom" and password=%s
# 如果我们输入tom" #  相当于把后面的一串语句注释了，所以只需要判断用户是否存在就可以登陆进去了，不需要判断密码。
# tom" # => select * from user where name="tom" #" and password=%s

# 不自定义用户名时：
# 输入 " or 1=1 # 相当于判断一个空用户或者1=1是否成立就可以登陆进去，用户名密码都不需要判断直接登陆进去了
# " or 1=1 # =》 select * from user where name="" or 1=1 #" and password=%s

# 正确的写法：
sql= 'select * from user where name=%s and password=%s'
row = cursor.execute(sql,(usr,pwd))
if row:
    print('登陆成功')
else:
    print('登陆失败')

---

五、索引

# 索引就是 键 - key

'''
1.键是添加给数据库表的 字段的
2.给表创建 键 后，该表不仅会形成 表结构、表数据，还有键的 B+结构图
3.键的结构图是需要维护的，在数据完成增、删、改操作时，只要影响到
    有键的字段，结构图都要维护一次，所以创建后一定会降低 增、删、改的效率
4.键可以极大地加快查询速度（开发需求中，几乎业务都和查有关系）
5.建立键的方式： 主键、外键、唯一键、index
'''

import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='tomjoy',passwd='123456',db='db2')
cursor = conn.cursor(DictCursor)

# 创建两张表
# sql = 'create table a1(id int primary key auto_increment,x int,y int)'
# cursor.execute(sql)
#
sq2 = 'create table a2(id int primary key auto_increment,x int,y int,index(x))'
cursor.execute(sq2)
#
# # 每个表插入5000条数据
# import random
# for i in range(1,5001):
#     x = i
#     y = random.randint(1,5000)
#     cursor.execute('insert into a3(x,y) values(%s,%s)',(x,y))
#     cursor.execute('insert into a4(x,y) values(%s,%s)',(x,y))
# conn.commit()


import time
# a1的x、a1的id,a2的x
start = time.time()
sql = 'select * from a1 where id=4975'
cursor.execute(sql)
end = time.time()
print(end-start)

start = time.time()
sql = 'select * from a1 where x=4975'
cursor.execute(sql)
end = time.time()
print(end-start)


start = time.time()
sql = 'select * from a2 where x=4975'
cursor.execute(sql)
end = time.time()
print(end-start)


# 使用 列表推导式 插入数据
# [cursor.execute('insert into a8(x,y) values(%s,%s)',(x,y)) for x,y in enumerate(range(1,5001)) ]
# conn.commit()