python封装数据库操作_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')