使用python执行sql语句和外键解析

一、下载并导入pymysql

pip install pymysql && import pymysql

   db=pymysql.connect(host='192.168.253.10',user='root',password='1',db='mysql',port=3306)   #如果报错host大概率因为没设置允许第三方登陆
cur=db.cursor()
cur.execute('show tables;')             #注意execute书写
db.commit()                                  #提交才生效
result=cur.fetchall()                   #取数据,fetchone（n）获取下表对应的数据
print(result)

实验：用python执行sql语句测试时间

1)

import pymysql
db=pymysql.connect(host='192.168.253.10',user='root',password='1',db='zzz',port=3306)
cur=db.cursor()
for i in range(1000):                         
    print(i)                 
    cur.execute("insert into ss values(0,'name-%s')"%i)       #必须用双引号
db.commit()                  #必须提交事务，否则无效，并且置顶格，将所有循坏写入的步骤当作一个事务提交。
result=cur.fetchall()
print(result)

2) python执行完毕后，去linux主机登陆查看是否插入成功。

MariaDB [zzz]> select count(*) from ss;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+

或者 select * from ss  limit  10;                 #注意分页查看，因为插入数据多

3) 查看profiling的状态，,如果是off改成ON

show variables  status like  '%profiling%' ;

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |     
| profiling              | off    |     
| profiling_history_size | 15    |

临时性更改：set profiling =1;                  #重启等会重置

永久性更改，写进/etc/my.cnf.d/server.cnf 的server模块下。

4）执行命令查看profiles状态的duration列，然后添加索引后执行相同命令再来查看，对比前后耗时，可以得知索引的作用。

MariaDB [zzz]> show profiles;
+----------+------------+----------------------------------------+
| Query_ID | Duration   | Query                                  |
+----------+------------+----------------------------------------+
|        1 | 0.00319622 | show variables like '%profiling%'      |
|        5 | 0.00165140 | select * from ss where name='name-123' |     此步骤时间同8作比较
|        6 | 0.00026767 | show profiling                         |
|        7 | 0.02831208 | create index n_i on ss(name)           |      #创建完索引后
|        8 | 0.00090224 | select * from ss where name='name-123'　　　此步骤时间同5作比较发现快了几十倍，插入量越多效果越明显

至此，python执行sql语句实验完成。

二、外键

• 如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）

• 对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并报错

#添加外键

alter table 子表名 add  constraint  外键名  foreign key (关键词)  references  夫表名（对应关键词）；  

#删除外键

alter table students drop foreign key 外键名字;

在学生表上创建外键指向班级表（表见上一博客）

MariaDB [zzz]> alter table students add constraint fk foreign key(cls_id) references classes(id); 
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`zzz`.`#sql-acc_13`, CONSTRAINT `fk` FOREIGN KEY (`cls_id`) REFERENCES `classes` (`id`))

此报错是因为外键的约束作用，学生表的id有3、4、5但是班级表的id只有1，2；解决此报错要么学生表删除id为{3，4，5}的数据，要么增加班级表id为3，4，5的数据（总之使两个表的id对应上）

我们这里选择删除学生表的3，4，5数据
MariaDB [zzz]> delete from students where id >2;
Query OK, 15 rows affected (0.003 sec)

再执行：

alter table students add constraint fk foreign key(cls_id) references classes(id) ;   

show create table  students（子表名）；     #查看外键是否添加成功

 同理，只要两个表之间添加了外键约束，插入、修改删除、数据都会受彼此约束。

 

例如，由于父表id只有1和2，我们现在往子表students里面添加数据：

MariaDB [zzz]> insert into students values(0,'小明',18,180.00,2,3,0);       #插入id=3报错
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`zzz`.`students`, CONSTRAINT `fk` FOREIGN KEY (`cls_id`) REFERENCES `classes` (`id`))

解决方法为，先添加父表的id=3的数据。