python数据库操作封装_Python封装操作MySQL

#!/usr/bin/env python

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

# @Time : 2018/12/6 5:35 PM

# @Author : jiayu.chen

# @Description:

import json

import pymysql

from base.log import Log

class MySQL(object):

logger = Log()

def __init__(self, **config):

try:

self._conn = pymysql.connect(**config)

self._cursor = self._conn.cursor()

except pymysql.Error as e:

self.logger.error("MySQL Error message ".format(e))

def query(self, sql):

try:

result = self._cursor.execute(sql)

except pymysql.Error as e:

self.logger.error("MySQL Error message : ".format(e))

result = False

return result

def select(self, table, column='*', condition=''):

global sql

condition = ' where ' + condition if condition else None

if condition:

try:

sql = "select %s from %s %s" % (column, table, condition)

self.logger.info("要执行的SQL：".format(sql))

except pymysql.Error as e:

self.logger.error("MySQL Error message ".format(e))

else:

try:

sql = "select %s from %s" % (column, table)

self.logger.info("要执行的SQL：".format(sql))

except pymysql.Error as e:

self.logger.error("MySQL Error message ".format(e))

sql = "select %s from %s" % (column, table)

self.query(sql)

return self._cursor.fetchall()

def insert(self, table, tdict):

column = ''

value = ''

for key in tdict:

column += ',' + key

value += "','" + tdict[key]

column = column[1:]

value = value[2:] + "'"

sql = "insert into %s(%s) values(%s)" % (table, column, value)

try:

self._cursor.execute(sql)

self.logger.info("要执行的SQL：".format(sql))

self._conn.commit()

except pymysql.Error as e:

self.logger.error("MySQL Error message ".format(e))

return self._cursor.lastrowid # 返回最后的id

def update(self, table, tdict, condition=''):

if not condition:

self.logger.error("must have condition ")

exit()

else:

condition = 'where ' + condition

value = ''

for key in tdict:

value += ",%s='%s'" % (key, tdict[key])

value = value[1:]

sql = "update %s set %s %s" % (table, value, condition)

try:

self._cursor.execute(sql)

self.logger.info("要执行的SQL：".format(sql))

except pymysql.Error as e:

self.logger.error("MySQL Error message ".format(e))

self.rollback()

# 返回受影响行数

return self.affected_num()

def delete(self, table, condition=''):

if not condition:

self.logger.error("must have condition ")

exit()

else:

condition = 'where ' + condition

# condition = 'where ' + condition if condition else None

sql = "delete from %s %s" % (table, condition)

self._cursor.execute(sql)

self._conn.commit()

# 返回受影响行数

return self.affected_num()

def rollback(self):

self._conn.rollback()

def affected_num(self):

return self._cursor.rowcount

def __del__(self):

try:

self._cursor.close()

self._conn.close()

except:

pass

def close(self):

self.__del__()