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__(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