m_SQL.py

import pymysql as MySQLdb
from sqlalchemy import create_engine
import pandas as pd
import re


class Schema:
    # 初始化
    # 参数一 数据库名 参数二表名
    def __init__(self, host='', user='', password='', mysqlName='', port=''):
        self.host = host
        self.user = user
        self.password = password
        self.mysqlName = mysqlName
        self.port = port
        # 打开数据库连接
        self.db = MySQLdb.connect(host=host, user=user, password=password, db=mysqlName, charset='utf8')
        # 使用 cursor() 方法创建一个游标对象 cursor
        self.cursor = self.db.cursor()

    def GetField(self, tableName=""):
        sql = 'select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME = "' + tableName + '"'
        self.cursor.execute(sql)

        str_f = self.cursor.fetchall().__str__()
        c = re.sub('[(), ]', '', str_f)
        str_list = c.split("'")
        field_list = list(filter(None, str_list))[1:]
        self.db.commit()
        return field_list

    def setAddField(self, tableName="", field=""):
        field_list = self.GetField(tableName=tableName)
        if field not in field_list:
            sql = 'ALTER TABLE ' + tableName + ' ADD ' + field + ' INT(16) DEFAULT 0 '
            self.cursor.execute(sql)
            self.db.commit()
        # pd.read_sql_query(sql, con=self.engine)

    def dataframeToMysql(self, data, tableName=""):
        engine_address = "mysql+pymysql://" + self.user + ":" + self.password + "@" + self.host + ":" + self.port + "/" + self.mysqlName
        engine = create_engine(engine_address, echo=True)
        data.to_sql(name=tableName, con=engine, if_exists='append')

    # list写入mysql table.listToMysql(['username','password'],["asd","as"])
    # 参数1 字段list 参数2 valueList
    def listToMysql(self, tableName, valueList=[]):
        try:
            field = ""
            value = ""
            for i, j in zip(self.fieldList, valueList):
                field += i + ','
                value += "'" + j + "',"
            sql = "INSERT INTO {} ({}) VALUES ({})".format(tableName, field[:-1], value[:-1])
            self.cursor.execute(sql)
        except Exception as e:
            print(str(e))

    # csv文件写入mysql 参数一 路径 参数二编码
    def csvToMysql(self, path='', encoding=''):
        import csv
        csv_reader = csv.reader(open(path, encoding=encoding))
        for row in csv_reader:
            try:
                self.listToMysql(row)
            except Exception as e:
                print(str(e))

    # xlsx文件写入mysql 参数一 路径 参数二编码
    def xlsxToMysql(self, path=""):
        import xlrd as xlsx
        for length, dataList in xlsx.open_workbook(path):
            try:
                valueList = []
                for elem in dataList:
                    valueList.append(str(elem.value))
                self.listToMysql(valueList)
            except Exception as e:
                print(str(e))

    def getData(self, tableName="", startTime="", endTime=""):
        engine_address = "mysql+pymysql://" + self.user + ":" + self.password + "@" + self.host + ":" + self.port + "/" + self.mysqlName
        engine = create_engine(engine_address, echo=True)
        sql = 'SELECT * FROM ' + tableName + ' WHERE datetime >= "' + startTime + '" and datetime< "' + endTime + '"'
        print (sql)
        data = pd.read_sql_query(sql, con=engine)
        print ("****%d"%(len(data)))
        return data

    def delData(self, tableName, keyWord):
        word = '"' + keyWord + '"'
        sql = 'DELETE FROM {tableName} WHERE query={keys}'.format(tableName=tableName, keys=word)
        self.cursor.execute(sql)
        self.commit()

    # 关闭数据库连接
    def closeConnect(self):
        self.db.close()

    # 提交
    def commit(self):
        self.db.commit()

    def delete(self, tableName):
        self.db.cursor("DELETE * FROM {}".format(tableName))