python 操作mysql类封装

# -*- coding: utf-8 -*-
import pymysql
import numpy as np
import pandas as pd
from sqlalchemy import create_engine


class MysqlOp():
    def __init__(self, host='127.0.0.1', user='root', passwd='123456', port=3306, db='test_db', charset='utf8'):

        self.host = host
        self.user = user
        self.passwd = passwd  # 密码
        self.port = port  # 端口，默认为3306
        self.db = db  # 数据库名称
        self.charset = charset  # 字符编码
        self.conn = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd,
                                    port=self.port, db=self.db, charset=self.charset)
        self.cur = self.conn.cursor()
        # create_engine()初始化数据库连接时需要提供：'数据库类型+数据库驱动名称://用户名:密码@地址:端口号/数据库名'
        self.engine = create_engine('mysql+pymysql://{usr}:{paswd}@{host}:{port}/{db}'.format(usr=user, paswd=passwd,
                                                                                        host=host, port=port, db=db))
    def getCur(self):
        print('Get cur successfully!')
        return self.cur

    def creat_table(self, df=None, tbName=None, index=False):
        if df is None:
            raise ValueError('Your input df is None!')
        df.to_sql(tbName, self.engine, index=index)
        print('Create table {tbName} successfully!'.format(tbName=tbName))

    def write_table(self, tbName=None, df=None):
        if df is None:
            raise ValueError('Your input df is None!')
        df.to_sql(tbName, self.engine, schema=self.db, index=False, if_exists='append')
        print('Write table {tbName} successfully!'.format(tbName=tbName))

    def delete_table(self, tbName=None, conditon=None):
        # eg:condition='con=1,con=2'
        if conditon is not None:
            sql = """DELETE FROM {tbName} WHERE {con}""".format(tbName=tbName, con=conditon)
        else:
            sql = """DELETE FROM {tbName}""".format(tbName=tbName)
        self.cur.execute(sql)
        print('Delete data from table {tbName} successfully!'.format(tbName=tbName))

    def listTables(self):
        sql = """show tables"""  # SQL语句
        self.cur.execute(sql)  # 执行SQL语句
        data = self.cur.fetchall()  # 通过fetchall方法获得数据
        data = [t[0] for t in list(data)]
        print('Tables of database {db}: {tables}'.format(db=self.db, tables=data))
        return data

    def update_table(self, tbName=None, content=None, conditon=None):
        # eg:condition='con=1,con=2'
        # eg:content={field1=sth_new, field2=sth_new}
        content_new = """"""
        for k, v in content.items():
            if type(v) is str:
                content_new += """{k}='{v}',""".format(k=k, v=v)
            else:
                content_new += """{k}={v},""".format(k=k, v=v)
        sql = """UPDATE {tbName} SET {content} WHERE {con}""".format(tbName=tbName, content=content_new[:-1], con=conditon)
        print(sql)
        self.cur.execute(sql)
        print('Update table {tbName} successfully!'.format(tbName=tbName))

    def readTable(self, tbName=None, items='*'):  # eg: items='field1, field2'
        if tbName is None:
            raise ValueError("tbName is None!")
        sql = """select {items} from {tbName}""".format(items=items, tbName=tbName)
        self.cur.execute(sql)
        data = np.array(self.cur.fetchall())
        sql = """select COLUMN_NAME from information_schema.COLUMNS where table_name = '{tbName}'""".format(tbName=tbName)
        self.cur.execute(sql)
        cols = list(self.cur.fetchall())
        cols = [col[0] for col in cols]
        data = pd.DataFrame(data, columns=cols)
        print(data.head())
        print('Read table {tbName} successfully!'.format(tbName=tbName))
        return data

    def showTableInfo(self,tbName=None):
        if tbName is None:
            raise ValueError("tbName is None!")
        sql = """show create table {tbName}""".format(tbName=tbName)
        self.cur.execute(sql)
        data = self.cur.fetchall()
        for s in list(data[0]):
            if s == tbName:
                continue
            print(s)
        return list(data[0])

    def dropTable(self,tbName=None):
        if tbName is None:
            raise ValueError("tbName is None!")

        sql = """drop table if exists {tbName}""".format(tbName=tbName)
        self.cur.execute(sql)
        print('Drop table {tbName} successfully!'.format(tbName))

    def execSql(self, sql=None):
        if sql is None:
            return
        else:
            self.cur.execute(sql)
            print('Run sql:{sql} successfully!'.format(sql=sql))


if __name__ == '__main__':

    sqlhandle = MysqlOp(host='127.0.0.1', user='root', passwd='123456', port=3306, db='test_db')