python操作数据库之mysql--pymysql

序言

简单说几句，在python中操作mysql数据库有好多种选择，本文选择pymysql来介绍。

• 安装
  pip install PyMySQL

代码示例

class MySql:

    def sqlcon(self, query, state='all'):

        # 创建一个数据库连接
        cnn = pymysql.connect(**db_config)

        # 游标cursor
        cursor = cnn.cursor()

        cursor.execute(query)

        # 获取结果
        if state == '1':
            res = cursor.fetchone()  # tuple
        else:
            res = cursor.fetchall()  # tuple of tuple

        # 关闭游标
        cursor.close()

        # 关闭数据库连接
        cnn.close()

        return res


if __name__ == '__main__':
    query_sql = 'select * from tb_projects'
    sql = MySql()
    a = sql.sqlcon(query_sql)
    print(a)

# db_config的话我是使用yaml格式文件存储在专门的目录下，只需要保证格式为如下形式即可（密码需要为字符串形式）
db_config = {'host': '数据库所在服务器IP', 'user': 'root', 'password': '12345678', 'port': 端口号, 'database': 'learn_django', 'charset': 'utf8'}

# 附上操作yml格式文件的代码
from ruamel.yaml import YAML
import os.path
from pathlib import Path

ProjectPath = Path(os.path.realpath(__file__)).parent.parent


class WithYaml:

    def __init__(self, relative_path):
        self.file_name = os.path.join(ProjectPath, relative_path)
        self.yaml = YAML(typ="safe")

    def get_yaml(self):
        with open(self.file_name, 'r', encoding='utf-8') as f:
            content = self.yaml.load(f)
            return content

    def set_yaml(self, content):
        with open(self.file_name, 'w') as file:
            self.yaml.dump(content, file)


if __name__ == '__main__':
    a = WithYaml('data/create_project_data.yml')
    text = a.get_yaml()
    print(text)

db_conf:
  'host': 127.0.0.1
  'user': root
  'password': "12345678"
  'port': 13316
  'database': learn_django
  'charset': 'utf8'

# 调用方法获取db_config 

from common.with_yml import WithYaml


wy = WithYaml('data/db_conf.yml')

db_config = wy.get_yaml().get('db_conf')