python MySQL的使用

注意：

1.Text类型不能作为主键

import pymysql

data = {
    'id': '1',
    'name': 'Mike',
    'age': '28'
}

class MySQL(object):
    def __init__(self,host,user,passwd,database,port,charset):
         self.host = host
         self.user = user
         self.passwd = passwd
         self.database = database
         self.port = port
         self.charset = charset

    def connect_mysql(self):
        try:
            global db
            db = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd,
                                 database=self.database, port=self.port, charset=self.charset)
            global cursor
            cursor = db.cursor()
            print('CONNECT DATABASE')
        except Exception as e:
            print(f'CONNECT FAILED.Case:{e}')

    def create_table(self,create_table_sql):
        sql = create_table_sql
        try:
            cursor.execute(sql)
            print('CREATE TABLE SUCCESS')
        except Exception as e:
            print(f'CREATE TABLE FAILED.CASE:{e}')

    def insert_data(self,insert_into_table_sql):
        sql = insert_into_table_sql

        try:
            cursor.execute(sql, tuple(data.values()))
            db.commit()
        except Exception as e:
            db.rollback()
            print(f'INSERT INTO TABLE FAILED.Case:{e}')

    def delete_data(self,delete_data_sql):
        sql = delete_data_sql

        try:
            cursor.execute(sql)
            db.commit()
        except Exception as e:
            db.rollback()
            print(f'DELETE DATA FAILED.Case:{e}')

    def updata_data(self,updata_data_sql):
        sql = updata_data_sql
        try:
            cursor.execute(sql, tuple(data.values()) * 2)
            db.commit()
        except Exception as e:
            db.rollback()
            print(f'UPDATA DATA FAILED.Case:{e}')

    def select_data(self,select_data_sql, one_or_all):
        sql = select_data_sql

        select_data_list = []

        try:
            cursor.execute(sql)
            if one_or_all == 'one':
                return cursor.fetchone()
            elif one_or_all == 'all':
                select_data = cursor.fetchone()
                while select_data:
                    select_data_list.append(select_data[0])
                    select_data = cursor.fetchone()
        except Exception as e:
            print(f'SELECT DATA FAILED.Case:{e}')

        return select_data_list

    def close_mysql(self):
        try:
            db.close()
            print('CLOSE DATABASE')
        except Exception as e:
            print(f'CLOSE DATABASE FAILED.Case:{e}')

if __name__ == "__main__":
    # 创建MySQL实例
    mysql = MySQL(host='localhost', user='root', passwd='cjlushenbin',
                        database='my_database', port=3306, charset='utf8')
    # 连接MySQL
    mysql.connect_mysql()

    # 创建表
    create_table_name='demo_table'
    create_table_sql='''
    CREATE TABLE IF NOT EXISTS {create_table_name}(
    ID VARCHAR(255),
    NAME VARCHAR(255),
    AGE VARCHAR(255),
    PRIMARY KEY(id)         #设置id为主键
    )'''.format(create_table_name=create_table_name)
    mysql.create_table(create_table_sql)

    # 插入数据
    # insert_data_table_name = 'demo_table'
    # insert_data_keys = ','.join(data.keys())
    # insert_data_values = ','.join(['%s'] * len(data))
    # insert_data_sql = 'INSERT INTO {table_name}({keys}) VALUES({values})'\
    #     .format(table_name=insert_data_table_name, keys=insert_data_keys,values=insert_data_values)
    # mysql.insert_data(insert_data_sql)

    # #查找数据
    # select_data = 'age'
    # select_data_table_name = 'demo_table'
    # select_data_condition = 'id<5'
    # select_data_sql = 'SELECT {data} FROM {table_name} WHERE {condition}' \
    #     .format(data=select_data, table_name=select_data_table_name, condition=select_data_condition)
    # select_data_list = mysql.select_data(select_data_sql,one_or_all='all')
    # print(select_data_list)

    # # 更新数据
    # updata_data_table_name = 'demo_table'
    # updata_data_keys = ','.join(data.keys())
    # updata_data_values = ','.join(['%s'] * len(data))
    #
    # sql = 'INSERT INTO {table_name}({keys}) VALUES({values}) ON DUPLICATE KEY UPDATE ' \
    #     .format(table_name=updata_data_table_name, keys=updata_data_keys, values=updata_data_values)
    # updata = ','.join(["{key}=%s".format(key=key) for key in data])
    # updata_data_sql = sql+updata
    #
    # mysql.updata_data(updata_data_sql)

    # 删除数据
    # delete_data_table_name = 'demo_table'
    # delete_data_condition = 'id=1'
    # delete_data_sql = 'DELETE FROM {table_name} WHERE {condition}'.format(table_name=delete_data_table_name,
    #                                                                       condition=delete_data_condition)
    # mysql.delete_data(delete_data_sql)

    # 关闭数据库
    # mysql.close_mysql()