使用Python操作MySQL数据库
一、安装PyMySQL的安装
pip install pymysql
二、连接数据库步骤
- pymysql连接数据库的必要参数:主机、端口、用户名、密码、数据库
- 注:pymysql不能提供创建数据库的服务,数据库要提前创建
import pymysql
# 1)建立数据库连接对象 conn
conn = pymysql.connect(user='root', passwd='root', database='db')
# conn = pymysql.connect(user='root', passwd='root', database='db', autocommit=True)
# 2)通过 conn 创建操作sql的 游标对象
# 注:游标不设置参数,查询的结果就是数据元组,数据没有标识性
# 设置pymysql.cursors.DictCursor,查询的结果是字典,key是表的字段
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 3)编写sql交给 cursor 执行
sql='create, select, insert, update, delete.....'
# 4)如果是查询,通过 cursor对象 获取结果
cursor.execute(sql)
# 5)操作完毕,端口操作与连接断开连接
cursor.close()
conn.close()
三、创建表
import pymysql
# 1.建立数据连接对象 conn
conn = pymysql.connect(user='root', password='root', database='db1')
# 2 通过conn创建sql的游标对象
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 3.编写sql语句
sql = 'create table test(id int, x int, y int)'
# 4.执行sql语句
cursor.execute(sql)
# 5.关闭
cursor.close()
conn.close()
四、插入数据
import pymysql
# 1.建立数据连接对象 conn
conn = pymysql.connect(user='root', password='root', database='db1')
# 2 通过conn创建sql的游标对象
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 3.编写sql语句
# 增加一条数据
# sql= 'insert into test1 values(1, 11,22)'
# 通过参数插入
sql = 'insert into test1 values(%s,%s,%s)'
# 4.执行sql语句
# 增加一条数据
# row = cursor.execute(sql, (1, 2, 100))
# 增加多条数据
row = cursor.executemany(sql, [(2, 22, 22), (3, 33, 22)])
# 5.提交sql
## 在创建conn对象时,不设置autocommit,默认开启事务,增删改操作不会直接映射到数据库中,
# 需要执行 conn.commit() 动作
conn.commit()
if row:
print("插入成功")
# 5.关闭
cursor.close()
conn.close()
五、删除数据
import pymysql
"""
@author RansySun
@create 2019-09-27-11:45
"""
conn = pymysql.connect(user='root', password='root', database='db1')
curson = conn.cursor(pymysql.cursors.DictCursor)
sql = 'delete from test1 where id=5'
row = curson.execute(sql)
conn.commit()
if row:
print("删除成功")
cursor.close()
conn.close()
六、修改数据
import pymysql
"""
@author RansySun
@create 2019-09-27-11:48
"""
# 创建连接对象
conn = pymysql.connect(user='root', password='root', database='db1')
# 创建游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 编写sql语句
sql = 'update test1 set x=%s where id=2'
# 执行sql语句
row = cursor.execute(sql, 10000)
# 提交sql语句
conn.commit()
if row:
print('修改成功')
cursor.close()
conn.close()
七、查询数据
Python查询Mysql使用 fetchone() 方法获取单条数据,使用
-
fetchall()
:方法获取多条数据。 -
fetchone()
: 该方法获取下一个查询结果集。结果集是一个对象 -
fetchall()
: 接收全部的返回结果行. -
rowcount()
: 这是一个只读属性,并返回执行execute()方法后影响的行数。
import pymysql
"""
@author RansySun
@create 2019-09-27-11:48
"""
# 1.建立数据连接对象 conn
conn = pymysql.connect(user='root', password='root', database='db1')
# 2 通过conn创建sql的游标对象
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 3.编写sql语句 查询语句
sql = 'select * from test1'
# 4.执行sql语句
try:
row = cursor.execute(sql)
print('row', row)
# 1获取全部记录
# res = cursor.fetchall()
# for row in res:
# print(row)
# 2获取一条记录
res = cursor.fetchone()
print(res)
# 3.获取指定条数
res = cursor.fetchmany(3)
print(res)
except:
conn.rollback()
# 5.关闭
cursor.close()
conn.close()
八、游标操作
import pymysql
"""
@author RansySun
@create 2019-09-27-12:07
"""
# 1.建立数据连接对象 conn
conn = pymysql.connect(user='root', password='root', database='db1')
# 2 通过conn创建sql的游标对象
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 3.编写sql语句
sql = 'select * from test1'
# 4.如果是查询,通过 cursor对象 获取结果
try:
cursor.execute(sql)
# absolute绝对偏移,游标重置,从头(0)开始偏移
# cursor.scroll(3, 'absolute')
# r2 = cursor.fetchone(5)
# print(r2)
# relative相对偏移,游标在当前位置进行左右偏移
cursor.scroll(2, 'relative')
res = cursor.fetchmany(1)
print(res)
except:
conn.rollback()
cursor.close()
conn.close()
九、pymysql事务
import pymysql
"""
@author RansySun
@create 2019-09-27-12:15
"""
# 1.建立数据连接对象 conn
conn = pymysql.connect(user='root', password='root', database='db1')
# 2 通过conn创建sql的游标对象
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 3.编写sql语句
crete_sql = 'create table t3(id int, name char(4), money int)'
try:
row = cursor.execute(crete_sql)
print(row)
except:
print('表已创建')
pass
# 空表才插入
# 空表才插入
row = cursor.execute('select * from t3')
if not row:
inert_sql = 'insert into t3 values(%s,%s,%s)'
row = cursor.executemany(inert_sql, [(1, 'tom', 10), (2, 'Bob', 10)])
conn.commit()
"""
try:
sql1 = 'update t3 set money=money-1 where name="tom"'
cursor.execute(sql1)
sql2 = 'update t3 set money=money 1 where name="ob"'
cursor.execute(sql2)
except:
print('转账执行异常')
conn.rollback()
else:
print('转账成功')
conn.commit()
"""
try:
sql1 = 'update t3 set money=money-1 where name="tom"'
r1 = cursor.execute(sql1)
sql2 = 'update t3 set money=money 1 where name="laowang"' # 转入的人不存在
r2 = cursor.execute(sql2)
except:
print('转账执行异常')
conn.rollback()
else:
if r1 and r2:
print('转账成功')
conn.commit()
else:
print("转账失败")
conn.rollback()
十、sql防注入
import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='root', passwd='root', db='oldboy')
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', 'abc')])
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('登录失败')
"""
sql = 'select * from user where name=%s and password=%s'
row = cursor.execute(sql, (usr, pwd))
if row:
print('登录成功')
else:
print('登录失败')
# 知道用户名时
# 输入用户时:
# tom => select * from user where name="tom" and password="%s"
# tom" # => select * from user where name="tom" #" and password="%s"
# 不自定义用户名时
# " or 1=1 # => select * from user where name="" or 1=1 #" and password="%s"
十一、索引
索引就是 键 - key
1)键 是添加给数据库表的 字段
2)给表创建 键 后,该表不仅会形参 表结构、表数据,还有 键的B 结构图
3)键的结构图是需要维护的,在数据完成增、删、改操作时,只要影响到有键的字段,结构图都要维护一次, 所以创建键后一定会降低 增、删、改 的效率
4)键可以极大的加快查询速度(开发需求中,几乎业务都和查有关系)
5)建立键的方式:主键、外键、唯一键、index
import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='root', passwd='root', db='db1')
cursor = conn.cursor(DictCursor)
# 创建两张表
sql1 = """create table a1(
id int primary key auto_increment,
x int,
y int
)"""
cursor.execute(sql1)
sql2 = """create table a2(
id int primary key auto_increment,
x int,
y int,
index(x)
)"""
cursor.execute(sql2)
# 每个表插入5000条数据
import random
for i in range(1, 5001):
x = i
y = random.randint(1, 5000)
cursor.execute('insert into a1(x, y) values(%s, %s)', (x, y))
cursor.execute('insert into a2(x, y) values(%s, %s)', (x, y))
conn.commit()
import time
# a1的x、a1的id、a2的x
# a1的id时间
b_time = time.time()
sql = 'select * from a1 where id=4975'
cursor.execute(sql)
e_time = time.time()
print(e_time - b_time)
# a1 的x时间
b_time = time.time()
sql = 'select * from a1 where x=4975'
cursor.execute(sql)
e_time = time.time()
print(e_time - b_time)
# a2时间
b_time = time.time()
sql = 'select * from a2 where x=4975'
cursor.execute(sql)
e_time = time.time()
print(e_time - b_time)