python MySQLdb数据库的增删改查

 一、详细步骤

1、定义数据库参数：数据库IP、用户名、密码、端口、编码格式以及数据库操作句柄；

class ConnectSQL:
    def __init__(self, host, user, password, port, charset="utf8"):
        self.host = host
        self.user = user
        self.password = password
        self.port = port
        self.charset = charset
        self.cursor = None

2、连接数据库：输入指定数据库名称，建立连接并返回数据库句柄；

    def connect(self, database):
        """
        Connect to the DataBase
        :param database:Database name
        :return:Database Handle
        """
        mysql = MySQLdb.connect(host=self.host,
                                user=self.user,
                                password=self.password,
                                port=self.port,
                                database=database,
                                charset=self.charset)
        self.cursor = mysql.cursor()
        return self.cursor

3、新增数据：输入数据表名，以元组形式输入表头和所需新增数据，然后将数据插入到数据表中；

    def add(self, table, keys: tuple, values: tuple):
        """
        Add data and insert it into the database
        :param table: table name
        :param keys:table header
        :param values:table data
        :return:
        """
        try:
            for _ in values:
                insert_sql = f"INSERT INTO {table}({','.join(_ for _ in keys)}) VALUES{_};"
                print(insert_sql)
                self.cursor.execute(insert_sql)
                return True
        except MySQLdb.IntegrityError:
            print('Insert fail, this message is exist')
            return False

4、查找数据表：根据数据表名和判断条件，查询数据表中相关数据；

    def search(self, table, sql_str: str):
        """
        Query data in the table
        :param table: table name
        :param sql_str: Database query conditions
        :return:
        """
        select_sql = F"SELECT * FROM {table} WHERE {sql_str};"
        print(select_sql)
        try:
            count = self.cursor.execute(select_sql)
            print(f'Found sql messages: %d' % count)
            if count != 0:
                for i in self.cursor.fetchall():
                    print(i)
            else:
                print("This message isn't exist")
            return count
        except MySQLdb.Error:
            print("Failed to query the data table. Procedure")
            return False

5、 修改数据表数据：输入数据表名，以元组形式输入表头和所需新增数据，然后将数据替换到数据表中；

    def edit(self, table, keys: tuple, values: tuple):
        """
        Modify or replace data in a data table
        :param table: table name
        :param keys: table header
        :param values: table data
        :return:
        """
        try:
            for _ in values:
                edict_sql = f"REPLACE INTO {table}({','.join(_ for _ in keys)}) VALUES{_};"
                print(edict_sql)
                self.cursor.execute(edict_sql)
            return True
        except MySQLdb.Error:
            print("Failed to modify the data table. Procedure")
            return False

6、 删除数据表数据：根据数据表和相关判断条件，删除数据表中指定数据；

    def delete(self, table, sql_str: str):
        """
        Delete data from the table
        :param table: table name
        :param sql_str: Database query conditions
        :return:
        """
        delete_sql = f"DELETE FROM {table} WHERE {sql_str};"
        print(delete_sql)
        try:
            self.cursor.execute(delete_sql)
            return True
        except MySQLdb.Error:
            return False

7、主函数

if __name__ == '__main__':
    hold_keys = ("id", "username", "age")
    hold_values = ((1, "zhangsan", 21), (2, "lisi", 23))
    hold = ConnectSQL(host=localhost, user="root", password="123456", port=3306)
    hold.connect('user')
    hold.add('tb_user', hold_keys, hold_values)
    hold.edit('tb_user', hold_keys, hold_values)
    hold.delete('tb_user', "id = 1")
    hold.search('tb_user', "id = 1")

二、汇总 

import MySQLdb


class ConnectSQL:
    def __init__(self, host, user, password, port, charset="utf8"):
        self.host = host
        self.user = user
        self.password = password
        self.port = port
        self.charset = charset
        self.cursor = None

    def connect(self, database):
        """
        Connect to the DataBase
        :param database:Database name
        :return:Database Handle
        """
        mysql = MySQLdb.connect(host=self.host,
                                user=self.user,
                                password=self.password,
                                port=self.port,
                                database=database,
                                charset=self.charset)
        self.cursor = mysql.cursor()
        return self.cursor

    def add(self, table, keys: tuple, values: tuple):
        """
        Add data and insert it into the database
        :param table: table name
        :param keys:table header
        :param values:table data
        :return:
        """
        try:
            for _ in values:
                insert_sql = f"INSERT INTO {table}({','.join(_ for _ in keys)}) VALUES{_};"
                print(insert_sql)
                self.cursor.execute(insert_sql)
                return True
        except MySQLdb.IntegrityError:
            print('Insert fail, this message is exist')
            return False

    def search(self, table, sql_str: str):
        """
        Query data in the table
        :param table: table name
        :param sql_str: Database query conditions
        :return:
        """
        select_sql = F"SELECT * FROM {table} WHERE {sql_str};"
        print(select_sql)
        try:
            count = self.cursor.execute(select_sql)
            print(f'Found sql messages: %d' % count)
            if count != 0:
                for i in self.cursor.fetchall():
                    print(i)
            else:
                print("This message isn't exist")
            return count
        except MySQLdb.Error:
            print("Failed to query the data table. Procedure")
            return False

    def edit(self, table, keys: tuple, values: tuple):
        """
        Modify or replace data in a data table
        :param table: table name
        :param keys: table header
        :param values: table data
        :return:
        """
        try:
            for _ in values:
                edict_sql = f"REPLACE INTO {table}({','.join(_ for _ in keys)}) VALUES{_};"
                print(edict_sql)
                self.cursor.execute(edict_sql)
            return True
        except MySQLdb.Error:
            print("Failed to modify the data table. Procedure")
            return False

    def delete(self, table, sql_str: str):
        """
        Delete data from the table
        :param table: table name
        :param sql_str: Database query conditions
        :return:
        """
        delete_sql = f"DELETE FROM {table} WHERE {sql_str};"
        print(delete_sql)
        try:
            self.cursor.execute(delete_sql)
            return True
        except MySQLdb.Error:
            return False


if __name__ == '__main__':
    hold_keys = ("id", "username", "age")
    hold_values = ((1, "zhangsan", 21), (2, "lisi", 23))
    hold = ConnectSQL(host=localhost, user="root", password="123456", port=3306)
    hold.connect('user')
    hold.add('tb_user', hold_keys, hold_values)
    hold.edit('tb_user', hold_keys, hold_values)
    hold.delete('tb_user', "id = 1")
    hold.search('tb_user', "id = 1")