Python3 数据库操作小封装

import pymysql

'''
    SQLController:对数据库操作
        私有:
            __sql_connect(self):
                作用:建立数据库连接   
                返回:数据库连接对象
            __sql_insert_info(self, insert_sql):
                insert_sql: sql语句,默认为空
                作用:数据插入
            __sql_delete_info(self, delete_sql):
                delete_sql: sql语句,默认为空
                作用:删除数据
            __sql_update_info(self, update_sql):
                update_sql: sql语句,默认为空
                作用:更新数据
            __sql_select_info(self, select_sql):
                select_sql: sql语句,默认为空
                作用:查询数据    
        公共:
            sql_close(self):
                关闭数据库连接         
            sql_handle(self, handle, sql):
                handle:操作方式,默认为空,不区分大小写
                    值:
                        SQL_INSERT:插入数据操作
                        SQL_DELETE:删除数据操作
                        SQL_UPDATE:更新数据操作
                        SQL_SELECT:查询数据操作
                sql:sql语句,默认为空
                作用:数据的增删改查操作
            handle_table(self, handle, name, sql):
                handle:操作方式,默认为空,不区分大小写
                    值:
                        SHOW_TABLES:显示所有的数据表
                        TABLE_IS_EXIST:判断某数据表是否存在
                        CREATE_TABLE:创建数据表
                        SHOW_COLUMNS:显示某数据表的列名
                name:数据表名称,默认为空
                sql:sql语句,默认为空
                作用:查询表是否存在以及创建表
'''

class SQLController():

    __hostname = ''
    __username = ''
    __password = ''
    __dbname = ''

    def __init__(self, hostname = '', username = '', password = '', dbname = ''):
        if hostname == '' and username == '' and password == '' and dbname == '':
            print("No hostname or username or password or dbname!")
            pass
        else:
            self.__hostname = hostname
            self.__username = username
            self.__password = password
            self.__dbname = dbname

    #连接数据库
    def __sql_connect(self):
        db = pymysql.connect(self.__hostname, self.__username, self.__password, self.__dbname)
        return db

    #关闭数据库连接
    def sql_close(self):
        self.__sql_connect().close()

    #sql使用
    def sql_handle(self, handle = '', sql = ''):
        handle = handle.upper()
        if sql == '':
            print('SQL is empty')
            return 0
        if handle == '':
            print('Handle is empty')
            return 0
        if handle == 'SQL_INSERT':
            self.__sql_insert_info(sql)
        elif handle == 'SQL_DELETE':
            self.__sql_delete_info(sql)
        elif handle == 'SQL_SELECT':
            self.__sql_select_info(sql)
        elif handle == 'SQL_UPDATE':
            self.__sql_update_info(sql)
        else:
            print('%s Error, use SQL_INSERT or SQL_DELETE or SQL_UPDATE or SQL_SELECT' % handle)

    #表和数据库的操作
    def handle_table(self, handle = '', name = '', sql = ''):
        if handle == 'CREATE_TABLE' and sql == '':
            print('No name of table and database!')
            return 0
        if handle == 'SHOW_COLUMNS' and name == '':
            print('No table has been selected!')
            return 0
        try:
            handle = handle.upper()
            db = self.__sql_connect()
            cursor = db.cursor()
            if handle == 'SHOW_TABLES' or handle == 'TABLE_IS_EXIST':
                cursor.execute('show tables')
                tables = cursor.fetchall()
                if len(tables) == 0:
                    print('No Tables, You Need Create!')
                for table in tables:
                    if handle == 'SHOW_TABLES':
                        print(table[0])
                    elif handle == 'TABLE_IS_EXIST':
                        if name == table[0]:
                            print('%s exist!' % name)
                        else:
                            print('No %s!' % name)
                cursor.close()
            elif handle == 'CREATE_TABLE':
                cursor.execute('%s' % sql)
                db.commit()
                cursor.close()
                print('%s create success!' % name)
            elif handle == 'SHOW_COLUMNS':
                cursor.execute('show columns from %s' % name)
                column = cursor.fetchall()
                for i in column:
                    print(i[0])
                cursor.close()
                print('Success')
        except:
            print('%s Error' % handle)


    #增加数据
    def __sql_insert_info(self, insert_sql):
        try:
            db = self.__sql_connect()
            cursor = db.cursor()
            cursor.execute(insert_sql)
            db.commit()
            cursor.close()
            print('Insert success')
        except:
            print('Insert Info Failed!')
            db.rollback()

    #查询数据
    def __sql_select_info(self, select_sql):
        try:
            db = self.__sql_connect()
            cursor = db.cursor()
            cursor.execute(select_sql)
            result = cursor.fetchall()
            for row in result:
                print(row[0])
            cursor.close()
            print('Select success')
        except:
            print('Display Info Failed!')

    #更新数据
    def __sql_update_info(self, update_sql):
        try:
            db = self.__sql_connect()
            cursor = db.cursor()
            cursor.execute(update_sql)
            db.commit()
            cursor.close()
            print('Update success')
        except:
            print('Update Info Failed!')
            db.rollback()

    #删除数据
    def __sql_delete_info(self, delete_sql):
        try:
            db = self.__sql_connect()
            cursor = db.cursor()
            cursor.execute(delete_sql)
            db.commit()
            cursor.close()
            print('Delete success')
        except:
            print('Delete Info Failed!')
            db.rollback()

    #数据库连接测试
    def sql_connect_test(self):
        db = self.__sql_connect()
        cursor = db.cursor()
        cursor.execute('select version()')
        data = cursor.fetchone()
        print('database version : %s' % data)

#模块测试(测试不完整)
# if __name__ == '__main__':
    # sqlc = SQLController('localhost', 'root', '123456', 'MovieInfo')
    # sqlc.sql_connect_test()
    # sqlc.table_handle('SHOW_TABLE')
    # m = 10
    # sql_lang = 'insert into b(age) values (%d)' % m
    # sqlc.sql_handle('SQL_INSERT', sql_lang)
    # sql_lang_2 = 'select * from b'
    # sql_lang_3 = 'delete from b where age = %d' % m
    # sqlc.sql_handle('SQL_SELECT', sql_lang_2)
    # sqlc.sql_handle('SQL_delete', sql_lang_3)
    # sqlc.sql_handle('SQL_SELECT', sql_lang_2)
    # sqlc.handle_table('SHOW_TABLES')
    # sqlc.handle_table('TABLE_IS_EXIST', 'a')
    # sqlc.handle_table('CREATE_TABLE', '', 'create table c (sex varchar(10))')
    # sqlc.handle_table('SHOW_TABLES')
    # sqlc.handle_table('SHOW_COlumns', 'a')