Python3读取yaml配置&&连接mysql

﹏ヽ暗。殇╰゛Y 2023-09-29 16:58 15阅读 0赞

#### 文章目录 ####

*  一、Python3读取yaml配置&&连接mysql
 *   *   *  前言：
     *  1.1 connect\_dasimadb.py
     *  1.2 dasima\_db.yaml
     *  1.3 执行结果

## 一、Python3读取yaml配置&&连接mysql ##

#### 前言： ####

*  使用**pymysql**模块连接和操作mysql
 *  使用**yaml**模块来加载yaml配置文件（ `pip install pyyaml` ）
 *  包含两个文件：`connect_dasimadb.py`和配置文件`dasima_db.yaml`
 *  可通过修改配置文件修改mysql连接信息

**代码如下所示**

### 1.1 connect\_dasimadb.py ###

#!/usr/bin/python3
    
    ## 编辑同目录下的dasima_db.yaml配置文件，可以修改连接的参数
    ## 启动命令：python3 connect_dasimadb.py
    
    import pymysql
    import yaml    ## pip3 install pyyaml
    
    with open("D:\git\Py_files\dasima_db.yaml") as ya:
        cfg = yaml.safe_load(ya)
    
    mysql_host = cfg["mysql"]["host"]
    mysql_user = cfg["mysql"]["user"]
    mysql_password = cfg["mysql"]["password"]
    mysql_port = cfg["mysql"]["port"]
    mysql_db = cfg["mysql"]["database"]
    mysql_charset = cfg["mysql"]["charset"]
    
    
    connection = pymysql.connect(host=mysql_host,
                    user=mysql_user,
                    password=mysql_password,
                    port=mysql_port,
                    db=mysql_db,
                    charset=mysql_charset)
    cur = connection.cursor()
    ## Create table
    try:
        create_sql = "create table sys (id int, name varchar(30), phone bigint);"
        cur.execute(create_sql)
    except Exception as e:
        print("Create table failed", e)
    else:
        print("Create table success;")
    
    ## insert data to table
    try:
        insert_sql = "insert into sys values(001, '杨俊杰', 13823239882);"
        cur.execute(insert_sql)
    except Exception as e:
        print("insert data into table sys failed", e)
    else:
        connection.commit()
        print("Insert data success!")

### 1.2 dasima\_db.yaml ###

*  下面不是一段真实的连接信息，可以修改为自己的服务器地址和账号密码等

mysql:
      host: "www.dasima.com"
      port: 3306
      user: "root"
      password: "xwphs123qwer"
      database: "xwphs"
      charset: "utf8"

### 1.3 执行结果 ###

![在这里插入图片描述][9cdd24061a6d4f289e324f3fdf0b1442.png]  
  
![在这里插入图片描述][adc6b85375a44cbe86e222a9d4e9bfff.png]

import pymysql.cursors
    
    # Connect to the database
    connection = pymysql.connect(host='localhost',
                                 user='user',
                                 password='passwd',
                                 database='db',
                                 cursorclass=pymysql.cursors.DictCursor)
    
    with connection:
        with connection.cursor() as cursor:
            # Create a new record
            sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
            cursor.execute(sql, ('webmaster@python.org', 'very-secret'))
    
        # connection is not autocommit by default. So you must commit to save
        # your changes.
        connection.commit()
    
        with connection.cursor() as cursor:
            # Read a single record
            sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
            cursor.execute(sql, ('webmaster@python.org',))
            result = cursor.fetchone()
            print(result)

[9cdd24061a6d4f289e324f3fdf0b1442.png]: https://img-blog.csdnimg.cn/9cdd24061a6d4f289e324f3fdf0b1442.png
[adc6b85375a44cbe86e222a9d4e9bfff.png]: https://img-blog.csdnimg.cn/adc6b85375a44cbe86e222a9d4e9bfff.png