mysql数据库封装_MySQL数据库常用方法的封装

#!/usr/bin/env python

# -*- coding:utf-8 -*-

"""

Created on 2019-5-25

@author: Kyrie Liu

@description: MySQL method

"""

import MySQLdb

from RelNoteConst import Const

class Database(object):

def __init__( self, host, user, passwd, db, port):

self.conn = MySQLdb.connect(host, user, passwd, db, port, charset='utf8')

self.cursor = self.conn.cursor()

def get_rows_count_of_table(self, _table_name, condition):

"""

get all rows number of the table

:param _table_name: table name

:param condition: filter condition, example:'*' - all rows, other 'key="123" and ...'

:return:

"""

__rows = 'SELECT COUNT(*) FROM {} WHERE {};'.format(_table_name, condition)

self.cursor.execute(__rows)

rows_num = self.cursor.fetchone()

if rows_num:

return rows_num[0]

return 0

def is_row_of_table(self, _table_name, condition):

"""

whether existed the row in table

:param _table_name: table name

:param condition: filter condition, example: 'Key3="567" and ...'

:return:

"""

return self.get_rows_count_of_table(_table_name, condition) > 0

def get_fields_of_table(self, _table_name):

"""

get all keys of the table

:param _table_name: table name

:return: keys iterator

"""

self.cursor.execute('SELECT * FROM {};'.format(_table_name))

fields_list = [column[0] for column in self.cursor.description]

return fields_list

def search_table(self, _table_name):

"""

whether exists the table

:param _table_name: table

:return: true - exist, false - not exist

"""

#panda 字符串命名不合适

is_existed_table = 'SHOW TABLES LIKE "{}"'.format(_table_name)

self.cursor.execute(is_existed_table)

__result = self.cursor.fetchall()

return True if __result else False

def insert_row_to_table(self, _table_name, _items):

"""

insert a row value with the length of keys

:param _table_name: table name

:param _items: values list, example: ['values1, values2, ...']

:return:

"""

__items_format = []

for item in _items:

if type(item) in [str, unicode]:

item = '"' + item.encode('utf8') + '"'

__items_format.append(str(item))

__insert = 'INSERT INTO {} VALUES({});'.format(_table_name, ','.join(__items_format))

# print __insert

try:

self.cursor.execute(__insert)

self.conn.commit()

except Exception as err:

print(err)

self.conn.rollback()

def insert_with_specific_item(self, _table_name, _items, _values):

"""

insert a new row with specific item

:param _table_name:

:param _items: names list of item

:param _values: values list

:return:

"""

values = ''

#__items_format = []

#for item in _items:

# if str == type(item):

# item = '"' + item.encode('utf8') + '"'

# __items_format.append(str(item))

for m_val in _values:

if isinstance(m_val,int):

values += '%d,' % m_val

elif isinstance(m_val, str):

values += "'%s'," % m_val

else:

pass

if values[-1] == ',':

values = values[:-1]

__insert = 'INSERT INTO {}({}) VALUES({});'.format(_table_name, ','.join(_items),values)

print __insert

try:

self.cursor.execute(__insert)

self.conn.commit()

except Exception as err:

print(err)

self.conn.rollback()

def update_value_of_row(self, _table_name, update_items, condition):

"""

update value of the specific row

:param _table_name: table name

:param update_items: 'Key1="123",Key2="234",...'

:param condition: 'Key3="567" and Key4="789" and ...'

:return:

"""

__update = 'UPDATE {} SET {} WHERE {};'.format(_table_name, update_items, condition)

try:

self.cursor.execute(__update)

self.conn.commit()

except Exception as err:

print(err)

self.conn.rollback()

def delete_data_of_table(self, _table_name, condition=None):

"""

delete data of the specific rows, delete all rows if condition is none

:param _table_name: table name

:param condition: 'Key3="xxx" , Key4="xxx" and ...'

:return:

"""

__delete = 'DELETE FROM ' + _table_name

try:

if condition:

__delete = '{} WHERE {}'.format(__delete, condition)

self.cursor.execute(__delete)

self.conn.commit()

except Exception as err:

print(err)

self.conn.rollback()

def drop_table(self, _table_name):

"""

delete table

:param _table_name: delete table

:return:

"""

try:

__drop = 'DROP TABLE ' + _table_name

self.cursor.execute(__drop)

self.conn.commit()

except Exception as err:

print(err)

self.conn.rollback()

def query_data_of_table(self, _table_name, keys, condition=None, order=None):

"""

query table

:param order: sorted output by which key

:param _table_name: table name

:param keys: 'Key1,key2...'

:param condition: 'Key1='x' and Key2='y' and ...'

:return: query result is iterator

"""

__keys = keys

if isinstance(__keys, list):

__keys = ','.join(__keys)

__query = 'SELECT {} FROM {}'.format(__keys, _table_name)

if condition:

__query += ' WHERE {} '.format(condition)

if order:

__query += ' ORDER BY {}'.format(order)

self.cursor.execute(__query)

__result = self.cursor.fetchall()

return list(__result)

def create_table(self, _table_name, _items):

"""

create table

:param _table_name: table name

:param _items: Keys list , example: ['Key1', 'Key2', ...]

:return:

"""

if len(_items) < 1:

print('Data item too short!')

return

__create = 'CREATE TABLE IF NOT EXISTS {} ({})'.format(_table_name, ','.join(_items))

try:

self.cursor.execute(__create)

self.conn.commit()

except Exception as err:

print(err)

self.conn.rollback()

def __del__(self):

self.cursor.close()

self.conn.close()