Python3读取yaml配置&&连接mysql

一、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 执行结果

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)