def main():
parser = argparse.ArgumentParser()
parser.add_argument(
"-g",
"--get",
type=str,
help=
"Print the document in database if exists, otherwise crawl it and then print"
)
parser.add_argument("-c",
"--crawl",
type=str,
help="Crawl one's total answer pictures")
parser.add_argument("-f",
"--find",
type=str,
help="Print the document in database")
parser.add_argument("-d",
"--delete",
type=str,
help="delete one document from database")
args = parser.parse_args()
db_connection = DBConnection()
if args.crawl is not None:
crawl_one(args.crawl, db_connection, COOKIES_STR)
if args.find is not None:
pprint(db_connection.find_one(args.find))
if args.get is not None:
pprint(get_one(args.get, db_connection, COOKIES_STR))
if args.delete is not None:
print(db_connection.delete_many(args.delete))
def __init__(self):
self.bag_of_words = [
'modi', 'pm', 'visit', 'narendra', 'prime minister'
]
self.bag_of_words_set = set(self.bag_of_words)
self.db_con = DBConnection().create_connection()
self.cursor = self.db_con.cursor()
def init():
print("Begin initialize...")
db_connection = DBConnection()
url_token_set = db_connection.restore_url_token()
origin = Author(ORIGIN_URL_TOKEN, COOKIES_STR, ORIGIN_NAME, ORIGIN_GENDER, ORIGIN_AVATAR_URL_TEMPLATE)
return db_connection, url_token_set, origin
def update(self):
if self.lastind is None: return
dataind = self.lastind
self.ax2.cla()
self.ax3.cla()
dt = datetime.date.fromtimestamp(self.xs[dataind])
db = DBConnection()
match = db.query("SELECT `date`, sum(`calls_in`), sum(`dur_in`) FROM `hourly_tower_activity` WHERE `date`='"+str(dt.year) + ('0'+str(dt.month) if dt.month < 10 else str(dt.month)) + ('0'+str(dt.day) if dt.day < 10 else str(dt.day))+"' GROUP BY `hour`")
clean, clean2 = [], []
for entry in match:
#print entry
clean.append(int(entry[1]))
clean2.append(int(entry[2]))
#self.ax2.plot(range(0, 24), clean, '-')
self.ax2.bar(range(0, 24), clean)
self.ax3.bar(range(0, 24), clean2)
self.ax2.text(0.05, 0.9, str(dt.year)+'-'+str(dt.month)+'-'+str(dt.day),
transform=self.ax2.transAxes, va='top')
self.selected.set_visible(True)
self.selected.set_data(self.xs[dataind], self.ys[dataind])
self.text.set_text('selected: %d'%dataind)
self.fig.canvas.draw()
show()
def init():
print("Begin initialize...")
db_connection = DBConnection()
url_token_set = db_connection.restore_url_token()
origin = Author(ORIGIN_URL_TOKEN, COOKIES_STR, ORIGIN_NAME, ORIGIN_GENDER,
ORIGIN_AVATAR_URL_TEMPLATE)
return db_connection, url_token_set, origin
def __init__(self):
self.year = 2015
self.base_url = "http://timesofindia.indiatimes.com/%s"
self.url_to_scrape = None
self.db_con = DBConnection().create_connection()
self.cursor = self.db_con.cursor()
self.request_headers = {
"User-Agent":
"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36"
}
def display_vist_details():
db_con = DBConnection().create_connection()
cursor = db_con.cursor()
sql = "select place, visit_date from visit_info"
cursor.execute(sql)
rs_tuple_list = cursor.fetchall()
value_dict = {}
for rs_tuple in rs_tuple_list:
(place, date_of_visit) = rs_tuple
value_dict[place] = date_of_visit
return render_template('index.html', vist_details=value_dict)
def hello_world():
db_con = DBConnection().create_connection()
cursor = db_con.cursor()
sql = "select place, visit_date from visit_info"
cursor.execute(sql)
rs_tuple_list = cursor.fetchall()
#return jsonify(rs_tuple_list)
value_dict = {}
for rs_tuple in rs_tuple_list:
(place, date_of_visit) = rs_tuple
value_dict[place] = date_of_visit
#return 'Hello World'
return jsonify(value_dict)
def Search_Like(keyword):
list = []
# 打开数据库连接
db = DBConnection.connection()
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
cursor_name = db.cursor()
# SQL查找语句
sql = "select * from user where Wnumber like '%" + keyword + "%' and Wnumber != '-1'"
print(keyword)
sql_name = "select * from user where userName like '%" + keyword + "%' and Wnumber != '-1'"
try:
# 执行sql语句
cursor.execute(sql)
# 获取所有记录列表
result = cursor.fetchall()
for row in result:
list.append(row)
# 执行sql语句
cursor_name.execute(sql_name)
result = cursor_name.fetchall()
for row in result:
print(row)
list.append(row)
except:
# 如果发生错误则回滚
db.rollback()
# 关闭数据库连接
db.close()
return list
def new(self,
data,
telegram_id,
complain_type=None,
chat_id=None,
message_id=None):
photo = None
if data['photo']:
photo = data['photo']
del data['photo']
if photo:
pass
if complain_type == 'digipos':
cnx = DBConnection.get_connection().connection()
cursor = cnx.cursor()
query_save_complain = (
"INSERT INTO complain_digipos "
"(complain_id, kabupaten, telegram_id, "
"kecamatan, id_outlet, nama_outlet, no_mkios, no_pelanggan, "
"tgl_transaksi, detail, pay_method, versi_apk_dipos, channel_lain, "
"chat_id, message_id) "
f'VALUES ("{self.complain_id}", %(kabupaten)s, {telegram_id}, %(kecamatan)s, '
f'%(id_outlet)s, %(nama_outlet)s, %(no_mkios)s, %(no_pelanggan)s, '
f'%(tgl_transaksi)s, %(detail)s, %(pay_method)s, %(versi_apk_dipos)s, '
f'%(channel_lain)s, "{chat_id}", "{message_id}")')
cursor.execute(query_save_complain, data)
cnx.commit()
data['photo'] = photo
else:
raise KeyError
def SignUpSearch(lwnumber):
list = []
# 打开数据库连接
db = DBConnection.connection()
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# SQL查找语句'
sql = "select * from user where Wnumber = '-1' and LWnumber = '%d'" % (
lwnumber)
try:
# 执行sql语句
cursor.execute(sql)
# 获取所有记录列表
result = cursor.fetchall()
for row in result:
list.append(row)
except:
# 如果发生错误则回滚
db.rollback()
# 关闭数据库连接
db.close()
return list
def SignUpAgree(userid, Wnumber, lwnumber):
# 打开数据库连接
db = DBConnection.connection()
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# SQL更新语句
sql = "update user set Wnumber='%d' , LWnumber = '%d' where userid='%d' " % \
(Wnumber,lwnumber,userid)
print(sql)
try:
# 执行sql语句
cursor.execute(sql)
#提交到数据库执行
db.commit()
return True
except:
# 如果发生错误则回滚
db.rollback()
return False
# 关闭数据库连接
db.close()
def insert(Tname, Tcontent, PWnumber, RWnumber):
# Wnumber = int(Wnumber)
# 打开数据库连接
db = DBConnection.connection()
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
#获取当前时间
#SQL插入语句
sql = "insert into task (name,content,PWnumber,RWnumber) \
values('%s','%s','%d','%d')" % \
(Tname,Tcontent,PWnumber,RWnumber)
try:
#执行sql语句
cursor.execute(sql)
#提交到数据库执行
db.commit()
return "成功"
except:
#如果发生错误则回滚
db.rollback()
return "失败"
#关闭数据库连接
db.close()
def CheckSub(LWnumber):
list = []
# 打开数据库连接
db = DBConnection.connection()
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# SQL查找语句
sql = "select userName, Wnumber from user where LWnumber = '%d'" % (
LWnumber)
try:
# 执行sql语句
cursor.execute(sql)
# 获取所有记录列表
result = cursor.fetchall()
for row in result:
list.append(row)
except:
# 如果发生错误则回滚
db.rollback()
return None
# 关闭数据库连接
db.close()
return list
def new(self,
data,
telegram_id,
complain_type=None,
chat_id=None,
message_id=None):
photo = None
if data['photo']:
photo = data['photo']
del data['photo']
if photo:
pass
if complain_type == 'voucher_fisik':
cnx = DBConnection.get_connection().connection()
cursor = cnx.cursor()
query_save_complain = (
"INSERT INTO complain_voucher_fisik "
"(complain_id, telegram_id, kabupaten, "
"kecamatan, nama_outlet, id_digipos_outlet, nomor_pelanggan, "
"serial_number, tgl_inject_voucher, paket, masalah, chat_id, "
"message_id) "
f'VALUES ("{self.complain_id}", {telegram_id}, %(kabupaten)s, %(kecamatan)s, '
f'%(nama_outlet)s, %(id_digipos_outlet)s, %(nomor_pelanggan)s, '
f'%(serial_number)s, %(tgl_inject_voucher)s, %(paket)s, %(masalah)s, '
f'"{chat_id}", "{message_id}")')
cursor.execute(query_save_complain, data)
cnx.commit()
data['photo'] = photo
else:
raise KeyError
def get(self):
cnx = DBConnection.get_connection().connection()
cursor = cnx.cursor()
cursor.execute(
f"SELECT * FROM complain_digipos where complain_id='{self.complain_id}'"
)
complain_result = cursor.fetchone()
if complain_result:
self.id = complain_result[0]
# self.complain_id = complain_result[1]
self.status = complain_result[2]
self.kabupaten = complain_result[3]
self.telegram_id = complain_result[4]
self.kecamatan = complain_result[5]
self.id_outlet = complain_result[6]
self.nama_outlet = complain_result[7]
self.no_mkios = complain_result[8]
self.no_pelanggan = complain_result[9]
self.tgl_transaksi = complain_result[10]
self.detail = complain_result[11]
self.pay_method = complain_result[12]
self.versi_apk_dipos = complain_result[13]
self.channel_lain = complain_result[14]
self.photo = complain_result[15]
self.chat_id = complain_result[16]
self.message_id = complain_result[17]
self.handler_user_id = complain_result[18]
self.created = complain_result[19]
return self
return self
def get(self):
cnx = DBConnection.get_connection().connection()
cursor = cnx.cursor()
cursor.execute(
f"SELECT * FROM complain_voucher_fisik where complain_id='{self.complain_id}'"
)
complain_result = cursor.fetchone()
if complain_result:
self.id = complain_result[0]
# self.complain_id = complain_id
self.telegram_id = complain_result[2]
self.status = complain_result[3]
self.kabupaten = complain_result[4]
self.kecamatan = complain_result[5]
self.nama_outlet = complain_result[6]
self.id_digipos_outlet = complain_result[7]
self.nomor_pelanggan = complain_result[8]
self.serial_number = complain_result[9]
self.tgl_inject_voucher = complain_result[10]
self.paket = complain_result[11]
self.masalah = complain_result[12]
# self.photo = complain_result[13]
self.chat_id = complain_result[14]
self.message_id = complain_result[15]
self.handler_user_id = complain_result[16]
self.created = complain_result[17]
return self
return self
def set_status(self, status: str, admin_id: str):
cnx = DBConnection.get_connection().connection()
cursor = cnx.cursor()
sql = f"UPDATE complain_registrasi_perdana SET status = '{status}', handler_user_id = '{admin_id}' " \
f"WHERE complain_id = '{self.complain_id}'"
cursor.execute(sql)
cnx.commit()
def insert(LWnumber, password, userName, Wnumber=-1):
flag = False
# 打开数据库连接
db = DBConnection.connection()
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# userName = userName.encode('utf-8')
#SQL插入语句
sql = "insert into user(password,userName,Wnumber,LWnumber)\
values('%s',\"%s\",'%d','%d')" % \
(password,userName,Wnumber,LWnumber)
print(sql)
try:
#执行sql语句
cursor.execute(sql)
#提交到数据库执行
db.commit()
flag = True
except:
#如果发生错误则回滚
db.rollback()
#关闭数据库连接
db.close()
return flag
async def add_user(user: dict):
cnx = DBConnection.get_connection().connection()
cursor = cnx.cursor(buffered=True)
query = ("INSERT INTO user "
"(telegram_id, telegram_username, kabupaten, kecamatan, nama_outlet, nomor_mkios) "
"VALUES (%(telegram_id)s, '', %(kabupaten)s, %(kecamatan)s, %(nama_outlet)s, %(nomor_mkios)s)")
cursor.execute(query, user)
cnx.commit()
async def order_barang(order: Order):
cnx = DBConnection.get_connection().connection()
cursor = cnx.cursor()
query = ("INSERT INTO orderan "
"(telegram_id, kode_barang, qty) "
"VALUES (%(telegram_id)s, %(kode_barang)s, %(qty)s)")
cursor.execute(query, order.get())
cnx.commit()
def main():
max_api_calls = api["max_api_calls"]
urls_per_call = api["urls_per_call"]
isProcessed = False
endCursor = urls_per_call
listingIds = []
queryListingsList = []
apiCallCount = 0
listingCount = 0
listEndReached = True
db = DBConnection(database["host"], database["port"])
db.set_database(database["name"])
db.set_collection(database["collection"])
records = db.get_records()
for index, record in enumerate(records):
try:
if record["offer"]["listing"]["is_processed"] == True:
isProcessed = True
else:
isProcessed = False
except KeyError:
isProcessed = False
if not isProcessed:
listingIds.append(record["offer"]["listing"]["listingId"])
listingCount += 1
if listingCount == endCursor:
queryListingsList.append(listingIds)
listingIds = []
endCursor += urls_per_call
apiCallCount += 1
if apiCallCount >= max_api_calls:
listEndReached = False
break
if listEndReached:
queryListingsList.append(listingIds)
with concurrent.futures.ThreadPoolExecutor() as executor:
executor.map(process_listing_metric_data, queryListingsList,
repeat(db), repeat(urls_per_call))
if listEndReached:
db.check_listings()
class Classifier:
def __init__(self):
self.bag_of_words = [
'modi', 'pm', 'visit', 'narendra', 'prime minister'
]
self.db_con = DBConnection().create_connection()
self.cursor = self.db_con.cursor()
def classify_title(self):
sql = "SELECT id, title FROM news_url WHERE id > %s ORDER BY id ASC "
sql_update = "UPDATE news_url SET is_valid = True WHERE id = %s"
news_url_id = self.get_last_news_url_id()
counter = 0
while True:
self.cursor.execute(sql, (news_url_id, ))
rs_tuple = self.cursor.fetchone()
if not rs_tuple:
break
(news_url_id, title) = rs_tuple
print(news_url_id)
for key_word in self.bag_of_words:
if re.search(key_word, title.strip(), re.I):
self.cursor.execute(sql_update, (news_url_id, ))
self.db_con.commit()
counter += 1
if counter % 1000 == 0:
print('Sleeping for 5 seconds')
time.sleep(5)
self.update_last_news_url_id(news_url_id)
def update_last_news_url_id(self, news_url_id):
sql = "UPDATE scraper_info SET news_url_id = %s WHERE id = 1"
self.cursor.execute(sql, (news_url_id, ))
self.db_con.commit()
def get_last_news_url_id(self):
sql = "SELECT news_url_id FROM scraper_info"
self.cursor.execute(sql)
rs_tuple = self.cursor.fetchone()
(news_url_id, ) = rs_tuple
return news_url_id
def main():
parser = argparse.ArgumentParser()
parser.add_argument("-g", "--get", type=str,
help="Print the document in database if exists, otherwise crawl it and then print")
parser.add_argument("-c", "--crawl", type=str,
help="Crawl one's total answer pictures")
parser.add_argument("-f", "--find", type=str,
help="Print the document in database")
parser.add_argument("-d", "--delete", type=str,
help="delete one document from database")
args = parser.parse_args()
db_connection = DBConnection()
if args.crawl is not None:
crawl_one(args.crawl, db_connection, COOKIES_STR)
if args.find is not None:
pprint(db_connection.find_one(args.find))
if args.get is not None:
pprint(get_one(args.get, db_connection, COOKIES_STR))
if args.delete is not None:
print(db_connection.delete_many(args.delete))
def main():
parameter_names = [
"hero_movespeed", "hero_attackspeed", "hero_attackrange", "hero_attackdamage", "fraction_neutral_left",
"neutral_total_eff_hp", "targeted_neutral_eff_hp", "fraction_lane_left", "lane_total_eff_hp",
"targeted_lane_eff_hp", "damage_spread_neutral", "damage_spread_lane", "success"
]
#starting_weights = [0.0033, -0.85, -0.0015, 0.02, -4, -0.003, 0.003, 1.25, 0.0005, 0, 0, 0]
db = DBConnection("doublepull", 1)
loop = Loop(parameter_names, db, 100)
loop.go()
async def get_account_by_message(
request: web.BaseRequest) -> web.json_response:
from_date = request.match_info.get('from')
to_date = request.match_info.get('to')
key_word = request.query.get('q').split(' ')
and_list = []
or_list = []
and_list.append(key_word.pop(0))
index = 0
for _ in range(0, int(len(key_word) / 2)):
if key_word[index].lower() == 'and':
and_list.append(key_word[index + 1])
if key_word[index].lower() == 'or':
or_list.append(key_word[index + 1])
index += 2
sub_query = ''
for value in and_list:
sub_query += f" AND lower(message) like '%{value.lower()}%'"
for value in or_list:
sub_query += f" OR lower(message) like '%{value.lower()}%'"
async with DBConnection(request) as connection, connection.transaction(
isolation='serializable'):
query = f"""
SELECT owner_id, owner_name, sum(engagement) as total_engagement, array_agg(id) as id_list
FROM data
WHERE time BETWEEN $1 AND $2
{sub_query}
GROUP BY owner_id,owner_name
ORDER BY total_engagement DESC
limit 10
"""
print(query)
result = await connection.fetch(
query,
arrow.get(from_date).datetime,
arrow.get(to_date).datetime,
)
if not result:
raise web.HTTPNotFound(text='daily message not found')
payload = []
for item in result:
item = dict(item)
url_id_list = []
for msg in item['id_list']:
msg = reverse('message', msg_id=msg)
url_id_list.append(msg)
item['id_list'] = url_id_list
payload.append(item)
return web.json_response(payload)
def new(self, data, telegram_id, chat_id=None, message_id=None):
cnx = DBConnection.get_connection().connection()
cursor = cnx.cursor()
query_save_complain = (
"INSERT INTO complain_registrasi_perdana "
"(complain_id, telegram_id, msisdn_or_nomor_kartu, "
"nama_lengkap, tempat_lahir, tanggal_lahir, nik, "
"no_kk, chat_id, message_id) "
f'VALUES ("{self.complain_id}", {telegram_id}, %(msisdn_or_nomor_kartu)s, %(nama_lengkap)s, '
f'%(tempat_lahir)s, %(tanggal_lahir)s, %(nik)s, '
f'%(no_kk)s, "{chat_id}", "{message_id}")')
cursor.execute(query_save_complain, data)
cnx.commit()
async def is_registered(id_user: int) -> User:
cnx = DBConnection.get_connection().connection()
cursor = cnx.cursor()
cursor.execute(f"SELECT * FROM user where telegram_id={id_user}")
user = cursor.fetchone()
user_result = User()
if user:
user_result.ok = True
user_result.telegram_id = user[0]
user_result.telegram_username = user[1]
user_result.kabupaten = user[2]
user_result.kecamatan = user[3]
user_result.nama_outlet = user[4]
user_result.nomor_mkios = user[5]
user_result.tgl_registrasi = user[6]
return user_result
return user_result
async def get_message_by_id(request: web.BaseRequest) -> web.json_response:
msg_id = request.match_info.get('msg_id')
async with DBConnection(request) as connection, connection.transaction(
isolation='serializable'):
result = await connection.fetchrow(
"""
SELECT *
FROM data
WHERE id = $1
""",
msg_id,
)
if not result:
raise web.HTTPNotFound(text='message not found')
payload = dict(result)
payload['time'] = payload['time'].isoformat()
return web.json_response(payload)
async def get_daily_message_count(
request: web.BaseRequest) -> web.json_response:
from_date = request.match_info.get('from')
to_date = request.match_info.get('to')
async with DBConnection(request) as connection, connection.transaction(
isolation='serializable'):
result = await connection.fetch(
"""
SELECT to_char(date_trunc('day', time), 'YYYY-MM-DD') as date, count(*)
FROM data
WHERE time BETWEEN $1 AND $2
GROUP BY date
ORDER BY date ASC
""",
arrow.get(from_date).datetime,
arrow.get(to_date).datetime)
if not result:
raise web.HTTPNotFound(text='daily message not found')
return web.json_response(dict(result))
async def get_word_cloud(request: web.FileResponse) -> web.json_response:
from_date = request.match_info.get('from')
to_date = request.match_info.get('to')
cloud_type = request.match_info.get('cloud_type')
if cloud_type not in ('wordcloud', 'hashtag', 'mention'):
cloud_type = 'wordcloud'
if os.path.exists(
f"{settings.MEDIA_PATH}/{cloud_type}__{from_date}__{to_date}.png"):
return web.FileResponse(
f"{settings.MEDIA_PATH}/{cloud_type}__{from_date}__{to_date}.png")
async with DBConnection(request) as connection, connection.transaction(
isolation='serializable'):
result = await connection.fetch(
"""
SELECT message
FROM data
WHERE time BETWEEN $1 AND $2
""",
arrow.get(from_date).datetime,
arrow.get(to_date).datetime)
if not result:
raise web.HTTPNotFound(text='daily message not found')
dictionary = await get_count_by_list(result, cloud_type)
wc = WordCloud(
background_color="white",
width=1000,
height=1000,
font_path='THSarabunNew.ttf',
relative_scaling=0.5,
)
wc.generate_from_frequencies(dictionary)
plt.imshow(wc, interpolation='bilinear')
plt.axis("off")
plt.savefig(
f"{settings.MEDIA_PATH}/{cloud_type}__{from_date}__{to_date}.png",
format="png")
return web.FileResponse(
f"{settings.MEDIA_PATH}/{cloud_type}__{from_date}__{to_date}.png")
def get(self):
cnx = DBConnection.get_connection().connection()
cursor = cnx.cursor()
cursor.execute(
f"SELECT * FROM complain_registrasi_perdana where complain_id='{self.complain_id}'"
)
complain_result = cursor.fetchone()
if complain_result:
self.id = complain_result[0]
self.telegram_id = complain_result[2]
self.status = complain_result[3]
self.msisdn_or_nomor_kartu = complain_result[4]
self.nama_lengkap = complain_result[5]
self.tempat_lahir = complain_result[6]
self.tanggal_lahir = complain_result[7]
self.nik = complain_result[8]
self.no_kk = complain_result[9]
self.chat_id = complain_result[10]
self.message_id = complain_result[11]
self.handler_user_id = complain_result[12]
self.created = complain_result[13]
return self
return self
def Search(Wnumber):
result = False
# 打开数据库连接
db = DBConnection.connection()
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# SQL查找语句
sql = "select * from user where Wnumber = '%d'" % (Wnumber)
try:
# 执行sql语句
cursor.execute(sql)
# 获取所有记录列表
result = cursor.fetchone()
except:
# 如果发生错误则回滚
db.rollback()
# 关闭数据库连接
db.close()
return result
