Python连接Mysql

素颜马尾好姑娘i 2022-10-05 01:55 143阅读 0赞

# 前言 #

最近玩python全栈项目，连接Mysql数据库

--------------------

# 代码实现 #

*  安装mysql 的connector模块  
    [https://dev.mysql.com/downloads/connector/python/][https_dev.mysql.com_downloads_connector_python]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70]  
    ![在这里插入图片描述][20210605184743583.png]  
    ![在这里插入图片描述][20210605184823331.png]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 1]
 *  一路默认  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 2]  
    安装完成上面的模块以后，直接在python编辑器里面，导入出现这种情况，需要我们重新设置一下，获取全局的依赖：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 3]  
    可以直接在设置里面下载mysql的这个模块：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 4]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 5]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 6]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 7]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 8]  
    ![在这里插入图片描述][20210605190042606.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 9]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 10]

ModuleNotFoundError: No module named 'mysql.connector'; 'mysql' is not a package

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 11]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 12]  
导入成功以后，还是出现了，类似的方法：

ModuleNotFoundError: No module named 'mysql'

只能重新启动试一试：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 13]  
重新启动以后，继续执行命令，还是报错，非常爽----O(∩\_∩)O哈哈~

(movie_project) D:\code\pytest>python mysq-testl.py
    12324234234
    Traceback (most recent call last):
      File "D:\code\pytest\mysq-testl.py", line 11, in <module>
        con = mysql.connector.connect(
      File "C:\Users\Administrator\.virtualenvs\movie_project-7781sJXo\lib\site-packages\mysql\connector\__init__.py", line 273, in connect
        return MySQLConnection(*args, **kwargs)
      File "C:\Users\Administrator\.virtualenvs\movie_project-7781sJXo\lib\site-packages\mysql\connector\connection.py", line 107, in __init__
        self.connect(**kwargs)
      File "C:\Users\Administrator\.virtualenvs\movie_project-7781sJXo\lib\site-packages\mysql\connector\abstracts.py", line 1000, in connect
        self.config(**kwargs)
      File "C:\Users\Administrator\.virtualenvs\movie_project-7781sJXo\lib\site-packages\mysql\connector\abstracts.py", line 615, in config
        self._add_default_conn_attrs()
      File "C:\Users\Administrator\.virtualenvs\movie_project-7781sJXo\lib\site-packages\mysql\connector\connection.py", line 117, in _add_default_conn_attrs
        if "64" in platform.architecture()[0]:
      File "c:\users\administrator\appdata\local\programs\python\python39\lib\platform.py", line 673, in architecture
        size = struct.calcsize('P')
    AttributeError: module 'struct' has no attribute 'calcsize'

只能先把之前安装的，卸载了试一试：  
![在这里插入图片描述][20210605191458809.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 14]

/* Navicat Premium Data Transfer Source Server : localhost_3306 Source Server Type : MySQL Source Server Version : 50721 Source Host : localhost:3306 Source Schema : test Target Server Type : MySQL Target Server Version : 50721 File Encoding : 65001 Date: 08/10/2018 12:37:19 */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for t_bonus
    -- ----------------------------
    DROP TABLE IF EXISTS `t_bonus`;
    CREATE TABLE `t_bonus` (
      `empno` int(4) NOT NULL,
      `job` varchar(20) DEFAULT NULL,
      `sal` decimal(10,2) DEFAULT NULL,
      `comm` decimal(10,2) DEFAULT NULL,
      PRIMARY KEY (`empno`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Table structure for t_dept
    -- ----------------------------
    DROP TABLE IF EXISTS `t_dept`;
    CREATE TABLE `t_dept` (
      `deptno` int(2) NOT NULL,
      `dname` varchar(20) DEFAULT NULL,
      `loc` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`deptno`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of t_dept
    -- ----------------------------
    BEGIN;
    INSERT INTO `t_dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
    INSERT INTO `t_dept` VALUES (20, 'RESEARCH', 'DALLAS');
    INSERT INTO `t_dept` VALUES (30, 'SALES', 'CHICAGO');
    INSERT INTO `t_dept` VALUES (40, 'OPERATIONS', 'BOSTON');
    COMMIT;
    
    -- ----------------------------
    -- Table structure for t_emp
    -- ----------------------------
    DROP TABLE IF EXISTS `t_emp`;
    CREATE TABLE `t_emp` (
      `empno` int(4) NOT NULL,
      `ename` varchar(20) DEFAULT NULL,
      `job` varchar(20) DEFAULT NULL,
      `mgr` int(4) DEFAULT NULL,
      `hiredate` date DEFAULT NULL,
      `sal` decimal(10,2) DEFAULT NULL,
      `comm` decimal(10,2) DEFAULT NULL,
      `deptno` int(2) DEFAULT NULL,
      PRIMARY KEY (`empno`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of t_emp
    -- ----------------------------
    BEGIN;
    INSERT INTO `t_emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.00, NULL, 20);
    INSERT INTO `t_emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30);
    INSERT INTO `t_emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30);
    INSERT INTO `t_emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.00, NULL, 20);
    INSERT INTO `t_emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250.00, 1400.00, 30);
    INSERT INTO `t_emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.00, NULL, 30);
    INSERT INTO `t_emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.00, NULL, 10);
    INSERT INTO `t_emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09 00:00:00', 3000.00, NULL, 20);
    INSERT INTO `t_emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000.00, NULL, 10);
    INSERT INTO `t_emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30);
    INSERT INTO `t_emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12 00:00:00', 1100.00, NULL, 20);
    INSERT INTO `t_emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.00, NULL, 30);
    INSERT INTO `t_emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000.00, NULL, 20);
    INSERT INTO `t_emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300.00, NULL, 10);
    COMMIT;
    
    -- ----------------------------
    -- Table structure for t_salgrade
    -- ----------------------------
    DROP TABLE IF EXISTS `t_salgrade`;
    CREATE TABLE `t_salgrade` (
      `grade` int(11) NOT NULL,
      `losal` decimal(10,2) DEFAULT NULL,
      `hisal` decimal(10,2) DEFAULT NULL,
      PRIMARY KEY (`grade`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of t_salgrade
    -- ----------------------------
    BEGIN;
    INSERT INTO `t_salgrade` VALUES (1, 700.00, 1200.00);
    INSERT INTO `t_salgrade` VALUES (2, 1201.00, 1400.00);
    INSERT INTO `t_salgrade` VALUES (3, 1401.00, 2000.00);
    INSERT INTO `t_salgrade` VALUES (4, 2001.00, 3000.00);
    INSERT INTO `t_salgrade` VALUES (5, 3001.00, 9999.00);
    COMMIT;
    
    SET FOREIGN_KEY_CHECKS = 1;

### 执行SQL语句 ###

通过游标执行SQL语句：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 15]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 16]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 17]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 18]  
删除模块，然后再运行：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 19]

#!/usr/bin/env python
    # _*_ coding: utf-8 _*_
    # @Time : 2021/6/5 18:51
    # @Author : zhaocunwei
    # @Version：V 0.1
    # @File : mysq-testl.py
    # @desc :
    
    import mysql.connector
    
    con = mysql.connector.connect(
        host="localhost", port="3306",
        user="root", password="root",
        database="demo"
    )
    
    con.close()

![在这里插入图片描述][20210605194905140.png]

# SQL注入攻击案例 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 20]

[https_dev.mysql.com_downloads_connector_python]: https://dev.mysql.com/downloads/connector/python/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70]: /images/20221005/42273ad2b093453c87c99b8c2d358cf0.png
[20210605184743583.png]: /images/20221005/915661be7dc845c9ac3bc9dc6c32c7e8.png
[20210605184823331.png]: /images/20221005/7d766e620ab14f2fbd6db80757a0a712.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 1]: /images/20221005/17489c73243a43148b5baeb6030da583.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 2]: /images/20221005/43364b24ff424e14b0b8c55a20054292.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 3]: /images/20221005/6c1429d62ea44ade82b3ec02ffcf0764.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 4]: /images/20221005/de6f5c2b370240eaae9f4f21b24930c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 5]: /images/20221005/2cbe1e4e5afd4da193ca4d6cff48d15c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 6]: /images/20221005/6445389a1de84e09b9d3ea7d2aff5606.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 7]: /images/20221005/7c2e38142776419aa47d6aeae87c1fd8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 8]: /images/20221005/125203fa96d44aaa8e89d0646a820d6c.png
[20210605190042606.png]: /images/20221005/b27e9261c9324a3e9fa73729d8065a07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 9]: /images/20221005/2bee58a2dacc4d3cb7aafb73a313deae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 10]: /images/20221005/9c3d343034914cf384ad87e7e1d7dcd4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 11]: /images/20221005/7e66d49ede1c4a4c9ca0171c6577c704.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 12]: /images/20221005/196b184412ec49c7aa70ed4ac0d8d0a5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 13]: /images/20221005/570d7a8c546c4b10b1ca84a93fb300b5.png
[20210605191458809.png]: /images/20221005/92a6a506a8ae454d8618ca9d2a9dbf1d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 14]: /images/20221005/6ab7a389a14942d6ae152bfb01ffe2e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 15]: /images/20221005/053778b61548409581b8693585097e03.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 16]: /images/20221005/d7074cb8ac3b4c4fac92f91305152acd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 17]: /images/20221005/53dce42900c6473490abae32e11ea659.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 18]: /images/20221005/861be5c08e7a45079b8f11b77ca11f12.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 19]: /images/20221005/08b873e76bb646e591acc1df2ff0b521.png
[20210605194905140.png]: /images/20221005/8d99df58124445459dedbc83a7eb2ac6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyMzcwOTEz_size_16_color_FFFFFF_t_70 20]: /images/20221005/e4281509aa8b41ac94c6226a10aa6884.png