一、操作数据库

封装python连接mysql的增删改查操作，可直接调用即可

import pymysql

"""
pip install PyMySQL==0.9.3
"""


class DbConnect():
    def __init__(self, db_cof, database=""):
        self.db_cof = db_cof
        # 打开数据库连接
        self.db = pymysql.connect(database=database,
                                  cursorclass=pymysql.cursors.DictCursor,
                                  **db_cof)
        # 使用cursor()方法获取操作游标
        self.cursor = self.db.cursor()

    def select(self, sql):
        # SQL 查询语句
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        return results

    def execute(self, sql):
        # SQL 增、删、改语句
        try:
            # 执行SQL语句
            self.cursor.execute(sql)
            # 提交修改
            self.db.commit()
        except:
            # 发生错误时回滚
            self.db.rollback()

    def close(self):
        # 关闭数据库连接
        self.db.close()

二、使用变量的sql

• SQL使用单个变量：

  select_sql = "select * from aio_data_nodes where sys_dn_ipaddr='%s';"%ip
  result = db.select(select_sql)

代码如下（示例）：

from common.connet_mysql import DbConnect
#获取配置文件信息
test_file = os.path.join(Conf.get_config_path(),"conf.yml")
print(test_file)
MySqlDB = YamlReader(test_file).data_all()
print(MySqlDB)

@pytest.fixture(scope="function",params=MySqlDB)
def get_storage_ids_fixture():
        """
        描述：
        查询数据库

        """
        dbinfo = {
            "host": MySqlDB[0]["MySqlDB"]["host"],
            "user": MySqlDB[0]["MySqlDB"]["user"],
            "password": MySqlDB[0]["MySqlDB"]["mysql_psw"],
            "port": MySqlDB[0]["MySqlDB"]["port"]
        }
        # print("获取前置操作")
        db = DbConnect(db_cof=dbinfo, database="aio")
        ip = MySqlDB[0]['Service']['worker']['ipaddr']
        select_sql = "select * from aio_data_nodes where sys_dn_ipaddr='%s';"%ip
        result = db.select(select_sql)
        print(result)
        id = result[0]["id"]
        print("查询id,返回结果：", id)
        return id

• SQL使用多个变量

   select_sql = "select * from aio_data_nodes where sys_dn_ipaddr='%s' and sys_dn_os_port='%s' ;"
   result = db.select(select_sql,(ip,port))

from common.connet_mysql import DbConnect
#获取配置文件信息
test_file = os.path.join(Conf.get_config_path(),"conf.yml")
print(test_file)
MySqlDB = YamlReader(test_file).data_all()
print(MySqlDB)

@pytest.fixture(scope="function",params=MySqlDB)
def get_storage_ids_fixture():
        """
        描述：
        查询数据库

        """
        dbinfo = {
            "host": MySqlDB[0]["MySqlDB"]["host"],
            "user": MySqlDB[0]["MySqlDB"]["user"],
            "password": MySqlDB[0]["MySqlDB"]["mysql_psw"],
            "port": MySqlDB[0]["MySqlDB"]["port"]
        }
        # print("获取前置操作")
        db = DbConnect(db_cof=dbinfo, database="aio")
        ip = MySqlDB[0]['Service']['worker']['ipaddr']
        port = MySqlDB[0]['Service']['worker']['port']
        select_sql = "select * from aio_data_nodes where sys_dn_ipaddr='%s' and sys_dn_os_port='%s' ;"
        result = db.select(select_sql,(ip,port))
        print(result)
        id = result[0]["id"]
        print("查询id,返回结果：", id)
        return id

conf.yml配置文件

#要使用的信息
Service:
  worker:
    ipaddr: 192.168.1.0
    port: 22
    username: root
    passwd: a