def connect_to_mysql():
conn = MySQLConnector()
conn.open(self.mysql_connection['host'],
self.mysql_connection['name'],
self.mysql_connection['user'],
self.mysql_connection['pass'])
return conn
def OutterChecker(predictGroups, SD, ED, HRZID, Code, downGasAmount,
upGasAmount):
# 基于起始和终止日期，建立合适的时间索引和DataFrame
validIndex = pd.date_range(SD, ED, freq='1min')
validDataFrame = pd.DataFrame(index=validIndex)
# 创建数据库连接
conn = MySQLConnector()
conn.openConnector()
# 查询该控制周期内燃气流量序列
SQL_Gas = u'SELECT create_time,瞬时流量 FROM bd_xinkou_2 WHERE create_time>"%s" and create_time<"%s"' % (
SD, ED)
conn.cursor.execute(SQL_Gas)
realGasSeries = np.array(conn.cursor.fetchall())
if len(realGasSeries) == 0:
return 1, [1], np.random.randn(len(validIndex)), np.random.randn(
len(validIndex))
realGasDataFrame = pd.DataFrame(np.array(realGasSeries[:, 1],
dtype=np.float32),
index=realGasSeries[:, 0],
columns=[u'瞬时流量'])
realGasDataFrame = realGasDataFrame.resample('1min').mean()
validDataFrame[u'瞬时流量'] = realGasDataFrame[u'瞬时流量']
realGasMean = realGasSeries[:, 1].mean()
# 查询换热站一次回温度
SQL_HFBT = u'SELECT create_time,一次回温度 FROM bd_xinkou_hrz WHERE project_sub_station_id=%d and code="%s" and create_time>"%s" and create_time<"%s"' % (
HRZID, Code, SD, ED)
conn.cursor.execute(SQL_HFBT)
realHFBTArray = np.array(conn.cursor.fetchall())
if len(realHFBTArray) == 0:
return 1, [1], np.random.randn(len(validIndex)), np.random.randn(
len(validIndex))
realHFBTSeries = pd.Series(np.array(realHFBTArray[:, 1], dtype=np.float32),
index=realHFBTArray[:, 0],
name=u'一次回温度')
realHFBTSeries = realHFBTSeries.resample('1min').mean()
validDataFrame[u'一次回温度'] = realHFBTSeries
# 确定预测组的恰当索引
try:
assert realGasMean > downGasAmount
assert realGasMean < upGasAmount
predictGroupsIndex = realGasMean // 100 - downGasAmount // 100
except AssertionError:
if realGasMean <= downGasAmount:
predictGroupsIndex = 0
else:
predictGroupsIndex = -1
targetPredict = predictGroups[predictGroupsIndex]
validDataFrame[u'预测回水温度'] = targetPredict
validDataFrame = validDataFrame.fillna(method='ffill').fillna(
method='bfill')
score = evalueHFBT(validDataFrame[u'瞬时流量'].as_matrix().ravel(),
validDataFrame[u'一次回温度'].as_matrix().ravel(),
validDataFrame[u'预测回水温度'].as_matrix().ravel())
GasDis = [1]
print u'在该控制周期内实际耗费燃气量为：%.1f' % realGasMean
return score, GasDis, validDataFrame[u'一次回温度'].as_matrix().ravel(
), validDataFrame[u'预测回水温度'].as_matrix().ravel()
def configSQL(self):
connector = MySQLConnector(LOGIN_INFO_PATH)
self.sqlConn = connector.connect()
# create database if not exists
if not DATABASE_NAME in self.sqlConn.getDatabaseList():
self.sqlConn.createDatabase(DATABASE_NAME)
db = self.sqlConn.getDatabase(DATABASE_NAME)
# create tables if not exists
if not SYMBOL_DETAIL_TABLE_NAME in db.getTableList():
db.createTable(SYMBOL_DETAIL_TABLE_NAME, SYMBOL_DETAIL_TABLE)
db.commit()
self.sqlConn.close()
def __init__(self, date):
self.date = date
self.connector = MySQLConnector()
self.logger = logging.getLogger('analyzer')
self.logger.setLevel(level=logging.DEBUG)
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)
formatter = logging.Formatter(
'%(asctime)s - %(name)s - %(levelname)s - %(message)s')
ch.setFormatter(formatter)
self.logger.addHandler(ch)
self.logger.info("Finished initialization.")
def __init__(self, date):
self.date = datetime.datetime.strptime(date, '%Y%m%d')
self.connector = MySQLConnector()
leancloud.init("U83hlMObhFRFRS4kX3lOxSlq-gzGzoHsz",
"Jw2Y6KFFsjI5kEz1qYqQ62da")
logging.basicConfig(level=logging.DEBUG)
self.logger = logging.getLogger('analyzer')
self.logger.setLevel(level=logging.DEBUG)
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)
formatter = logging.Formatter(
'%(asctime)s - %(name)s - %(levelname)s - %(message)s')
ch.setFormatter(formatter)
self.logger.addHandler(ch)
self.logger.info("Finished initialization.")
def updateHistData(symbols: list):
connector = MySQLConnector(LOGIN_INFO_PATH)
sqlConn = connector.connect()
saveCount = 0
for i in range(len(symbols)):
print("updating... {}/{}".format(i + 1, len(symbols)))
symbol = symbols[i]
yf = yahoofinance.YFData(symbol)
updateHistPrice(sqlConn, yf, symbol)
updateDividend(sqlConn, yf, symbol)
updateStocksplit(sqlConn, yf, symbol)
checkAdjclose(sqlConn, yf, symbol)
saveCount += 1
if saveCount == 100:
sqlConn.commit()
saveCount = 0
sqlConn.commit()
sqlConn.close()
def save_to_trips(registers):
"""
Saves a list of registers to local>taxi>trips
Input:
registers: list of suitable dictionaries
Output:
None
"""
# Create MySQL connection
cnx = MySQLConnector()
# Insert registers into trips table
for reg in registers:
cnx.insert(reg)
# Close connection
cnx.close()
def getSymbolList() -> list:
connector = MySQLConnector(LOGIN_INFO_PATH)
sqlConn = connector.connect()
sql = """
SELECT `symbols`.`symbols`.`symbol`, `symbols`.`symbols`.`enable`, `symbols`.`symbol_details`.`marketCap`
FROM `symbols`.`symbols`
INNER JOIN `symbols`.`symbol_details` ON `symbols`.`symbols`.`symbol`=`symbols`.`symbol_details`.`symbol`
WHERE `symbols`.`symbols`.`enable` = 1 and `symbols`.`symbol_details`.`marketCap` > 0;
"""
data = sqlConn.execute(sql)
symbols = data["symbol"]
sql = """
SELECT `symbol` FROM `symbols`.`symbol_details`
WHERE `quoteType` = "INDEX";
"""
data = sqlConn.execute(sql)
symbols.extend(data["symbol"])
sqlConn.close()
return symbols
def __init__(self, last_date=None, this_date=None):
self.connector = MySQLConnector()
self.logger = logging.getLogger('crawler')
self.logger.setLevel(level=logging.DEBUG)
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG) # 输出到console的log等级的开关
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
ch.setFormatter(formatter)
self.logger.addHandler(ch)
# load media list
if last_date == None:
last_date = self.get_last_date()
self.last_date = datetime.datetime.strptime(last_date, '%Y%m%d').replace(tzinfo=pytz.utc)
if this_date != None:
self.today = this_date
else:
self.today = time.strftime("%Y%m%d", time.localtime())
self.logger.info("Finished initialization.")
from MySQLConnector import MySQLConnector
if __name__=="__main__":
print("Hello world!")
print(MySQLConnector().connect())
def readData(self):
# 建立对数据库的连接
conn = MySQLConnector()
conn.openConnector()
# ++++++++++++++ 查询锅炉状态 +++++++++++++++++++++++++++++++++++
# 从SQL语句中分析所需要抓取的字段名模式
columnsPattern = re.compile("SELECT DISTINCT (.*?) FROM")
columnsStr = re.findall(columnsPattern, self._QueryBoilerState)[0]
columns = columnsStr.strip().split(',')
columns = [(c.strip())[2:] for c in columns]
# 对字段名进行修正
if len(self._BoilerColumnsMap()) != 0:
columns = [self._BoilerColumnsMap()[c] for c in columns]
# 游标执行查询锅炉状态数据的语句
conn.cursor.execute(self._QueryBoilerState)
tempBoilerState = np.array(conn.cursor.fetchall())
# 对该数据集中日期时间进行修正
modifiedTime = [tools_DateTimeTrans(i) for i in tempBoilerState[:, 0]]
BoilerData = pd.DataFrame(np.array(tempBoilerState[:, 1:], np.float32),
index=modifiedTime,
columns=columns[1:])
# ++++++++++++++ 查询锅炉状态 +++++++++++++++++++++++++++++++++++
# ++++++++++++++ 查询天气状态 +++++++++++++++++++++++++++++++++++
# 从SQL语句中分析所需要抓取的字段名模式
columnsStr = re.findall(columnsPattern, self._QueryWeatherState)[0]
columns = columnsStr.strip().split(',')
columns = [c.strip()[2:] for c in columns]
# 对字段名进行修正
if len(self._WeatherColumnsMap()) != 0:
columns = [self._WeatherColumnsMap()[c] for c in columns]
# 游标执行查询天气状态数据的语句
conn.cursor.execute(self._QueryWeatherState)
tempWeatherState = np.array(conn.cursor.fetchall())
modifiedTime = [tools_DateTimeTrans(i) for i in tempWeatherState[:, 0]]
WeatherData = pd.DataFrame(np.array(tempWeatherState[:, 1:],
np.float32),
index=modifiedTime,
columns=columns[1:])
# ++++++++++++++ 查询天气状态 +++++++++++++++++++++++++++++++++++
# ++++++++++++++ 查询室内状态 +++++++++++++++++++++++++++++++++++
dev_ids = self._CollectInHomeDeviceID()
dev_DataFrames = []
for k, dev_id in enumerate(dev_ids):
# 生成每一个设备对应的SQL语句
individualDevSQL = self._QueryInHomeState % (dev_id)
# 从SQL语句中抽取所抽取的字段
columnsStr = re.findall(columnsPattern, individualDevSQL)[0]
# 对字段名称进行修正
columns = columnsStr.strip().split(',')
columns = [c.strip()[2:] for c in columns]
if len(self._InHomeColumnsMap()) != 0:
columns = [self._InHomeColumnsMap()[c] for c in columns]
columns = [u'%d#传感器%s' % (k, c) for c in columns]
# 游标执行查询室内状态数据的语句
conn.cursor.execute(individualDevSQL)
tempInHomeState = np.array(conn.cursor.fetchall())
try:
modeifiedTime = [
tools_DateTimeTrans(i) for i in tempInHomeState[:, 0]
]
InHomeData = pd.DataFrame(np.array(tempInHomeState[:, 1:],
np.float32),
index=modeifiedTime,
columns=columns[1:])
dev_DataFrames.append(InHomeData)
except IndexError:
print u'%d#室内传感器无数据' % k
continue
# ++++++++++++++ 查询室内状态 +++++++++++++++++++++++++++++++++++
# ++++++++++++++ 查询换热站状态 +++++++++++++++++++++++++++++++++
if self._QueryHRZState != False:
# 从SQL语句中分析所需要抓取的字段名模式
columnsStr = re.findall(columnsPattern, self._QueryHRZState)[0]
columns = columnsStr.strip().split(',')
columns = [c.strip()[2:] for c in columns]
# 对字段名进行修正
if len(self._HRZColumnsMap()) != 0:
columns = [self._HRZColumnsMap()[c] for c in columns]
# 游标执行查询天气状态数据的语句
conn.cursor.execute(self._QueryHRZState)
tempHRZState = np.array(conn.cursor.fetchall())
modifiedTime = [tools_DateTimeTrans(i) for i in tempHRZState[:, 0]]
HRZData = pd.DataFrame(np.array(tempHRZState[:, 1:], np.float32),
index=modifiedTime,
columns=columns[1:])
else:
HRZData = pd.DataFrame()
totalData = self.concatData(BoilerData, WeatherData, dev_DataFrames,
HRZData)
return totalData
import argparse
from MySQLConnector import MySQLConnector
if __name__ == '__main__':
parser = argparse.ArgumentParser()
parser.add_argument('-host', type=str, help='host ip/domain')
parser.add_argument('-id', type=str, help='id')
parser.add_argument('-db', type=str, help='Database name')
parser.add_argument('-verbose',
action='store_true',
default=False,
help='verbose option')
args = parser.parse_args()
conn = MySQLConnector(args.host, args.id, args.db, verbose=args.verbose)
while True:
print(
"===================================================================="
)
print(
"q : query, qs : query with string, gs : get settings, p : print history, c : clear history"
)
print(
"gh : get history, ghd : get history dict, ec : export csv, ep : export pickle, ri : remove invalid"
)
print("quit : quit")
a = input("opcode > ")
print(
"--------------------------------------------------------------------"
def __init__(self, date):
self.date = date
self.articles = []
self.connector = MySQLConnector()
leancloud.init("U83hlMObhFRFRS4kX3lOxSlq-gzGzoHsz", "Jw2Y6KFFsjI5kEz1qYqQ62da")
logging.basicConfig(level=logging.DEBUG)
