python+pymysql：连接数据库

"""
1、pymysql安装导入：
　　1.1、在cmd命令行输入pip install pymysql或者pip3 install PyMySQL安装
　　1.2、在pycharm里安装:
　　　　1.2.1、点击最底部Terminal, 输入pip install pymysql或者pip3 install PyMySQL安装
　　　　1.2.2、点击最顶部File》Settings(快捷键Ctrl + Alt + S)》Project: PyProject》PythonInterpreter》点击 + 号》搜索输入pymysql》选择对应模块》点击InstallPackage安装模块
　　1.3、模块导入：import pymysql

2、输入命令安装如果失败：
　　2.1、检查一下pip是否安装或升级pip
　　2.2、检查是否有开启网络代理比如fiddler，如果有关闭fiddler代理
　　2.3、检查python环境是否正确

3、pymysql连接数据库和使用：
"""
# -*- coding: UTF-8 -*-
# 当前用户的登录名：铺先生技术研发中心
# 当前文件名：Mysql_家政.py
# 创建时间：2023-03-15 08:56:41
# 创建人：猪油仔

import pymysql

db = pymysql.connect(
    host='000.0.0.0',  # 数据库地址
    port=0000,  # 端口
    user='0000',  # 用户名/账号
    password='0000',  # 密码
    db='0000',  # 数据库名
    charset='utf8'  # 数据库编码
)
"""使用cursor()方法获取操作游标"""
cursor = db.cursor()
print("---------------------------------------------------------------------------------------------------------------")

"""查询数据库版本"""
sql_01 = "select version();"

"""查询所有表名称"""
sql_02 = "show tables;"

"""创建表"""
sql_03 = """
            CREATE TABLE if not exists test1_python(
            python_id     int(10)      NOT NULL     AUTO_INCREMENT COMMENT '注释：id',
            python_name   varchar(100) DEFAULT NULL                COMMENT '注释：姓名',
            python_phone  varchar(100) DEFAULT NULL                COMMENT '注释：手机',
            python_sex    varchar(100) DEFAULT NULL                COMMENT '注释：性别',
            python_age    varchar(100) DEFAULT NULL                COMMENT '注释：年龄',
            python_height varchar(100) DEFAULT NULL                COMMENT '注释：身高',
            python_weight varchar(100) DEFAULT NULL                COMMENT '注释：体重',
            python_native_place varchar(100) DEFAULT NULL          COMMENT '注释：体重',
            PRIMARY KEY (python_id)                                COMMENT '注释：主键约束python_id'
            )COMMENT '注释：python测试'
         """

"""添加列（默认添加最后一列）"""
sql_04 = """
            ALTER TABLE 
                test1_python 
            ADD COLUMN 
                python_native_place varchar(100) DEFAULT NULL COMMENT '籍贯'
         """

"""添加列（添加到指定位置）"""
sql_05 = """
            ALTER TABLE
                test1_python
            ADD COLUMN
                python_native_place varchar(100) DEFAULT NULL COMMENT '籍贯' AFTER python_sex
         """

"""添加列（添加到第一列）"""
sql_06 = """
            ALTER TABLE
                test1_python
            ADD COLUMN
                python_native_place varchar(100) DEFAULT NULL COMMENT '籍贯' AFTER
         """

"""添加/修改表注释"""
sql_07 = """
            ALTER TABLE 
                test1_python
            COMMENT 'python测试666'
         """

"""修改列的类型和长度、添加/修改列注释"""
sql_08 = """
            ALTER TABLE 
                test1_python
            MODIFY
                python_native_place varchar(100) COMMENT '籍贯'
         """

"""删除指定列"""
sql_09 = """
            ALTER TABLE 
                test1_python 
            DROP COLUMN 
                python_native_place
         """

"""指定插入数据01"""
sql_10 = """
            INSERT INTO 
                test1_python 
            SET 
                python_name = '猪油仔',
                python_phone = '19911111111',
                python_sex = '男',
                python_age = '22岁',
                python_height = '160CM',
                python_weight = '50KG'
         """

"""删除指定数据"""
sql_11 = """
            DELETE 
                A 
            FROM 
                test1_python A 
            WHERE 
                A.python_id = 5
         """

"""删除表的所有数据"""
sql_12 = """
            DELETE 
                A 
            FROM 
                test1_python A
         """

"""查询表数据：ORDER BY 字段名+DESC=降序=321————ORDER BY 字段名+ASC=升序=123—————ORDER BY RAND( )=随机排序"""
sql_13 = """
            SELECT 
                *  
            FROM 
                test1_python 
            WHERE 
                python_name='猪油仔' AND 
                python_phone='19911111111' 
            ORDER BY 
                RAND() 
            LIMIT 
                10000
        """

"""连表查询表数据：ORDER BY 字段名+DESC=降序=321————ORDER BY 字段名+ASC=升序=123—————ORDER BY RAND( )=随机排序"""
sql_14 = """
            SELECT
                A.python_id,
                A.python_name,
                B.python_id,
                B.python_name
            FROM 
                test1_python A
                INNER JOIN test2_python B ON A.python_id = B.python_id
            WHERE 
                A.python_name='猪油仔' AND 
                A.python_phone='19911111111' 
            ORDER BY 
                RAND() 
            LIMIT
                10000
        """

sql = sql_14
print("打印执行的mysql语句：", sql)
print("---------------------------------------------------------------------------------------------------------------")

"""使用execute方法执行SQL语句"""
cursor.execute(sql)

"""commit()提交事务，插入数据时必须要提交事务，否则执行语句后，数据库是不会有新增数据的"""
db.commit()

"""fetchone():获取一条数据、fetchall():获取所有数据"""
data = cursor.fetchall()

"""打印查询结果"""
print("打印执行mysql语句的结果：", data)

"""关闭游标"""
cursor.close()

"""关闭连接"""
db.close()