def query_obj_by_date2(userid, searchdate, limit): sql = ( "select date, category, branding, notes, amount, memo from expenses" + " where userid='" + userid + "' and left(date, 10) like'%" + searchdate + "%' order by date desc limit " + str(limit) ) # print sql conn = MySQLdb.connect( config.DB_HOST, config.DB_USERNAME, config.DB_PWD, config.DB_NAME, charset="utf8mb4", port=config.DB_PORT ) cursor = conn.cursor() log.info(sql) cursor.execute(sql) out = cursor.fetchall() cursor.close() objs = [] for o in out: expense = Expense() expense.init_with_mysql(userid, amount=o[4], category=o[1], branding=o[2], date=o[0], notes=o[3], memo=o[5]) objs.append(expense) return objs
def get_expense_msg(userid, category): sql = "select count(1), avg(amount) from expenses " + \ "where date > DATE_ADD(CURDATE(), INTERVAL -30 DAY) " +\ "and userid='" + userid + "' and category='" + category + "'" conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \ config.DB_NAME, charset='utf8mb4', port=config.DB_PORT) cursor = conn.cursor() log.info(sql) cursor.execute(sql) out = cursor.fetchall() user_count = out[0][0] user_amount = out[0][1] sql = "select count(1)/count(distinct userid), avg(amount) from expenses " + \ "where userid not in ('obF30jhXJFKf9ak6_KlhF84WhmKc', 'obF30jlR2x9xmh6a5pMF-ZIYtzQY') and " + \ "date > DATE_ADD(CURDATE(), INTERVAL -30 DAY) and category='" + category + "'" conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \ config.DB_NAME, charset='utf8mb4', port=config.DB_PORT) cursor = conn.cursor() log.info(sql) cursor.execute(sql) out = cursor.fetchall() avg_count = out[0][0] avg_amount = out[0][1] cursor.close() return (user_count, user_amount, avg_count, avg_amount)
def get_total_amount(db_cursor, userid, from_date_str, end_date_str): sql = "select sum(amount) from expenses where userid=\"%s\" and date>='%s' and date<='%s'" \ % (userid, from_date_str, end_date_str) log.info("SQL: get_total_amount: " + sql) print sql db_cursor.execute(sql) out = db_cursor.fetchall() if out[0][0] is None: return config.AMOUNT_NONE else: return float(out[0][0])
def query_obj_by_date(userid, fromdate, enddate): sql = "select expenseid, category, date, amount, notes, memo from expenses" + \ " where userid='" + userid +"' and date >='" +\ fromdate + "' and date<='" + enddate + "' order by date desc" conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \ config.DB_NAME, charset='utf8mb4', port=config.DB_PORT) cursor = conn.cursor() log.info(sql) cursor.execute(sql) out = cursor.fetchall() cursor.close() return out
def _response_single_picture_msg(msg, title, description, picurl, url): """ Sample: --- <xml> <ToUserName><![CDATA[toUser]]></ToUserName> <FromUserName><![CDATA[fromUser]]></FromUserName> <CreateTime>12345678</CreateTime> <MsgType><![CDATA[news]]></MsgType> <ArticleCount>1</ArticleCount> <Articles> <item> <Title><![CDATA[title1]]></Title> <Description><![CDATA[description1]]></Description> <PicUrl><![CDATA[picurl]]></PicUrl> <Url><![CDATA[url]]></Url> </item> </Articles> <FuncFlag>1</FuncFlag> </xml> """ msg["MsgType"] = "news" FromUserName = msg["ToUserName"] msg["ToUserName"] = msg["FromUserName"] msg["FromUserName"] = FromUserName xml = '<xml>' for k in ['ToUserName', 'FromUserName', 'CreateTime', 'MsgType']: xml += '<%s>%s</%s>' % (k, _cdata(msg[k]), k) # add article s = u"""%s <ArticleCount>1</ArticleCount> <Articles> <item> <Title>%s</Title> <Description>%s</Description> <PicUrl>%s</PicUrl> <Url>%s</Url> </item> </Articles> </xml> """ % (xml, title, description, picurl, url) log.info(s.encode('utf-8', 'ignore')) return s
def insert_expense(expense): conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \ config.DB_NAME, charset='utf8mb4', port=config.DB_PORT) cursor = conn.cursor() sql = Expense.generate_insert_sql(expense) log.info("SQL:" + sql) #print sql try: cursor.execute(sql) conn.commit() except MySQLdb.Error: log.error("SQL fail: %s" % sql) conn.rollback() cursor.close() # TODO: defensive check return True
def query_obj_by_date2(userid, searchdate, limit): sql = "select date, category, branding, notes, amount, memo from expenses" + \ " where userid='" + userid +"' and left(date, 10) like'%" +\ searchdate + "%' order by date desc limit " + str(limit) #print sql conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \ config.DB_NAME, charset='utf8mb4', port=config.DB_PORT) cursor = conn.cursor() log.info(sql) cursor.execute(sql) out = cursor.fetchall() cursor.close() objs = [] for o in out: expense = Expense() expense.init_with_mysql(userid, amount=o[4], category=o[1], \ branding = o[2], date=o[0], notes=o[3], memo=o[5]) objs.append(expense) return objs
def get_expense_msg(userid, category): sql = ( "select count(1), avg(amount) from expenses " + "where date > DATE_ADD(CURDATE(), INTERVAL -30 DAY) " + "and userid='" + userid + "' and category='" + category + "'" ) conn = MySQLdb.connect( config.DB_HOST, config.DB_USERNAME, config.DB_PWD, config.DB_NAME, charset="utf8mb4", port=config.DB_PORT ) cursor = conn.cursor() log.info(sql) cursor.execute(sql) out = cursor.fetchall() user_count = out[0][0] user_amount = out[0][1] sql = ( "select count(1)/count(distinct userid), avg(amount) from expenses " + "where userid not in ('obF30jhXJFKf9ak6_KlhF84WhmKc', 'obF30jlR2x9xmh6a5pMF-ZIYtzQY') and " + "date > DATE_ADD(CURDATE(), INTERVAL -30 DAY) and category='" + category + "'" ) conn = MySQLdb.connect( config.DB_HOST, config.DB_USERNAME, config.DB_PWD, config.DB_NAME, charset="utf8mb4", port=config.DB_PORT ) cursor = conn.cursor() log.info(sql) cursor.execute(sql) out = cursor.fetchall() avg_count = out[0][0] avg_amount = out[0][1] cursor.close() return (user_count, user_amount, avg_count, avg_amount)
def query_obj_by_date(userid, fromdate, enddate): sql = ( "select expenseid, category, date, amount, notes, memo from expenses" + " where userid='" + userid + "' and date >='" + fromdate + "' and date<='" + enddate + "' order by date desc" ) conn = MySQLdb.connect( config.DB_HOST, config.DB_USERNAME, config.DB_PWD, config.DB_NAME, charset="utf8mb4", port=config.DB_PORT ) cursor = conn.cursor() log.info(sql) cursor.execute(sql) out = cursor.fetchall() cursor.close() return out
def delete_obj_by_id(userid, expenseid): if expenseid != "": sql = "delete from expenses where userid='" + userid + "' and expenseid=" + expenseid else: sql = "delete from expenses where userid='" + userid + "'" conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, config.DB_PWD, \ config.DB_NAME, charset='utf8mb4', port=config.DB_PORT) log.info(sql) cursor = conn.cursor() # TODO: report error try: cursor.execute(sql) conn.commit() except MySQLdb.Error: log.error("ERROR delete mysql fail") conn.rollback() cursor.close() return True
def delete_obj_by_id(userid, expenseid): if expenseid != "": sql = "delete from expenses where userid='" + userid + "' and expenseid=" + expenseid else: sql = "delete from expenses where userid='" + userid + "'" conn = MySQLdb.connect( config.DB_HOST, config.DB_USERNAME, config.DB_PWD, config.DB_NAME, charset="utf8mb4", port=config.DB_PORT ) log.info(sql) cursor = conn.cursor() # TODO: report error try: cursor.execute(sql) conn.commit() except MySQLdb.Error: log.error("ERROR delete mysql fail") conn.rollback() cursor.close() return True
def word_split(sentence, shall_print=0): # python word split is incorrect for float, for example,"16.5 中午吃饭", # will be splited as "16 5 中午吃饭", instead of "16.5 中午 吃饭" SPECIAL_CHARACTER_FOR_FLOAT='a' sentence=sentence.replace('。', SPECIAL_CHARACTER_FOR_FLOAT) sentence=sentence.replace('.', SPECIAL_CHARACTER_FOR_FLOAT) algor = mmseg.Algorithm(sentence) token_list = [] for tok in algor: if tok.text.replace(SPECIAL_CHARACTER_FOR_FLOAT, '0').isdigit(): token_list.append((tok.text.replace(SPECIAL_CHARACTER_FOR_FLOAT, '.'), \ tok.start, tok.end)) else: token_list.append((tok.text, tok.start, tok.end)) # temporarily print for text, start, end in token_list: if shall_print == 1: log.info("%s, %d, %d" % (text, start, end)) else: if shall_print == 2: print "%s, %d, %d" % (text, start, end) return token_list
def word_split(sentence, shall_print=0): # python word split is incorrect for float, for example,"16.5 中午吃饭", # will be splited as "16 5 中午吃饭", instead of "16.5 中午 吃饭" SPECIAL_CHARACTER_FOR_FLOAT = 'a' sentence = sentence.replace('。', SPECIAL_CHARACTER_FOR_FLOAT) sentence = sentence.replace('.', SPECIAL_CHARACTER_FOR_FLOAT) algor = mmseg.Algorithm(sentence) token_list = [] for tok in algor: if tok.text.replace(SPECIAL_CHARACTER_FOR_FLOAT, '0').isdigit(): token_list.append((tok.text.replace(SPECIAL_CHARACTER_FOR_FLOAT, '.'), \ tok.start, tok.end)) else: token_list.append((tok.text, tok.start, tok.end)) # temporarily print for text, start, end in token_list: if shall_print == 1: log.info("%s, %d, %d" % (text, start, end)) else: if shall_print == 2: print "%s, %d, %d" % (text, start, end) return token_list