python操作mysql数据库

# -*- coding: utf-8 -*-
import pymysql
import numpy as np


class MysqlOp():
    def __init__(self, host='127.0.0.1', user='', passwd='', port=3306, db='xx', 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()

    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 readTable(self, tbName=None, items='*', dt=None):
        if tbName is None:
            raise ValueError("tbName is None!")
        if dt is None:
            sql = """select {items} from {tbName}""".format(items=items, tbName=tbName)
        elif dt is not None:
            sql = """select {items} from {tbName} where dt={dt}""".format(items=items, tbName=tbName, dt=dt)
        self.cur.execute(sql)
        data = np.array(self.cur.fetchall())
        print(data[:5, :])
        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()
    sqlhandle.listTables()
    sqlhandle.readTable(tbName='xx')
    sqlhandle.showTableInfo(tbName='xx')