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 = []
