python操作mysql
-
pip install pymysql
import pymysql # 连接数据库的参数 connect = pymysql.connect( host='localhost', user='root', password='Cql123456', database='test1', charset='utf8' ) cursor = connect.cursor(cursor=pymysql.cursors.DictCursor) # 参数控制查询的返回值是字典类型 sql = 'select * from boy_to_girl' cursor.execute(sql) # 执行查询sql执行 # print(cursor.fetchone()) # {'id': 1, 'bid': 1, 'gid': 1}, 返回值是字典, 多次读取时需注意光标位置 # print(cursor.fetchall()) # 获取所有查询结果, 返回值是列表套字典, 多次读取时需注意光标位置 print(cursor.fetchmany(3)) # 参数控制获取多少条数据, 返回值是列表套字典, 多次读取时需注意光标位置 # [{'id': 1, 'bid': 1, 'gid': 1}, {'id': 2, 'bid': 1, 'gid': 3}, {'id': 3, 'bid': 1, 'gid': 7}] # 关闭句柄释放内存空间 cursor.close() connect.close()
pymysql中sql指令注入问题
-
产生原因:
- 没有对用户输入的指令做检验
- 后面的代码相当于注释
import pymysql connect = pymysql.connect( host='localhost', user='root', password='Cql123456', database='test1', charset='utf8' ) cursor = connect.cursor(cursor=pymysql.cursors.DictCursor) name = input('请输入用户名:') pwd = input('请输入用户密码:') sql = "select * from nick where name = '%s' and pwd = '%s'" % (name, pwd) print(sql) cursor.execute(sql) print(cursor.fetchall()) # 关闭句柄释放内存空间 cursor.close() connect.close() ''' 请输入用户名:tank'# 请输入用户密码:456 select * from nick where name = 'tank'#' and pwd = '456' [{'id': 1, 'name': 'tank', 'pwd': 'tank123'}] '''
-
解决方法: 将用户输入的指令和包含%占位符的sql查询语句一起传入cursor.execute方法中, 该方法会自动对用户输入的指令进行检测及相关的转义处理
import pymysql connect = pymysql.connect( host='localhost', user='root', password='Cql123456', database='test1', charset='utf8' ) cursor = connect.cursor(cursor=pymysql.cursors.DictCursor) name = input('请输入用户名:') pwd = input('请输入用户密码:') sql = "select * from nick where name = %s and pwd = %s" # 注意%s不加引号 print(sql) cursor.execute(sql, (name, pwd)) print(cursor.fetchall()) # 关闭句柄释放内存空间 cursor.close() connect.close() ''' 请输入用户名:tank 请输入用户密码:tank123 select * from nick where name = %s and pwd = %s [{'id': 1, 'name': 'tank', 'pwd': 'tank123'}] '''
其他操作---增, 改, 删
-
增加记录
import pymysql connect = pymysql.connect( host='localhost', user='root', password='Cql123456', database='test1', charset='utf8' ) cursor = connect.cursor(cursor=pymysql.cursors.DictCursor) # 一条记录的信息 # name = input('请输入用户名:') # pwd = input('请输入用户密码:') # 多条记录的信息 data = [ ('jason1', 'pwd1'), ('jason2', 'pwd2'), ('jason3', 'pwd3') ] sql = "insert into nick (name, pwd) values (%s, %s)" print(sql) # insert into nick (name, pwd) values (%s, %s) # cursor.execute(sql, (name, pwd)) # 增加一条记录的语句, execute cursor.executemany(sql, data) # 增加多条记录的语句, executemany connect.commit() # 交付结果至硬盘 # 关闭句柄释放内存空间 cursor.close() connect.close()
-
修改一条记录
import pymysql connect = pymysql.connect( host='localhost', user='root', password='Cql123456', database='test1', charset='utf8' ) cursor = connect.cursor(cursor=pymysql.cursors.DictCursor) sql = "update nick set name = %s, pwd = %s where id = 4" print(sql) # update nick set name = %s, pwd = %s where id = 4 cursor.execute(sql, ('dragon', 'cql123456')) connect.commit() # 交付结果至硬盘 # 关闭句柄释放内存空间 cursor.close() connect.close()
-
删除一条数据
import pymysql connect = pymysql.connect( host='localhost', user='root', password='Cql123456', database='test1', charset='utf8' ) cursor = connect.cursor(cursor=pymysql.cursors.DictCursor) sql = "delete from nick where name = %s" print(sql) # delete from nick where name = %s cursor.execute(sql, ('tank',)) connect.commit() # 交付结果至硬盘 # 关闭句柄释放内存空间 cursor.close() connect.close()
索引
- key包含两层意义和作用:
- 约束(规范数据库的结构完整性)---constrant
- 索引(辅助查询)
- 索引处于实现层面, 不会约束被索引的字段的行为---index
- 作用: 提高查询效率
- 类比: 字典中的目录
- 本质: 一个特殊的文件
- 底层原理: B+树
索引的种类
-
主键索引: 加速查找 + 不能重复 + 不能为空
-
唯一索引: 加速查找 + 不能重复
联合唯一索引
-
普通索引: 加速查找
联合索引
主键索引
# 新增主键索引方式一: 在表创建之后增加
alter table table_name
add primary key (id);
# 新增主键索引方式二: 在创建表时增加
create table table_name (
id int auto_increment,
primary key (id)
) charset utf8;
# 删除主键索引, 如果主键自增, 需先删除自增约束
alter table table_name
drop primary key;
唯一索引
select count(real_name) from test_primary_key;
+------------------+
| count(real_name) |
+------------------+
| 4000001 |
+------------------+
1 row in set (2.26 sec) # 未添加唯一索引前查询时间
# 添加唯一索引的方式一: 在表创建之后添加
alter table test_primary_key
add constraint uqe_real_name unique index (real_name);
select count(real_name) from test_primary_key;
+------------------+
| count(real_name) |
+------------------+
| 4000001 |
+------------------+
1 row in set (0.77 sec) # 添加唯一索引后的查询时间
# 添加唯一索引的方式二: 在创建表时添加
create table unique_index (
id int auto_increment primary key,
name varchar(32),
constraint uqe_name unique key (name)
) charset utf8;
# 此时unique key, unique index, unique三者同义
# 删除唯一索引, 使用关键字index
alter table test_primary_key
drop index uqe_real_name;
普通索引
# 添加普通索引的方式一: 在表创建之后添加
alter table test_primary_key
add index ix_real_name (real_name); # 声明索引名及被添加普通索引的字段
# 添加普通索引的方式二: 在表创建时添加
create table ordinary_index (
id int auto_increment primary key,
name varchar(32),
index ix_name (name)
) charset utf8;
# 删除普通索引
alter table test_primary_key
drop index ix_real_name;
索引的优缺点: 优点是加快了查询速度, 但是通过观察 *.ibd 文件可知加索引后会占用大量磁盘空间
索引不会命中的情况
- 索引并不是越多越好, 有些sql语句会使索引不命中
-
不能在where语句的条件中进行四则运算, 会降低sql的查询效率
select count(id) from test_primary_key where id*2 = 1000;
-
使用函数:
select * from t where reverse(email) = "zekai";
-
类型不一致: 如果字段类型是字符串类型, sql查询时where语句的条件判断也需要是字符串类型, 此外where语句的条件的范围大或者模糊也会降低搜索效率
select * from t where email = 999;
-
排序条件为添加了索引的字段, 则select查询的字段也必须是添加了索引的字段, 否则无法命中
select name from t order by email desc;
-
count(列)代替count(*)
-
组合(联合)索引最左前缀
# index (a, b, c) # where a = 2 ... # 命中 # where (b...) 逻辑连接词 (c...) # 未中
-
explain获取查询报告
explain select count(real_name) from test_primary_keyG ''' id: 1 select_type: SIMPLE # 索引的类型, all表示可能全表扫表 table: test_primary_key partitions: NULL # 分区 type: index # 索引类型 possible_keys: NULL # 可能用到的索引 key: ix_real_name # 确实用到的所有 key_len: 98 # 索引的长度 ref: NULL rows: 3992630 # 扫描长度 filtered: 100.00 Extra: Using index # 使用到了索引 1 row in set, 1 warning (0.00 sec) '''
-
索引覆盖:
select from test_primary_key where id = 2000;
慢查询日志
-
查看慢sql的相关变量:
show variables like "%slow%"
+---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | # 默认关闭记录慢sql查询日志, on为开启 | slow_query_log_file | G: | # 慢sql日志的记录位置 +---------------------------+-------+
-
查看能接受的最长查询时间变量:
show variables like "%long%"
+----------------------------------------------------------+-----------+ | Variable_name | Value | +----------------------------------------------------------+-----------+ | long_query_time | 10.000000 | | performance_schema_events_stages_history_long_size | 10000 | | performance_schema_events_statements_history_long_size | 10000 | | performance_schema_events_transactions_history_long_size | 10000 | | performance_schema_events_waits_history_long_size | 10000 | +----------------------------------------------------------+-----------+ # long_query_time 能接受的最长查询时间变量
-
配置慢sql日志变量:
set global 变量名 = 变量值
set global slow_query_log = on; set global slow_query_log_file = "G:/软件压缩包/mysql-5.7.28/slow_sql_log"; # window中文件路径使用 / 分隔 set global long_query_time = 1; select count(real_name) from test_primary_key; # (3.23 sec), 配置好后需先退出客户端重连再查询, 则会记录慢sql