python操作mysql

#!/usr/bin/env python
# encoding: utf-8

import pymysql
import datetime
import sys
from warnings import filterwarnings

filterwarnings('ignore', category=pymysql.Warning)  # 过滤掉Mysql的warnning信息


# 封装pymysql类基本操作mysqldb


class MysqlDb:
    def __init__(self, host=None, user=None, password="", database=None, port=3306, charset=''):
        try:
            self.conn = pymysql.connect(host=host, user=user, password=password, database=database, port=int(port),
                                        charset=charset)
            self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        except pymysql.Error as e:
            sys.exit()

    # 针对读操作返回结果集
    def _execute(self, sql=''):
        try:
            self.cursor.execute(sql)
            records = self.cursor.fetchall()
            return records
        except pymysql.Error as e:
            sys.exit()

    # 针对更新,删除,事务等操作失败时回滚
    def executecommit(self, sql=''):
        try:
            self.cursor.execute(sql)
            self.conn.commit()

        except pymysql.Error as e:
            self.conn.rollback()
            sys.exit()

    # 创建表
    # tablename:表名称,attr_dict:属性键值对,constraint:主外键约束
    # attr_dict:{'book_name':'varchar(200) NOT NULL'...}
    # constraint:PRIMARY KEY(`id`)
    def createtable(self, tbl, attr_dict, constraint='PRIMARY KEY(`id`)'):
        sql = ''
        sql_mid = '`id` bigint(11) NOT NULL AUTO_INCREMENT,'
        for attr, value in attr_dict.items():
            sql_mid = sql_mid + '`' + attr + '`' + ' ' + value + ','
        sql = sql + 'CREATE TABLE IF NOT EXISTS %s (' % tbl
        sql = sql + sql_mid
        sql = sql + constraint
        sql = sql + ') ENGINE=InnoDB DEFAULT CHARSET=utf8'
        self.executecommit(sql)

    def qurey(self, table, columns='*', condition=None, order=''):
        consql = condition.replace('&', ' and ') + ' and 1=1 '
        sql = 'SELECT {0} FROM {1} WHERE '.format(columns, table)
        sql = sql + consql + order
        return self._execute(sql)

    def select(self, table, condition=None, order=''):
        """
        查询表内容
        :param table: table name
        :param condition: {'name':'xiaoming'...}
        :param order: 'order by id desc'
        :return:
        """
        consql = ' '
        if condition:
            for subcond in condition:
                if subcond:
                    consql = consql + subcond['column'] + subcond.get('delimiter', '=') + subcond['value'] + ' and '
        consql = consql + '1=1 '
        sql = 'SELECT * FROM %s WHERE ' % table
        sql = sql + consql + order
        return self._execute(sql)

    def delete(self, tbl, cond_dict=None, order=''):
        """
        查询表内容
        :param tbl: table name
        :param cond_dict: {'name':'xiaoming'...}
        :param order: 'order by id desc'
        :return:
        """
        consql = ' '
        if cond_dict:
            for k, v in cond_dict.items():
                consql = consql + k + '=' + v + ' and'
        consql = consql + ' 1=1 '
        sql = 'DELETE  FROM %s WHERE ' % tbl
        sql = sql + consql + order
        return self.executecommit(sql)

    # 插入单条数据
    def insert(self, tbl, columns, value):
        # values_sql = ['%s' for v in attrs]
        attrs_sql = '(' + ','.join(columns) + ')'
        values_sql = ' VALUES (' + ','.join(map(lambda x: '\'' + str(x) + '\'', value)) + ')'
        sql = 'INSERT INTO %s' % tbl
        sql = sql + attrs_sql + values_sql
        self.executecommit(sql)

    def insertlot(self, tbl, col_val_dict):
        try:
            for data in col_val_dict:
                columns_list = []
                values_list = []
                for key, value in data.items():
                    columns_list.append(key)
                    if "\'" in str(value):
                        value = value.replace("\'", "\\'")
                    values_list.append('\'' + str(value) + '\'')
                sql = "INSERT INTO {0}({1}) VALUES({2})".format(tbl, ", ".join(columns_list), ", ".join(values_list))
                self.executecommit(sql)
        except Exception as e:
            logger.error(e)

    def insertmany(self, tbl, columns, values):
        """
        插入多条数据
        :param tbl: table name
        :param columns: [id,name,...]
        :param values: [[1,'jack'],[2,'rose']]
        :return:
        """
        values_sql = ['%s'] * len(columns)
        # values_sql = ['%s' for v in columns]
        attrs_sql = '(' + ','.join(columns) + ')'
        values_sql = ' VALUES (' + ','.join(values_sql) + ')'
        sql = 'INSERT INTO %s' % tbl
        sql = sql + attrs_sql + values_sql
        try:
            for i in range(0, len(values), 20000):
                self.cursor.executemany(sql, values[i:i + 20000])
                self.conn.commit()
        except pymysql.Error as e:
            self.conn.rollback()
            sys.exit()

    @staticmethod
    def _now():
        now0 = datetime.datetime.now()
        now = now0.strftime('%Y-%m-%d %H:%M:%S')
        logger.debug(now)

    def _close(self):
        try:
            self.cursor.close()
            self.conn.close()
        except AttributeError:
            pass

    def __del__(self):
        self._close()