Python 与数据库交互的封装(MySQL,SQLServer,SQLite)

Python与数据库交互原理

---

Python利用pymssql包和pymysql包可以实现与数据库的统一,并且Python可以利用游标来对数据库进行快速操作,本代码实现了SqlBase基类和几个SqlHelper类来对不同类型的数据库进行交互,因为游标的存在,三种数据库的操作在某种程度上是相通的

---

安装包

pip install pymssql
pip install pymysql
pip install sqlite3

SqlBase.py

__init__函数

    def __init__(self, cursor, conn):
        self._cursor = cursor
        self._conn = conn

cursor:由子类创建作为游标对象
conn:由子类创建作为连接对象
因为_cursor和_conn的存在,在子类中也可以调用,起到一个特殊性的拓展

common函数

    def close(self) -> None:
        '''
        关闭游标对象和连接对象
        :param:NULL
        :return:None
        '''
        self._cursor.close()
        self._conn.close()

    def execute(self, sqlstring: str) -> list:
        '''
        执行SQL语句
        :param sqlstring: Sql语句
        :return: 返回结果
        '''
        self._cursor.execute(sqlstring)
        data = self._cursor.fetchall()
        return data

select函数

    def selectbycolumns(self, tbname: str, *columns: str) -> list:
        '''
        通过列名进行Select查询
        :param tbname: 表名
        :param columns: 需要查询的列名
        :return: 查询结果
        '''
        col = str(columns).replace("[", "").replace("]", "").replace("'", "").replace("(", "").replace(")", "")
        sqlstring = f"select {col} from {tbname} "
        if len(columns) == 0: sqlstring = f"select *from {tbname}"
        self._cursor.execute(sqlstring)
        data = self._cursor.fetchall()
        return data

    def selectbyexpression(self, tbname: str, expression: str) -> list:
        '''
        通过where子句表达式进行Select查询
        :param tbname: 表名
        :param expression:where子句
        :return: 查询结果
        '''
        sqlstring = f"select *from {tbname} where {expression}"
        self._cursor.execute(sqlstring)
        data = self._cursor.fetchall()
        return data

insert函数

    def insertbyvalues(self, tbname: str, *values) -> None:
        '''
        通过全部字段值新增数据到表
        :param tbname: 表名
        :param values: 所有字段的值
        :return: None
        '''
        vls = str(values).replace("[", "").replace("]", "")
        sqlstring = f"insert into {tbname} values {vls}"
        print(sqlstring)
        self._cursor.execute(sqlstring)
        self._conn.commit()

    def insertbykeyvalues(self, tbname: str, **keyvalues) -> None:
        '''
        通过 字段名=值 的键值对新增记录
        :param tbname: 表名
        :param keyvalues: 字段名=值的字典
        :return: None
        '''
        keys = str(keyvalues.keys()).replace("dict_keys", "").replace("'", "").replace("[", "").replace("]", "")
        values = str(keyvalues.values()).replace("dict_keys", "").replace("[", "").replace("]", "")
        sqlstring = f"insert into {tbname} {keys} values {values}"
        self._cursor.execute(sqlstring)
        self._conn.commit()

delete函数

    def deletebykeyvalues(self, tbname: str, **keyvalues) -> None:
        '''
        通过 字段名=值 的方式查找到对于键值对并删除
        :param tbname: 表名
        :param keyvalues: 键值对
        :return: None
        '''
        keys = list(keyvalues.keys())
        values = list(keyvalues.values())
        pairs = []
        for i in range(len(keys)):
            pairs.append(f"{keys[i]}={values[i]}")
            pairs.append("and")  # 使用and连接词
        del pairs[len(pairs) - 1]  # 删除最后一个and连接词
        pairs = str(pairs).replace("[", "").replace("]", "").replace("'", "").replace(",", "")
        sqlstring = f"delete from {tbname} where {pairs}"
        self._cursor.execute(sqlstring)
        self._conn.commit()

    def deletebyexpression(self, tbname: str, expression: str) -> None:
        '''
        通过where表达式进行查询并删除
        :param tbname: 表名
        :param expression:表达式
        :return: None
        '''
        sqlstring = f"delete from {tbname} where {expression}"
        self._cursor.execute(sqlstring)
        self._conn.commit()

update函数

    def updatebykeyvalues(self, tbname: str, expression: str, **keyvalues) -> None:
        '''
        通过expression表达式找到数据后对 字段名=值 进行修改
        :param tbname: 表名
        :param expression:where表达式
        :param keyvalues: 修改的字段名=值对
        :return: None
        '''
        keys = list(keyvalues.keys())
        values = list(keyvalues.values())
        keypairs = []
        for i in range(len(keys)):
            temp = f"{keys[i]}=\"{(values[i])}\""
            keypairs.append(temp)
        keypairs = str(keypairs).replace("[", "").replace("]", "").replace("'", "")
        sqlstring = f"update {tbname} set {keypairs} where {expression}"
        self._cursor.execute(sqlstring)
        self._conn.commit()

create函数

    def createtable(self, tbname: str, *args: list) -> None:
        '''
        通过List创建新表格
        比如createtable("TB_TestTbale",["ID","nchar(10)"],["Password","nchar(20)","NOT NULL"])
        每个字段用一个list表示 顺序为 [字段名,类型名,*约束，*其他]
        :param tbname: 表格名称
        :param args: 参数
        :return:None
        '''
        data = []
        for i in range(len(args)):
            temp = str(args[i]).replace("[", "").replace("]", "").replace("'", "").replace(",", "")
            data.append(temp)
        data = str(data).replace("[", "(").replace("]", ")").replace("'", "")
        sqlstring = f"create table {tbname} {data}"
        self._cursor.execute(sqlstring)
        self._conn.commit()

SqlHelper.py

class MsSqlHelper(SqlBase):
    def __init__(self, ip: str, db: str, uid: str, pwd: str):
        conn = pymssql.connect(server=ip, user=uid, password=pwd, database=db)
        cursor = conn.cursor()
        SqlBase.__init__(self, cursor, conn)


class MySqlHelper(SqlBase):
    def __init__(self, host: str, user: str, passwd: str, db: str, port=3306, charset='utf8'):
        conn = pymysql.connect(host, user, passwd, port, db, charset)
        cursor = conn.cursor()
        SqlBase.__init__(self, cursor, conn)


class SqliteHelper(SqlBase):
    def __init__(self, db: str):
        conn = sqlite3.connect(db)
        cursor = conn.cursor()
        SqlBase.__init__(self, cursor, conn)