def loadingSegPosData(self, brand, index):
comm_type = 'PRAISE:' if (index % 2 == 0) else 'BAD:'
print(comm_type, brand, 'seg and pos data loading...')
data = dbConnect(self.segPosTableNameList[index])
row = len(data) # 表格行数
vol = 1 # 表格列数
# 设置数据层次结构，10行2列
self.model = QStandardItemModel(10, 1)
# 设置水平方向四个头标签文本内容
self.model.setHorizontalHeaderLabels(['评论'])
for i in range(row):
for j in range(vol):
# 只展示“评论”列的数据
temp_data = data[i][j + 1] # 临时记录，不能直接插入表格
item = QStandardItem(str(temp_data))
self.model.setItem(i, j, item)
self.segPosTable[index].setModel(self.model)
if brand == 'lenovo':
self.stackedWidgetContentLenovo.setVisible(True)
else:
self.stackedWidgetContentHp.setVisible(True)
'''表格样式'''
# 设置行样式
self.segPosTable[index].setAlternatingRowColors(True)
# 隐藏横向滚动条
self.segPosTable[index].setHorizontalScrollBarPolicy(False)
# 所有列自动拉伸，充满界面，适用于列数较多且内容合适的表格model
self.segPosTable[index].horizontalHeader().setStretchLastSection(True)
self.segPosTable[index].horizontalHeader().setSectionResizeMode(
QHeaderView.Stretch)
def excelUpload():
"""Handle excel data file uploads."""
conn = dbConnect()
plist = 'nothing posted'
if request.method == 'POST':
wb = load_workbook(request.files['excelUpload'])
ws = wb["points"]
playerIDs = []
with conn:
with conn.cursor() as cur:
for nameCell in ws.rows[0][1:]:
name = nameCell.value
cur.execute("""SELECT player_id FROM players
WHERE name = %s""",
(name,))
playerID = cur.fetchone()
if playerID is not None:
playerIDs.append(playerID[0])
else:
cur.execute("""INSERT INTO players (name,join_date)
VALUES (%s,%s) RETURNING player_id;""",
(name, datetime.date.today()))
playerID = cur.fetchone()
playerIDs.append(playerID[0])
with conn:
with conn.cursor() as cur:
for game in ws.rows[1:]:
cur.execute("""INSERT INTO games (game_date)
VALUES (%s) RETURNING game_id""",
(game[0].value,))
gameID = cur.fetchone()[0]
scoreData = []
for col, pID in enumerate(playerIDs, 1):
points = game[col].value
if points is not None:
scoreData.append((pID, gameID, points))
cur.executemany("""INSERT INTO
scores (player_id,game_id,points)
VALUES (%s,%s,%s)""",
scoreData)
plist = 'data loaded.'
return render_template('excel.html', p1=plist)
def player(player_id):
"""Show profile page for a player."""
conn = dbConnect()
with conn:
with conn.cursor() as cur:
cur.execute("SELECT name FROM players WHERE player_id = %s",
(player_id,))
name = cur.fetchone()[0]
with conn.cursor() as cur:
cur.execute("""SELECT game_id, game_date, points
FROM datedScores WHERE player_id = %s""",
(player_id,))
games = cur.fetchall()
return render_template("player.html", name=name, games=games)
def game(game_id):
"""Show summary page for a game."""
conn = dbConnect()
with conn:
with conn.cursor() as cur:
cur.execute("SELECT game_date FROM games WHERE game_id = %s",
(game_id,))
date = cur.fetchone()[0]
with conn.cursor() as cur:
cur.execute("""SELECT player_id, name, score
FROM scores INNER JOIN players ON player_id
WHERE game_id = %s""",
(game_id,))
scores = cur.fetchall()
return render_template("game.html", date=date, scores=scores)
def __init__(self, directory: str) -> None:
load_dotenv()
self.db_config = {
"username": environ.get("dbUsername"),
"hostname": environ.get("dbHostname"),
"password": environ.get("dbPassword"),
"port": environ.get("dbPort"),
"database": environ.get("databaseName"),
"table_name": "Challenges"
}
self.member_set: Set[str] = set()
self.db_obj = dbConnect(self.db_config)
self.storage_directory = directory
# call the upload challenge function
self.uploadChallenges(self.storage_directory)
# Remove existing members from the set
self.check_unique_members(self.member_set)
# call the member upload function
self.upload_members(self.member_set)
def data():
"""Score data for 2015-8-1 to 2015-11-4.
data is of format:
{ playerID(int):[
[gameTime(str), score(int)]
]
}
"""
conn = dbConnect()
date1 = datetime.date(2015, 8, 1)
date2 = datetime.date(2015, 11, 4)
plist = None
data = None
with conn:
with conn.cursor() as cur:
# get players that were active in the time period
cur.execute("""SELECT DISTINCT player_id
FROM datedScores
WHERE game_date > %s
AND game_date < %s """,
(date1, date2))
plist = [x[0] for x in cur.fetchall()]
with conn.cursor() as cur:
# get scores for active players
data = {}
for p in plist:
cur.execute("""SELECT game_date, points
FROM datedScores
WHERE player_id = %s """,
(p,))
data[p] = cur.fetchall()
return jsonify(data)
def players():
"""Serve the players page."""
conn = dbConnect()
# adding players to the list
if request.method == 'POST':
np = request.form['p1']
if np is not None:
with conn:
with conn.cursor() as cur:
cur.execute("""INSERT INTO players (name,join_date)
VALUES (%s,%s);""",
(np, datetime.date.today()))
with conn:
with conn.cursor() as cur:
cur.execute("SELECT player_id, name FROM players;")
plist = cur.fetchall() # data returned as tuples
return render_template('players.html', plist=plist)
def scoreTableDataFetcher():
"""Return all scores for each player, along with game and player lists."""
conn = dbConnect()
with conn:
with conn.cursor() as cur:
# get a list of all games
cur.execute("SELECT game_id, game_date FROM games")
games = cur.fetchall()
with conn.cursor() as cur:
# get a list of all players
cur.execute("SELECT player_id, name FROM players")
players = cur.fetchall()
with conn.cursor() as cur:
# get a list of all scores
cur.execute("SELECT game_id, player_id, points FROM scores")
scores = cur.fetchall()
data = {"games": games, "players": players, "scores": scores}
return jsonify(data)
def handleWebRequest(emailId):
if emailId is not None:
dbConn = dbConnect.dbConnect(siteConfig.dbHost, siteConfig.dbUser, siteConfig.dbPass, siteConfig.dbName, True)
mailProcessor = eMailProcessor.eMailProcessor(gMailRepoRoot, gMailRepoType, siteConfig.debugEnabled)
dbConn.dbConnect()
userInfo = dbConn.fetchUserAccessToken(emailId)
'''
Set inProgress to True in database.
Fetch Emails.
Process EMails.
Set inProgress to False in database.
'''
progressInfo = dbConn.getProgressInfo(emailId)
if progressInfo['inProgress'] == 0 or progressInfo['inProgress'] is None:
# dbConn.setProgressInfo(emailId, True)
strDateTime = None
if progressInfo['fetchDate'] is not None:
dateTime = progressInfo['fetchDate']
month = dateTime.strftime("%B")[0:3]
year = dateTime.strftime("%Y")
day = dateTime.strftime("%d")
strDateTime = day + '-' + month + '-' + year
try:
#downloadMails(emailId, userInfo['oauthToken'], userInfo['oauthSecret'], strDateTime)
mailProcessor.processEMails(emailId)
except:
debugTraceInst.doPrintTrace(errorStringsInst.getFailedRequestError(), sys.exc_info()[2])
#dbConn.setProgressInfo(emailId, False, 100)
dbConn.dbDisconnect()
#joining the tweets df with the scores df
sentScore = pd.DataFrame.from_dict(scores)
df = df.join(sentScore)
#find average of sentiment scores for a given stock
scoreAvg = df['compound'].mean()
scoreAvg = round(scoreAvg, 5)
scoreList.append({'stock':stock, 'score': scoreAvg})
#if tweepy returns a none type for a given query, move to next stock
except KeyError:
continue
#clear lists for next stock
tickList.clear()
scores.clear()
searched_tweets.clear()
new_tweets.clear()
dbConnect.dbConnect(scoreList)
#remaining rate limit from twitter API
limits = api.rate_limit_status()
remain_search_limits = limits['resources']['search']['/search/tweets']['remaining']
print('-----------------------------------------------------------------')
print ('Remaining Rate', remain_search_limits)
## IMPORT ##
import pymongo
import yelpExecute
import dbConnect
import sentence
############
db = dbConnect.dbConnect()
reviews = sentence.createReviewList("EAwh1OmG6t6p3nRaZOW_AA", db)
yelpExecute.yelpExecute(reviews)
from dbConnect import dbConnect
with dbConnect() as conn:
with conn.cursor() as cur:
cur.execute("TRUNCATE players,games,scores")
print("cleared data from tables.")
dbConn.dbDisconnect()
if __name__ == '__main__':
'''
Check if the request is triggered from the Web.
If so, hand it over to appropriate handler.
'''
if len(sys.argv) >1:
handleWebRequest(sys.argv[1])
else:
'''
Issue Fetch requests every 24 hour.
'''
dbConn = dbConnect.dbConnect(siteConfig.dbHost, siteConfig.dbUser, siteConfig.dbPass, siteConfig.dbName, True)
mailProcessor = eMailProcessor.eMailProcessor(gMailRepoRoot, gMailRepoType, siteConfig.debugEnabled)
while 1:
dbConn.dbConnect()
userInfoList = dbConn.fetchAllAccessTokens()
print 'Download Started at: %d'%(time.time())
for userInfo in userInfoList:
'''
Set inProgress to True in database.
Fetch Emails.
Process EMails.
Set inProgress to False in database.
'''
