MySQL笔记（六）使用python封装

新建配置文件

[db]
db_port = 3306
db_user = root
db_host = 127.0.0.1
db_pass = 123456
db_name = zpcs
db_charset = utf8

[server]
ip = 127.0.0.1

import pymysql
import configparser


class ConnMysql(object):
    def __init__(self):
        config = configparser.ConfigParser()  # 读取文件配置新信息
        config.read(r"C:\Users\admin\AppData\Local\Temp\备课04.py\db_configure.ini")  # 读取文本路径
        host = config.get("db", "db_host")
        user = config.get("db", "db_user")
        pwd = config.get("db", "db_pass")
        name = config.get("db", "db_name")
        char = config.get("db", "db_charset")
        port = eval(config.get("db", "db_port"))
        self.db = pymysql.connect(host=host, user=user, port=port, password=pwd, database=name, charset=char)
        print("数据库连接")

    def db_select(self, sql):
        try:
            cs1 = self.db.cursor()
            count = cs1.execute(sql)
            print("查询到%d条数据" % count)
            for i in range(count):
                result = cs1.fetchone()
                print(result)
        except:
            print("出现异常")

    def db_close(self):
        try:
            self.db.close()

            print("关闭数据库")
        except:
            print("数据库关闭错误")


if __name__ == '__main__':
    a = ConnMysql()
    sql = "select * from students;"
    a.db_select(sql)
    a.db_close()

二次封装

新建config_01.py文件,作为模块进行导入，第一次封装

conn = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "mysql",
    "database": "school",
    "charset": "utf8"
}

第二次封装

import pymysql
from configure_01 import conn


class Database(object):
    def __init__(self,
                 host=conn["host"],
                 port=conn["port"],
                 user=conn["user"],
                 password=conn["password"],
                 database=conn["database"],
                 charset=conn["charset"]
                 ):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.charset = charset

    def getConnection(self):
        if not self.database:
            raise RuntimeError("配置信息有问题")
        try:
            self.conn = pymysql.connect(host=self.host,
                                        port=self.port,
                                        user=self.user,
                                        password=self.password,
                                        database=self.database,
                                        charset=self.charset)
        except:
            raise RuntimeError("初始化数据哭失败")

        self.cursor = self.conn.cursor()
        return self.cursor

    # 增删改
    def execute(self, sql, params=None):
        self.getConnection()
        self.cursor.execute(sql, params)
        self.conn.commit()
        print("提交成功")

    # 查询一个
    def select_sql (self, sql, params=None):
        self.getConnection()
        self.cursor.execute(sql, params)
        data = self.cursor.fetchone()
        return data

    # 查询多个
    def select_sql (self, sql, params=None):
        self.getConnection()
        self.cursor.execute(sql, params)
        datas = self.cursor.fetchall()
        return datas

    def close(self):
        try:
            if self.cursor:
                self.cursor.close()
        except:
            print("关闭游标失败")
        finally:
            try:
                if self.conn:
                    self.conn.close()
            except:
                print("关闭数据库异常")
# if __name__ == '__main__':
#     db = Database()
#     cursor = db.getConnection()
#     num = input("请输入学号：")
#     name = input("请输入姓名")
#     sql = "insert into students(studentno, name) VALUES(%s,%s)"
#     db.execute(sql, [num, name])
#     db.close()

新建py调用第二次封装的模块

from Databases_mysql_01 import Database


db = Database()
cursor = db.getConnection()
num = input("请输入学号：")
name = input("请输入姓名")
sql = "insert into students(studentno, name) VALUES(%s,%s)"
db.execute(sql, [num, name])

# 查询一条数据
# sql = "select * from students WHERE studentno = %s"
# data = db.select_sql(sql,  [2])
# print(data)

db.close()