def process_users_chunk(chunk, stats_db_file): db = Database(stats_db_file) chunk_dbfile = os.path.join(DISTRIBUTED_TWEET_STATS_USER_PATH, 'users_{}_{}.db'.format(chunk[0], chunk[-1])) chunk_db = Database(chunk_dbfile) stats_tb = chunk_db.create_table('statistics', TWEET_STATS_COLUMNS) chunk_db.cursor.execute('BEGIN') for uid in chunk: if os.path.getsize(chunk_dbfile.replace('.db', '.db-shm')) >= 1e8: print "{} is larger than 100MB".format(chunk_dbfile) break results = db.select( 'SELECT * FROM statistics WHERE user_id={}'.format(uid)) chunk_db.insert( 'INSERT INTO {} VALUES (?, ?, ?, ?, ?)'.format(stats_tb), results, many=True) chunk_db.connection.commit() chunk_db.connection.close() db.connection.close()
def update_routes(self): """更新路線基本資料""" with Database() as db: cities = db.select_city() for city in cities: if city['status']: print( "🌐 正在下載{city}的路線基本資料...".format(city=city['chinese_name'])) routes = Bustw().get_info(city['english_name'])['routes'] with Database() as db: db.delete_routes(city['english_name']) for route in routes: db.insert_route({ 'route_uid': route['routeUID'], 'route_name': route['routeName'], 'city': route['city'], 'departure_stop_name': route['departureStopName'], 'destination_stop_name': route['destinationStopName'], }) else: with Database() as db: db.delete_routes(city['english_name'])
def process_db_file(user_chunks, tweet_db_file): mover_tweets_db = Database( os.path.join(MOVERS_TWEETS_DB_PATH, tweet_db_file.split('/')[-1])) mover_tweets_tb = mover_tweets_db.create_table(TWEETS_TBNAME, TWEETS_COLUMNS) mover_tweets_db.cursor.execute('BEGIN') current_tweets_db = Database(tweet_db_file) s = time.time() for i, user_chunk in enumerate(user_chunks): if int(ceil((float(i) / len(user_chunks)) * 100)) % 25 == 0 or i == len(user_chunks) - 1: print "\n\tProcessing chunk {} out of {}".format( i + 1, len(user_chunks)) process_user_chunk(user_chunk, current_tweets_db, mover_tweets_db) current_tweets_db.connection.close() mover_tweets_db.connection.commit() mover_tweets_db.connection.close() print '\n\tElapsed Time for db file: {}s\n'.format( round(time.time() - s, 2))
def check_ingestion_status(): '''Print max dates in each table.''' sql = "SELECT NOW();" now = Database().execute(sql) msg = 'Now: ' space = (22 - len(msg))*' ' print(msg, space, tb.DateConvert(now.values[0][0]).date) sql = 'SELECT MAX(date) FROM ticker;' ticker_date = Database().execute(sql) msg = 'Max ticker date: ' space = (22 - len(msg))*' ' print(msg, space, tb.DateConvert(ticker_date.values[0][0]).date) sql = 'SELECT MAX(open_date) FROM candles;' candle_date = Database().execute(sql) msg = 'Max candle date: ' space = (22 - len(msg))*' ' print(msg, space, tb.DateConvert(candle_date.values[0][0]).date) sql = 'SELECT MAX(open_date) FROM engineered_data;' eng_date = Database().execute(sql) msg = 'Max engineered date: ' space = (22 - len(msg))*' ' print(msg, space, tb.DateConvert(eng_date.values[0][0]).date)
def select_cities(self): """選擇要搜尋的資料""" with Database() as db: cities = db.select_city() questions = [{ 'type': 'checkbox', 'qmark': '🏙 ', 'message': '請選擇要檢索的城市\n', 'name': 'answer', 'choices': [{ 'name': city['chinese_name'], 'value': city['english_name'], 'checked': city['status'] } for city in cities] }] print() try: answer = prompt(questions)['answer'] except KeyError: raise KeyboardInterrupt print() with Database() as db: for english_name in answer: db.update_city(english_name, english_name in answer)
async def setup(self, ctx, channel: discord.TextChannel): db = Database(ctx.guild.id) if not await db.find_info(): logs_setup = await db.setup(channel.id) if logs_setup: embed = discord.Embed(title='Logker first setup successfully!', color=discord.Colour.green()) embed.add_field(name='Default Language', value='English') embed.add_field(name='Logs Channel', value=(ctx.channel if channel is None else channel).mention) msg = await (ctx if channel is None else channel).send( embed=embed) await msg.add_reaction('✅') await (ctx if channel is None else channel).send( file=discord.File('assets/yujin_wonyoung_thumbs_up.gif')) main_server = self.client.get_channel(833224379562590218) await main_server.send(f'Logker has setup in {ctx.guild.name}!' ) else: info = await db.find_info() lang = 'en' if info is None else info['logs_language'] embed = already_setup_en( ctx) if lang == 'en' else already_setup_th(ctx) await ctx.send(embed=embed)
def db(self): """ Fetch the database configuration for the environment, and create an Engine object with the retrieved information. """ config = Database().load_config() return create_engine("%s://%s@%s/%s" % (config['adapter'], config['username'], config['host'], config['database']))
def __init__(self, distro, release, target, subtarget, profile, packages=set()): self.log = logging.getLogger(__name__) self.config = Config() self.database = Database(self.config) self.distro = distro.lower() self.release = release self.target = target self.subtarget = subtarget self.profile = profile self.vanilla = False self.vanilla_packages = set(self.database.get_image_packages(self.distro, self.release, self.target, self.subtarget, self.profile)) self.vanilla_packages.update(self.config.get(self.distro).get("vanilla", "")) if not packages: # install default packages self.log.debug("using vanilla packages as packages are empty") self.vanilla = True self.packages = self.vanilla_packages elif set(packages) == self.vanilla_packages: # install default packages self.log.debug("packages are like vanilla packages") self.vanilla = True self.packages = self.vanilla_packages else: self.log.debug("use custom packages") self.packages = packages self.log.debug("packages\t %s", self.packages) self.log.debug("vanilla\t %s", self.vanilla_packages) self.set_request_hash() self.log.debug("image request hash: {}".format(self.request_hash))
def get_records_subset(sample_size=10000): databases = fd.askopenfilenames( title="Choose 10 databases with stats by user") output_csv_path = fd.askdirectory(title="Choose csv output directory") with open(os.path.join(output_csv_path, '1000 user sample.csv'), 'wb') as csv_file: writer = csv.writer(csv_file, delimiter=',') writer.writerow([ 'User ID', 'Date', 'Fips', 'tmax', 'prcp', 'humidity', 'Total Tweets', 'Weather Tweets' ]) for i, db_file in enumerate(databases): print 'Db {} out of {} Dbs'.format(i + 1, len(databases)) db = Database(db_file) chosen_users = choose_n([ user[0] for user in db.select( 'SELECT DISTINCT user_id FROM aggregated_tweet_data LIMIT {}' .format(sample_size)) ]) for uid in chosen_users: records = db.select( 'SELECT * FROM aggregated_tweet_data WHERE user_id={}'. format(uid)) writer.writerows(records) db.connection.close()
def main(self): with Database() as db: if not len(db.select_city()): self.initialize_database() self.select_cities() self.update_routes()
def process_single_db(db_file): db = Database(db_file) users_fips_tb = db.create_table(USER_FIPS_TBNAME, USER_FIPS_COLUMNS) user_chunks = chunkify([user[0] for user in get_users_in_db(db_file)], n=10000) process_user_chunks(user_chunks, db)
def select_routes(self, route_name: str): """取得資料庫中名稱符合的路線""" with Database() as db: routes = db.select_route(route_name) return routes
async def on_raw_message_edit(self, payload): if payload.cached_message: return db = Database(payload.guild_id) # Create a new instance if not await db.info_exists( ): # Check if that server has Logker setup? return info = await db.find_info() # Check if guild_id is equal to payload.guild.id if info['guild_id'] == payload.guild_id: logs_channel = self.client.get_guild(info['guild_id']).get_channel( info['channel_id']) config_lang = 'en' if info is None else info['logs_language'] # Set language version of embed message embed = (message_events_msg.raw_message_edit_en( payload.cached_message) if config_lang == 'en' else message_events_msg.raw_message_edit_th( payload.cached_message)) await logs_channel.send(embed=embed)
def get_requests(self, *args, **kwargs): db = Database() result_set = db.query( f"SELECT * FROM requests WHERE id_user = {self.id}") requests = [] for request in result_set: last_change = db.query( f"SELECT * FROM history WHERE id_request = {request.id}") last_ts = last_change[-1][-1] if len(last_change) > 0 else None requests.append({ 'id': request.id, 'user': object_to_dict(self), 'driver': db.get(Driver, request.id_driver, as_dict=True), 'collector': db.get(Collector, request.id_collector, as_dict=True), 'status': request.status, 'points': request.points, 'weight': request.weight, 'timestamp': last_ts }) return requests
def parallel_migration(coastal_counties_db, tweet_db_file): current_coastal_counties_db_file = os.path.join( RESULTS_PATH, os.path.split(tweet_db_file)[1]) current_coastal_counties_db = Database(current_coastal_counties_db_file) coastal_counties_tweets_table = current_coastal_counties_db.create_table( *COASTAL_COUNTIES_TWEETS_TABLE) joined_rows, other_db_name = coastal_counties_db.ijoin( (tweet_db_file, 'other_db', 'tweets'), FIELDS_TO_SELECT_FOR_JOIN + ',counties.fips', MATCH_CRITERIA_FOR_JOIN) current_coastal_counties_db.cursor.execute('BEGIN') current_coastal_counties_db.insert("""INSERT INTO {} VALUES(?,?,?,?,?)""".format(coastal_counties_tweets_table), joined_rows, many=True) current_coastal_counties_db.connection.commit() print "\tDetaching database..." coastal_counties_db.cursor.execute( """DETACH DATABASE '{}'""".format(other_db_name)) current_coastal_counties_db.connection.close()
def __init__(self, logger, db_connection, master_token): """ Class to initialise downloading of files from OH, convert files into lists of dictionaries, and upload to db :param logger: logging object passed from parent script :param db_connection: database connection in the form of psycopg2.connect(...) """ self.logger = logger try: self.db = Database(db_connection) self.ingester = UpsertIngester(db_connection) self.oh = OHWrapper(logger=logger, files_directory=BULK_FILES_DIRECTORY, master_token=master_token) except Psycopg2Error: logger.error( f'Error occurred while initialising classes. Breaking script.: {traceback.format_exc()}' ) sys.exit(1) os.makedirs(BULK_FILES_DIRECTORY, exist_ok=True)
def deliver_request(self, id_request, delivery_code): db = Database() code_search = db.query( f"SELECT * FROM delivery_codes WHERE code = '{delivery_code}'") id_collector = code_search[0][1] delivered_request = { 'id': id_request, 'id_collector': id_collector, 'status': Request.REQUEST_STATUS.get('evaluation') } request = Request(**delivered_request) db.update(request) driver = Driver(**{'id': self.id, 'current_request': None}) db.update(driver) dc = db.get(DeliveryCode, code_search[0][0]) db.delete(dc) new_history = { 'id_request': request.id, 'old_status': Request.REQUEST_STATUS.get('ongoing'), 'new_status': Request.REQUEST_STATUS.get('evaluation'), 'timestamp': str(datetime.datetime.now().timestamp()) } history = History(**new_history) db.add(history) return { 'request': object_to_dict(request), 'msg': 'Request delivered successfully' }
def check_data_continuity(symbol='all', table='candles', verbose=True): '''Check that candles form a fully continuous date range.''' if symbol == 'all': symbols = get_symbols() else: if isinstance(symbol, str): symbols = [symbol] for symbol in symbols: sql = f"SELECT open_date FROM {table} WHERE symbol = '{symbol}';" dates = Database().execute(sql).open_date min_date = dates.min() max_date = dates.max() continuous_date_range = pd.date_range(min_date, max_date, freq='1H') discontinuous = 0 for i, date in enumerate(dates.iloc[1:], start=1): prev_date = tb.DateConvert(dates.iloc[i-1]).datetime date = tb.DateConvert(date).datetime if date != prev_date + timedelta(hours=1): discontinuous+=1 if verbose: print(date) if discontinuous: discontinuous = int(discontinuous/2)+1 print(f'There are {discontinuous} discontinuous dates in {symbol}') else: print(f'No discontinuous dates found in {symbol}')
async def member_role_update(self, before: discord.Member, after: discord.Member): if before.activity != after.activity: return if before.status != after.status: return db = Database(before.guild.id) # Create a new instance if not await db.info_exists( ): # Check if that server has Logker setup? return info = await db.find_info() if info['guild_id'] == before.guild.id: guild_log = self.client.get_guild(info['guild_id']) logs_channel = guild_log.get_channel(info['channel_id']) config_lang = 'en' if info is None else info['logs_language'] if before.roles != after.roles: for guild in after.guild.roles: if guild not in guild_log.roles: return # Set language version of embed message embed = (member_events_msg.member_role_update_en( before, after) if config_lang == 'en' else member_events_msg.member_role_update_th( before, after)) await logs_channel.send(embed=embed)
def attend_request(self, id_request, *args, **kwargs): db = Database() attended_request = { 'id': id_request, 'id_driver': self.id, 'status': Request.REQUEST_STATUS.get('ongoing') } request = Request(**attended_request) db.update(request) driver = Driver(**{'id': self.id, 'current_request': id_request}) db.update(driver) new_history = { 'id_request': request.id, 'old_status': Request.REQUEST_STATUS.get('new'), 'new_status': Request.REQUEST_STATUS.get('ongoing'), 'timestamp': str(datetime.datetime.now().timestamp()) } history = History(**new_history) db.add(history) return { 'request': object_to_dict(request), 'msg': 'Request attended successfully' }
async def on_message_edit(self, before, after): if after.author.bot: return db = Database(before.guild.id) # Create a new instance if not await db.info_exists( ): # Check if that server has Logker setup? return info = await db.find_info() # Check if guild_id is equal to message.guild.id if info['guild_id'] == after.guild.id: logs_channel = self.client.get_guild(info['guild_id']).get_channel( info['channel_id']) config_lang = 'en' if info is None else info['logs_language'] if before.content != after.content: # Set language version of embed message embed = (message_events_msg.message_edit_en(before, after) if config_lang == 'en' else message_events_msg.message_edit_th(before, after)) await logs_channel.send(embed=embed)
def exchange_points(self, points_quantity, *args, **kwargs): db = Database() if self.points < points_quantity: return {'msg': 'Insufficient points'} self.points -= points_quantity db.update(self) return {'msg': f'{points_quantity} exchanged successfully'}
async def member_mention_client(self, message: discord.Message): # Prevent client responds to itself if message.author is self.client.user: return if message.author.bot: return if self.client.user.mentioned_in(message): db = Database(message.guild.id) # Create a new instance if not await db.info_exists(): # Check if that server has Logker setup? config_lang = 'en' else: info = await db.find_info() config_lang = info[2] prefix = await db.find_prefix() if '@here' in message.content: return if '@everyone' in message.content: return await message.channel.send( f'I saw you mention me! Type `{prefix}help` if you really need help!' if config_lang == 'en' else f'เราเห็นแกต้องการความช่วยเหลือนะ พิมพ์ `{prefix}help` เพื่อดูคำสั่งทั้งหมด!' )
def handle(event, context): body = json.loads(event.get('body')) collector = Collector(**body) db = Database() db.add(collector) collector.__dict__.pop('_sa_instance_state') response = { 'statusCode': 200, 'headers': { 'Access-Control-Allow-Origin': '*', 'Access-Control-Allow-Credentials': True }, 'body': json.dumps({ 'collector': collector.__dict__, 'msg': f'Collector {collector.id} added successfully' }), } return response
def __init__(self): threading.Thread.__init__(self) self.log = logging.getLogger(__name__) self.log.info("log initialized") self.config = Config() self.log.info("config initialized") self.db = Database(self.config) self.log.info("db initialized")
def install(request): #return HttpResponse(urlunquote(request.POST)) #return HttpResponse(request.POST.dict()) doc = json.loads(request.POST.get('data')) doc['timestamp_added'] = int(time.time()) database = Database() probe = 'dk_dtu_compute_questionnaire_survey' doc_id = database.insert(doc, collection = probe) return HttpResponse(json.dumps({'ok':str(doc_id)}), status = 200)
def handle(event, context): db = Database() partner = db.get_all(Partner, as_dict=True) response = { 'statusCode': 200, 'body': json.dumps(partner), } return response
def handle(event, context): db = Database() drivers = db.get_all(Driver, as_dict=True) response = { 'statusCode': 200, 'body': json.dumps(drivers), } return response
def reset_dimension(dimension: int): set_session_info('dimension', dimension) # start init database and hash table db_path = f'data/{"client" if get_role() else "server"}' \ f'/{get_session_info("username")}/{dimension}/chunk_sections' set_session_info('chunk_section_db', Database(db_path)) # if server if get_role() == 0: set_session_info('chunk_section_hash', dict())
def __init__(self): threading.Thread.__init__(self) self.log = logging.getLogger(__name__) self.log.info("log initialized") self.config = Config() self.log.info("config initialized") self.database = Database(self.config) self.log.info("database initialized") self.worker_id = None self.imagebuilders = []