def load(self, cursor: sqlite3.Cursor) -> None:
"""Load the properties from the provided database cursor."""
cursor.execute('select mu, sigma, games_count, wins_count from players '
'where name=? and ladder=?', [self.name, self.ranking.ladder])
loaded = cursor.fetchone()
logging.info('Fetched player %s skill %s', self.name, loaded['mu'])
self.rating = self.ranking.tsh.create_rating(loaded['mu'], loaded['sigma'])
self.games_count = loaded['games_count']
self.wins_count = loaded['wins_count']
def add(
cursor: sqlite3.Cursor, solution: str, usages: str,
keywords: _KeywordsType):
cmd = 'INSERT INTO clues VALUES (?, ?, ?)'
try:
cursor.execute(cmd, (solution, usages, _format_keyword_counts(keywords)))
except (sqlite3.OperationalError, sqlite3.IntegrityError):
print(cmd, solution, usages, _format_keywords(keywords))
raise
def str2int(s: str, cursor: sqlite3.Cursor) -> int:
"""
On interroge une base données, on vérifie si la chaine de caractère est soit
dans la case phonologie, soit dans la case graphie et on renvoie tous les entiers correspondants.
:param s: chaine de caractères
:param cursor: pointeur de base de données
:return: l'entier associé à la chaine de caractères d'entrée, False sinon
"""
recherche = "SELECT integer FROM Lexique WHERE ortho = ? OR phon = ?"
cursor.execute(recherche, (s,)*2)
return cursor.fetchone()
async def import_ignored_channels(conn: asyncpg.Connection, c: sqlite3.Cursor):
log.info("Importing ignored channels...")
channels = []
log.debug("Gathering ignored channels from sqlite...")
c.execute("SELECT server_id, channel_id FROM ignored_channels")
rows = c.fetchall()
for server_id, channel_id in rows:
channels.append((server_id, channel_id))
log.debug(f"Collected {len(channels):,} records from old database.")
log.info("Copying records to ignored channels table")
res = await conn.copy_records_to_table("ignored_entry", records=channels, columns=["server_id", "entry_id"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
log.info("Finished importing channels.")
def update_grammar_with_table_values(grammar_dictionary: Dict[str, List[str]],
schema: Dict[str, List[TableColumn]],
cursor: Cursor) -> None:
for table_name, columns in schema.items():
for column in columns:
cursor.execute(f'SELECT DISTINCT {table_name}.{column.name} FROM {table_name}')
results = [x[0] for x in cursor.fetchall()]
if column_has_string_type(column):
productions = sorted([f'"{str(result)}"' for result in results], reverse=True)
grammar_dictionary["string"].extend(productions)
elif column_has_numeric_type(column):
productions = sorted([f'"{str(result)}"' for result in results], reverse=True)
grammar_dictionary["number"].extend(productions)
def query(cursor: sqlite3.Cursor, clue: str) -> List[Tuple[str, int, _KeywordsType]]:
results = []
cmd = """
SELECT solution, usages, keywords
FROM clues
WHERE keywords LIKE ?
"""
like = '%' + _format_keywords_query(clue_keywords(clue)) + '%'
for solution, usages, keywords in cursor.execute(cmd, (like,)):
keywords = _query_keywords(keywords)
results.append((solution, usages, keywords))
return results
async def import_server_properties(conn: asyncpg.Connection, c: sqlite3.Cursor):
properties = []
prefixes = []
times = []
log.debug("Gathering server property records from sqlite...")
log.info("Importing server properties...")
c.execute("SELECT server_id, name, value FROM server_properties")
rows = c.fetchall()
for server_id, key, value in rows:
server_id = int(server_id)
if key == "prefixes":
prefixes.append((server_id, json.loads(value)))
continue
if key == "times":
value = json.loads(value)
for entry in value:
times.append((server_id, entry["timezone"], entry["name"]))
continue
elif key in ["events_channel", "levels_channel", "news_channel", "welcome_channel", "ask_channel",
"announce_channel", "announce_level"]:
value = int(value)
elif key in ["watched_message", "watched_channel"]:
continue
elif key == "commandsonly":
value = bool(value)
properties.append((server_id, key, value))
log.debug(f"Collected {len(properties):,} properties, {len(times):,} timezones and {len(prefixes):,} prefixes"
f" from old database.")
log.info("Copying records to server property table")
res = await conn.copy_records_to_table("server_property", records=properties, columns=["server_id", "key", "value"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
log.info("Copying records to server prefixes table")
res = await conn.copy_records_to_table("server_prefixes", records=prefixes, columns=["server_id", "prefixes"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
log.info("Copying records to server timezone table")
res = await conn.copy_records_to_table("server_timezone", records=times, columns=["server_id", "zone", "name"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
log.info("Finished importing server properties.")
async def import_characters(conn: asyncpg.Connection, c: sqlite3.Cursor, new_ids: Dict[int, int]):
log.info("Importing characters...")
# Dictionary that maps character names to their SQL ID
old_ids = {}
chars = []
log.debug("Gathering character records from sqlite...")
c.execute("""SELECT id, user_id, name, level, vocation, world, guild FROM chars ORDER By id ASC""")
rows = c.fetchall()
for char_id, user_id, name, level, vocation, world, guild in rows:
chars.append((user_id, name, level, vocation, world, guild))
old_ids[name] = char_id
log.debug(f"Collected {len(chars):,} records from old database.")
log.info("Copying records to character table")
res = await conn.copy_records_to_table("character", records=chars,
columns=["user_id", "name", "level", "vocation", "world", "guild"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
new_chars = await conn.fetch('SELECT id, name FROM "character"')
log.debug("Generating old id to new id mapping...")
new_ids.clear()
for new_id, name in new_chars:
new_ids[old_ids[name]] = new_id
log.debug("Old id to new id mapping generated.")
ids = 1
deaths = []
killers = []
log.debug("Gathering death records from sqlite...")
c.execute("""SELECT char_id, level, killer, date, byplayer FROM char_deaths ORDER BY date ASC""")
rows = c.fetchall()
# This doesn't seem very safe to do, maybe it would be better to import deaths the old way
for char_id, level, killer, date, byplayer in rows:
byplayer = byplayer == 1
date = datetime.datetime.fromtimestamp(date, datetime.timezone.utc)
deaths.append((new_ids[char_id], level, date))
killers.append((ids, killer, byplayer))
ids += 1
log.debug(f"Collected {len(deaths):,} records from old database.")
log.info("Copying records to deaths table.")
res = await conn.copy_records_to_table("character_death", records=deaths, columns=["character_id", "level", "date"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
log.info("Copying records to death killers table.")
res = await conn.copy_records_to_table("character_death_killer", records=killers,
columns=["death_id", "name", "player"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
log.debug("Gathering level up records from sqlite...")
c.execute("""SELECT char_id, level, date FROM char_levelups ORDER BY date ASC""")
rows = c.fetchall()
levelups = []
for char_id, level, date in rows:
date = datetime.datetime.fromtimestamp(date, datetime.timezone.utc)
levelups.append((new_ids[char_id], level, date))
log.debug(f"Collected {len(levelups):,} records from old database.")
log.info("Copying records to level ups table.")
res = await conn.copy_records_to_table("character_levelup", records=levelups,
columns=["character_id", "level", "date"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
log.info("Finished importing characters.")
async def import_roles(conn: asyncpg.Connection, c: sqlite3.Cursor):
log.info("Importing roles...")
auto_roles = []
joinable_roles = []
log.debug("Gathering auto roles from sqlite...")
c.execute("SELECT server_id, role_id, guild FROM auto_roles")
rows = c.fetchall()
for server_id, role_id, guild in rows:
auto_roles.append((server_id, role_id, guild))
log.debug(f"Collected {len(auto_roles):,} records from old database.")
log.info("Copying records to auto roles table")
res = await conn.copy_records_to_table("role_auto", records=auto_roles, columns=["server_id", "role_id", "rule"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
log.debug("Gathering joinable roles from sqlite...")
c.execute("SELECT server_id, role_id FROM joinable_roles")
rows = c.fetchall()
for server_id, role_id in rows:
joinable_roles.append((server_id, role_id))
log.debug(f"Collected {len(joinable_roles):,} records from old database.")
log.info("Copying records to joinable roles table")
res = await conn.copy_records_to_table("role_joinable", records=joinable_roles, columns=["server_id", "role_id"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
log.info("Finished importing roles.")
async def import_events(conn: asyncpg.Connection, c: sqlite3.Cursor, new_char_ids: Dict[int, int]):
log.info("Importing events...")
events = []
subscribers = []
participants = []
new_event_ids = {}
i = 1
log.debug("Gathering event records from sqlite...")
c.execute("SELECT id, creator, name, start, active, status, description, server, joinable, slots FROM events")
rows = c.fetchall()
for event_id, creator, name, start, active, status, description, server, joinable, slots in rows:
new_event_ids[event_id] = i
start = datetime.datetime.fromtimestamp(start, datetime.timezone.utc)
active = bool(active)
joinable = bool(joinable)
status = 4 - status
events.append((creator, name, start, active, description, server, joinable, slots, status))
i += 1
log.debug(f"Collected {len(events):,} records from old database.")
log.info("Copying records to events table")
res = await conn.copy_records_to_table("event", records=events,
columns=["user_id", "name", "start", "active", "description", "server_id",
"joinable", "slots", "reminder"])
log.debug(f"Copied {get_affected_count(res):,} records successfully.")
log.debug("Gathering event subscribers from sqlite...")
c.execute("SELECT event_id, user_id FROM event_subscribers")
rows = c.fetchall()
for event_id, user_id in rows:
subscribers.append((new_event_ids[event_id], user_id))
log.debug(f"Collected {len(subscribers):,} records from old database.")
log.info("Copying records to event subscribers table")
res = await conn.copy_records_to_table("event_subscriber", records=subscribers, columns=["event_id", "user_id"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
log.debug("Gathering event participants from sqlite...")
c.execute("SELECT event_id, char_id FROM event_participants")
rows = c.fetchall()
for event_id, char_id in rows:
participants.append((new_event_ids[event_id], new_char_ids[char_id]))
log.debug(f"Collected {len(participants):,} records from old database.")
log.info("Copying records to event participants table")
res = await conn.copy_records_to_table("event_participant", records=participants,
columns=["event_id", "character_id"])
log.info(f"Copied {get_affected_count(res):,} records successfully.")
log.info("Finished importing events.")
def query_db(query_string: str, cursor: sqlite3.Cursor):
yield cursor.execute(query_string).fetchall()
def create_table_nh(cursor: sqlite3.Cursor, rt: dict):
"""Next_hop table creating, first table"""
cursor.execute("create table next_hop (next_hop text)")
for next_hop in rt["route_table"]["next_hop"]:
cursor.execute("insert into next_hop values (?)", (next_hop, ))
def record_exists(cursor: sqlite3.Cursor, path: str) -> bool:
cursor.execute("SELECT EXISTS(SELECT 1 FROM records WHERE path=? LIMIT 1)", (path,))
return bool(cursor.fetchone()[0])
def count_records(cursor: sqlite3.Cursor) -> int:
cursor.execute("SELECT COUNT() FROM records")
return cursor.fetchone()[0]
def vote(cursor: sqlite3.Cursor, voter: Voter) -> None:
query: str = '''UPDATE voters SET has_voted = 1 WHERE id = ?'''
cursor.execute(query, (voter._id, ))
voter.has_voted = True
def update_record(cursor: sqlite3.Cursor, path: str, hash_: bytes, modified: float) -> None:
cursor.execute("INSERT OR REPLACE INTO records(path, hash, modified) VALUES (?, ?, ?)", (path, hash_, modified))
def populate_db_and_check_for_asset_renaming(
cursor: Cursor,
data: DataHandler,
data_dir: Path,
msg_aggregator: MessagesAggregator,
username: str,
to_rename_asset: str,
renamed_asset: Asset,
target_version: int,
):
# Manually input data to the affected tables.
# timed_balances, multisettings and (external) trades
# At this time point we only have occurence of the to_rename_asset
cursor.execute(
'INSERT INTO timed_balances('
' time, currency, amount, usd_value) '
' VALUES(?, ?, ?, ?)',
('1557499129', to_rename_asset, '10.1', '150'),
)
# But add a time point where we got both to_rename_asset and
# renamed_asset. This is to test merging if renaming falls in time where
# both new and old asset had entries
cursor.execute(
'INSERT INTO timed_balances('
' time, currency, amount, usd_value) '
' VALUES(?, ?, ?, ?)',
('1558499129', to_rename_asset, '1.1', '15'),
)
cursor.execute(
'INSERT INTO timed_balances('
' time, currency, amount, usd_value) '
' VALUES(?, ?, ?, ?)',
('1558499129', renamed_asset.identifier, '2.2', '25'),
)
# Add one different asset for control test
cursor.execute(
'INSERT INTO timed_balances('
' time, currency, amount, usd_value) '
' VALUES(?, ?, ?, ?)',
('1556392121', 'ETH', '5.5', '245'),
)
# Also populate an ignored assets entry
cursor.execute(
'INSERT INTO multisettings(name, value) VALUES(?, ?)',
('ignored_asset', to_rename_asset),
)
cursor.execute(
'INSERT INTO multisettings(name, value) VALUES(?, ?)',
('ignored_asset', 'RDN'),
)
# Finally include it in some trades
cursor.execute(
'INSERT INTO trades('
' time,'
' location,'
' pair,'
' type,'
' amount,'
' rate,'
' fee,'
' fee_currency,'
' link,'
' notes)'
'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
(
1543298883,
'external',
'ETH_EUR',
'buy',
'100',
'0.5',
'0.1',
'EUR',
'',
'',
),
)
cursor.execute(
'INSERT INTO trades('
' time,'
' location,'
' pair,'
' type,'
' amount,'
' rate,'
' fee,'
' fee_currency,'
' link,'
' notes)'
'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
(
1563298883,
'kraken',
f'{to_rename_asset}_EUR',
'buy',
'100',
'0.5',
'0.1',
to_rename_asset,
'',
'',
),
)
cursor.execute(
'INSERT INTO trades('
' time,'
' location,'
' pair,'
' type,'
' amount,'
' rate,'
' fee,'
' fee_currency,'
' link,'
' notes)'
'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
(
1564218181,
'binance',
f'{to_rename_asset}_EUR',
'buy',
'100',
'0.5',
'0.1',
'BNB',
'',
'',
),
)
data.db.conn.commit()
# now relogin and check that all tables have appropriate data
del data
data = DataHandler(data_dir, msg_aggregator)
with creation_patch, target_patch(target_version=target_version):
data.unlock(username, '123', create_new=False)
# Check that owned and ignored assets reflect the new state
ignored_assets = data.db.get_ignored_assets()
assert A_RDN in ignored_assets
assert renamed_asset in ignored_assets
owned_assets = data.db.query_owned_assets()
assert A_ETH in owned_assets
assert renamed_asset in owned_assets
# Make sure that the merging of both new and old name entry in same timestamp works
querystr = (
f'SELECT time, amount, usd_value FROM timed_balances WHERE time BETWEEN '
f'0 AND 2556392121 AND currency="{renamed_asset.identifier}" ORDER BY time ASC;'
)
cursor = data.db.conn.cursor()
result = cursor.execute(querystr).fetchall()
assert len(result) == 2
assert result[0][0] == 1557499129
assert result[0][1] == '10.1'
assert result[0][2] == '150'
assert result[1][0] == 1558499129
assert result[1][1] == '3.3'
assert result[1][2] == '40'
# Assert that trades got renamed properly
cursor = data.db.conn.cursor()
query = (
'SELECT id,'
' time,'
' location,'
' pair,'
' type,'
' amount,'
' rate,'
' fee,'
' fee_currency,'
' link,'
' notes FROM trades ORDER BY time ASC;'
)
results = cursor.execute(query)
trades = []
for result in results:
trades.append({
'id': result[0],
'timestamp': result[1],
'location': result[2],
'pair': result[3],
'trade_type': result[4],
'amount': result[5],
'rate': result[6],
'fee': result[7],
'fee_currency': result[8],
'link': result[9],
'notes': result[10],
})
assert len(trades) == 3
assert trades[0]['fee_currency'] == 'EUR'
assert trades[0]['pair'] == 'ETH_EUR'
assert trades[1]['fee_currency'] == renamed_asset.identifier
assert trades[1]['pair'] == f'{renamed_asset.identifier}_EUR'
assert trades[2]['pair'] == f'{renamed_asset.identifier}_EUR'
assert data.db.get_version() == target_version
def v8_call_get_history_posl_ulozena_pozice(c: sqlite3.Cursor, adrhex: int):
dt = telegram_create(adrhex, Telegram.BEZNY, b'\x02\x54\x00')
c.execute(
"insert into serial_prikaz(cas, prikaz, typ_dotazu) values(?, ?, ?)",
(datetime.now(), conv_hex_to_str(dt),
APP_TYP_DOTAZU.V8_POSLEDNI_CTENA_POZICE.value))
def hmp_process_ctvrthodiny_data(cur: sqlite3.Cursor, ddata:bytes, od:bytes): #predpoklad obecny cas ctvrthodiny
#print("hmp_process_ctvrthodiny_data()")
res = { 'cas': 0, 'odhad': 0, 'hl_poc_imp': 0, 'podr_cas_imp1':0, 'podr_poc_imp1':0, 'podr_cas_imp2':0, 'podr_poc_imp2':0 }
data = ddata[6:-1]
#d0 = data[0] # stav vystupu
#d1 = data[1] # SYSTEM byte (RAM addr 20h)
d2 = data[2] # cas ve 1/4 low byte
d3 = data[3] # bity 0 1 jsou ve 1/4 high byte, bity 4-7 jsou high byte odhadu spotrebovane prace na konci 1/4h
d4 = data[4] # odhad spotrebovane prace na konci 1/4 low byte
#cas 1/4h
res['cas'] = (((d3 << 6) & 0xFF) >> 6) * 256 + d2 #cas v sekundach
#odhad
odhad = ((d3 >> 4) & 0x0F) # Odhad.bit 11 = 0 => Odhad = odhad
if odhad & 8 == 8 : # komprimovano Odhad.bit 11 = 1 => Odhad = (odhad.bit0-10 * 4) + 2048
odhad = ((odhad << 1) & 0x0F) >> 1
res['odhad'] = (odhad * 256 + d4)*4 + 2048
else:
res['odhad'] = odhad * 256 + d4 #odhad
res['hl_poc_imp'] = data[6] * 256 + data[5]
res['hl_cas_imp'] = (data[9] * 65536 + data[8] * 256 + data[7]) /1000
res['podr_cas_imp1'] = (data[12] * 65536 + data[11] * 256 + data[10]) /1000
res['podr_poc_imp1'] = data[14] * 256 + data[13]
res['podr_cas_imp2'] = (data[17] * 65536 + data[16] * 256 + data[15]) /1000
res['podr_poc_imp2'] = data[19] * 256 + data[18]
s_adr = conv_hex_to_str(od)
kk = cur.execute("""select (select konst from zapojeni_vstupy zv inner join zapojeni z on zv.zapojeni_id = z.id where adr_zarizeni=? and cislo=?) k1,
(select konst from zapojeni_vstupy zv inner join zapojeni z on zv.zapojeni_id = z.id where adr_zarizeni = ? and cislo=?) k2,
(select konst from zapojeni_vstupy zv inner join zapojeni z on zv.zapojeni_id = z.id where adr_zarizeni = ? and cislo=?) k3""",
(s_adr, 1, s_adr, 2, s_adr, 3)).fetchone()
if not kk or kk[0] == None or kk[1] == None or kk[2] == None:
return
k1 = kk[0]
k2 = kk[1]
k3 = kk[2]
"""
vypocet vykonu = 3600 / (cas hl eml * konstanta) .... pro jednotlive vstupy
vypocet prace = aktulanli pocet impulsu * konstatnta ... -> impl_1p = round(((impl_1p * 4) / k), 2) <- ta 4.ka znamena 4*15 - 1h na hodinu"""
v_1 = round(3600 / (res['hl_cas_imp'] * k1), 2)
v_2 = round(3600 / (res['podr_cas_imp1'] * k2), 2)
v_3 = round(3600 / (res['podr_cas_imp2'] * k3), 2)
p_1 = round(((res['hl_poc_imp'] * 4) / k1), 2)
p_2 = round(((res['podr_poc_imp1'] * 4) / k2), 2)
p_3 = round(((res['podr_poc_imp2'] * 4) / k3), 2)
if res['hl_poc_imp'] == 0:
cosF = 0
else:
cosF = round( res['hl_poc_imp'] / math.sqrt(res['hl_poc_imp']**2 + res['podr_poc_imp1']**2), 2)
cur.execute("""REPLACE INTO livedata(adr_zarizeni, cas, odhad, vykon_1, prace_1, vykon_2, prace_2, vykon_3, prace_3, cosf, cas_odecet)
values (?,?,?,?,?,?,?,?,?,?,?);""", (s_adr, res['cas'], res['odhad'], v_1, p_1, v_2, p_2, v_3, p_3, cosF, datetime.now()))
def hmp_call_get_datumcas(c: sqlite3.Cursor) -> bytes:
hx = "ff a5 00 3f 02 1a 2d 00 80 53"
c.execute("insert into serial_prikaz(cas, prikaz, typ_dotazu) values(?, ?, ?)", (datetime.now(), hx, APP_TYP_DOTAZU.HMP_DATUM_A_CAS.value))
def hmp_call_stoup_konst(c: sqlite3.Cursor) -> bytes:
hx = "ff a5 00 3f 02 1a 23 2a 00 b3"
c.execute("insert into serial_prikaz(cas, prikaz, typ_dotazu) values(?, ?, ?)", (datetime.now(), hx, APP_TYP_DOTAZU.HMP_STOUPACI_KONSTANTA.value))
def hmp_call_posun_regul_krivka(c: sqlite3.Cursor) -> bytes:
hx = "ff a5 00 3f 02 1a 22 25 00 b9"
c.execute("insert into serial_prikaz(cas, prikaz, typ_dotazu) values(?, ?, ?)", (datetime.now(), hx, APP_TYP_DOTAZU.HMP_POSUN_REGULACNI_KRIVKA.value))
def hmp_call_techmax(c: sqlite3.Cursor) -> bytes:
hx = "ff a5 00 3f 02 1a 22 22 00 bc"
c.execute("insert into serial_prikaz(cas, prikaz, typ_dotazu) values(?, ?, ?)", (datetime.now(), hx, APP_TYP_DOTAZU.HMP_TMAX.value))
def track_macros(connection: sqlite3.Connection, cursor: sqlite3.Cursor):
foodsEaten = []
foodEaten = ''
totalCalories = 0
totalProtein = 0
totalCarbohydrate = 0
totalFat = 0
totalFiber = 0
while foodEaten != 'stop':
addFood = ''
foodEaten = input(
"Please type in a food that you have eaten or 'stop' to stop entering foods>>"
).lower()
if foodEaten == 'stop':
break
cursor.execute("SELECT * FROM FOODS WHERE FOODS.FOOD = ?",
(foodEaten.capitalize(), ))
result = cursor.fetchone()
if result is None:
print("This food does not exist in the database.")
while addFood != 'n':
addFood = input(
"Please type 'a' if you would like to add it to the database "
"or 'n' to continue adding other foods>>").lower()
if addFood == 'a':
result = return_food_entry(foodArg=foodEaten)
save_to_database(result, connection, cursor)
totalCalories += result['calories']
totalCarbohydrate += result['carbohydrate']
totalFat += result['fat']
totalProtein += result['protein']
totalFiber += result['fiber']
print("This food has been added to foods eaten.")
break
else:
totalCalories += result[1]
totalProtein += result[2]
totalFat += result[3]
totalCarbohydrate += result[4]
totalFiber += result[5]
if addFood == 'n':
continue
foodsEaten.append(foodEaten)
if len(foodsEaten) == 0:
print(
"No foods were provided, so no information is available to display. Going back to main query...\n"
)
return
proteinPercentage = totalProtein * 4 * 100 / totalCalories
fatPercentage = totalFat * 9 * 100 / totalCalories
carbohydratePercentage = (totalCarbohydrate -
totalFiber) * 4 * 100 / totalCalories
remainingPercentage = 100 - proteinPercentage - fatPercentage - carbohydratePercentage
prettyFoodsEaten = return_pretty_consumption(foodsEaten)
print("\nYou consumed the foods: {}.".format(", ".join(prettyFoodsEaten)))
print(
"You ate a total of {:.2f} calories, {:.2f} grams of protein, {:.2f} grams of fat,"
" {:.2f} grams of carbohydrates, and {:.2f} grams of fiber.".format(
totalCalories, totalProtein, totalFat, totalCarbohydrate,
totalFiber))
print("{:.2f}% of calories from {:.2f} grams of protein.".format(
proteinPercentage, totalProtein))
print("{:.2f}% of calories from {:.2f} grams of fat.".format(
fatPercentage, totalFat))
print(
"{:.2f}% of calories from {:.2f} grams of carbohydrates minus {:.2f} grams of fiber."
.format(carbohydratePercentage, totalCarbohydrate, totalFiber))
print("{:.2f}% of calories are inaccurate from incorrect data.\n".format(
abs(remainingPercentage)))
def v8_process_history(cur: sqlite3.Cursor, ddata: bytes, od: bytes):
data = ddata[6:-1]
r = data[3:]
#print("v8_process_history() : {}".format(conv_hex_to_str(r)))
if r[0] == 0xFF and r[1] == 0xFF and r[2] == 0xFF and r[3] == 0xFF:
return
cas_s = (r[0] & 0x1F) * 2
cas_m = (r[1] & 0x07) << 3 | (r[0] & 0xE0) >> 5
cas_h = (r[1] & 0xF8) >> 3
cas_d = (r[2] & 0x1F)
cas_mes = ((r[3] & 0x01) << 3) | (r[2] & 0xE0) >> 5
cas_rok = (r[3] & 0xFE) >> 1
cas_rok = 2000 + cas_rok
d = datetime(cas_rok,
cas_mes,
cas_d,
cas_h,
cas_m,
cas_s,
tzinfo=timezone.utc)
unixstamp = d.timestamp()
cas14h = ((r[5] & 0x0F) * 256) + r[4] # 84 x3 - delka zaznamu = 900 sekund
v8_sammod = r[5] & 0x80
v8_ukon_14h = r[5] & 0x40
ukon_14h_inext = r[5] & 0x20
# 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8
# A5 3F 20 35 C7 52 4E 15 | E0 91 B4 28 84 23 | 35 02 A2 40 20 7C F9 00 64 F3 01 E0 5C 15
vs = []
vs.append(((r[7] & 0x0F) * 256) + r[6]) # pocet impulzu vstup 1
vs.append(((r[7] & 0xF0) * 256) + r[8]) # pocet impulzu vstup 2
vs.append(((r[10] & 0x0F) * 256) + r[9]) # pocet impulzu vstup 3
vs.append(((r[10] & 0xF0) * 256) + r[11]) # pocet impulzu vstup 4
vs.append(((r[13] & 0x0F) * 256) + r[12]) # pocet impulzu vstup 5
vs.append(((r[13] & 0xF0) * 256) + r[14]) # pocet impulzu vstup 6
vs.append(((r[16] & 0x0F) * 256) + r[15]) # pocet impulzu vstup 7
vs.append(((r[16] & 0xF0) * 256) + r[17]) # pocet impulzu vstup 8
#vypocet je identicky jak u HMP
s_adr = conv_hex_to_str(od)
#Hodnota odberu se z impulsu a konstanty vypocita takto
#odber:=4 * 1000 * Pocet_impulsu / Konstanta
dbvstupy = cur.execute(
"select cislo, konst from zapojeni_vstupy zv inner join zapojeni z on zv.zapojeni_id = z.id where adr_zarizeni = ? order by cislo asc",
(s_adr, )).fetchall()
jk = ""
for vstup in dbvstupy:
jk += str(vstup[1]) + ","
if len(jk) > 0:
jk = jk[:-1]
jk = "[" + jk + "]"
last_hw_id = -1
try:
cur.execute(
"""insert into historie_data_raw(adr_zarizeni, rok, mesic, den, hh, mm, ss, piv_1, piv_2, piv_3, piv_4, piv_5, piv_6, piv_7, piv_8, cas_14h, v8_sammod, v8_ukon_14h, ukon_14h_inext, unixstamp, rawdata, k)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?)""",
(s_adr, cas_rok, cas_mes, cas_d, cas_h, cas_m, cas_s, vs[0], vs[1],
vs[2], vs[3], vs[4], vs[5], vs[6], vs[7], cas14h,
v8_sammod, v8_ukon_14h, ukon_14h_inext, unixstamp,
conv_hex_to_str(ddata), jk))
last_hw_id = cur.lastrowid
except Exception as e:
pass
try:
dd = data[1:3]
pos = int(((int(dd[1]) * 256 + int(dd[0])) - 2304) / 18)
cur.execute("update zapojeni set cislozaznamu=? where adr_zarizeni=?",
(
pos,
s_adr,
))
except Exception as e:
print("v8_process_history(): Ex: {}".format(str(e)), flush=True)
pass
#cosF se vypocita = cinny/sqrt(cinny^2 + jalovy^2) , cinny = HL, jalovy = 1P
cosF = 0
try:
cur.execute(
"select cislo, konst from zapojeni_vstupy zv inner join zapojeni z on zv.zapojeni_id = z.id where adr_zarizeni = ? and zv.is_jalovy=1",
(s_adr, ))
jal = cur.fetchone()
cur.execute(
"select cislo, konst from zapojeni_vstupy zv inner join zapojeni z on zv.zapojeni_id = z.id where adr_zarizeni = ? and zv.is_cinny=1",
(s_adr, ))
jin = cur.fetchone()
if jal and jin:
imp_jal = round(((vs[jal[0] - 1] * 4) / jal[1]), 2)
imp_cin = round(((vs[jin[0] - 1] * 4) / jin[1]), 2)
if imp_cin == 0:
cosF = 0
else:
cosF = round(imp_cin / math.sqrt(imp_cin**2 + imp_jal**2), 2)
except:
pass
try:
#d = datetime(cas_rok,cas_mes,cas_d,cas_h, cas_m, tzinfo=timezone.utc)
d = d + timedelta(seconds=10) - timedelta(
minutes=15
) # posun o 15 min nazpet + pripocti 10s k datum nektere vychazji 9:59:58
s = int(d.minute / 15) # cislo ctvrthodiny
d = d.replace(minute=0, second=0) #vynuluj minuty
ux = d.timestamp()
ss = ["s1", "s2", "s3", "s4"]
ivs = cis = -1
for vstup in dbvstupy:
try:
cis = vstup[0]
ivs = cis - 1
cur.execute(
"select s1, s2, s3, s4, id from historie where datum=? and adr_zarizeni=? and vstup=?",
(ux, s_adr, cis))
r = cur.fetchone()
odber = round(((vs[ivs] * 4) / vstup[1]), 2)
if r:
rid = r[4]
if r[s] is None: # s - cislo ctvrthodiny
s5v = [r[0], r[1], r[2], r[3]]
s5v[s] = odber
# POZOR TOHLE UPRAVIT /4 JE POUZE PRO KWH JE POTREBA PRIDAT JEDNOTKY Z KTERYCH SE TO PAK BERE ZDA PRUMER NEBO JEN SOUCET
# TO SAME I PRO HMP !!!
s5 = round((int_none(s5v[0]) + int_none(s5v[1]) +
int_none(s5v[2]) + int_none(s5v[3])) / 4,
2)
q = "update historie set {}=?, s5=?, regmax=?, cosf=?, cloud_sent=0 where id=?".format(
ss[s], )
cur.execute(q, (odber, s5, -1, cosF, rid))
else:
q = "update historie set {}_dupl= coalesce({}_dupl, '') || ? || ',', cloud_sent=0 where id=?".format(
ss[s], ss[s])
cur.execute(q, (odber, rid))
else:
# POZOR TOHLE UPRAVIT /4 JE POUZE PRO KWH JE POTREBA PRIDAT JEDNOTKY Z KTERYCH SE TO PAK BERE ZDA PRUMER NEBO JEN SOUCET
# TO SAME I PRO HMP !!!
s5 = round(odber / 4, 2)
q = "insert into historie(datum, vstup, adr_zarizeni, {}, s5, cosf) values(?,?,?, ?,?,?)".format(
ss[s], )
cur.execute(q, (ux, cis, s_adr, odber, s5, cosF))
except Exception as e:
print(
"v8_process_history() foreach: Ex: {} cis: {} ivs: {} vs: {} s: {}"
.format(str(e), cis, ivs, vs, s),
flush=True)
pass
except Exception as e:
print("v8_process_history() FF: Ex: {}".format(str(e)), flush=True)
pass
return
def __is_email_unique__(cursor: sqlite3.Cursor, email: str) -> bool:
stmt = 'SELECT * FROM User WHERE email = "{}"'
query = cursor.execute(stmt.format(email))
result = query.fetchone()
return True if not result else False
def save(self, cursor: sqlite3.Cursor) -> None:
"""Save the properties to the provided database cursor."""
cursor.execute('update players set mu=?, sigma=?, games_count=?, '
'wins_count=? where name=? and ladder=?',
[self.rating.mu, self.rating.sigma, self.games_count,
self.wins_count, self.name, self.ranking.ladder])
def _video_exists(self, c: sqlite3.Cursor, yt_id: str):
return bool(
c.execute('select 1 from videos where yt_id == ?',
(yt_id, )).fetchall())
def update_table_with_data(cursor: sqlite3.Cursor, data: str, station_id: int):
for temp in data:
cursor.execute(
"INSERT INTO temp VALUES(?,?,?,?)",
(station_id, temp["date"], temp["value"], temp["quality"]))
def delete_record(cursor: sqlite3.Cursor, path: str) -> None:
cursor.execute("DELETE FROM records WHERE path=?", (path,))
def _getProfileType(self, container_id: str, db_cursor: db.Cursor) -> Optional[str]:
db_cursor.execute("select id, container_type from containers where id = ?", (container_id, ))
row = db_cursor.fetchone()
if row:
return row[1]
return None
def yield_records(cursor: sqlite3.Cursor) -> T.Generator[Record, None, None]:
cursor.execute("SELECT path, hash, modified FROM records")
while chunk := cursor.fetchmany():
for record in chunk:
yield Record(*record)
def clean_up_old_db(c: sqlite3.Cursor):
log.info("Cleaning up old database")
# Clean up characters
c.execute("SELECT min(id), name as id FROM chars GROUP BY name HAVING COUNT(*) > 1")
rows = c.fetchall()
log.debug("Removing duplicate characters...")
for char_id, name in rows:
c.execute("""UPDATE char_levelups SET char_id = ?
WHERE char_id IN
(SELECT id FROM chars WHERE name = ? ORDER BY id LIMIT 1)""", (char_id, name))
c.execute("""UPDATE char_deaths SET char_id = ?
WHERE char_id IN
(SELECT id FROM chars WHERE name = ? ORDER BY id LIMIT 1)""", (char_id, name))
c.execute("""UPDATE event_participants SET char_id = ?
WHERE char_id IN
(SELECT id FROM chars WHERE name = ? ORDER BY id LIMIT 1)""", (char_id, name))
c.execute("DELETE FROM chars WHERE name = ? AND id != ?", (name, char_id))
log.info(f"Removed {len(rows):,} duplicate characters")
# Clean up deaths
log.debug("Removing duplicate deaths...")
c.execute("""DELETE FROM char_deaths
WHERE rowid NOT IN
(SELECT min(rowid) FROM char_deaths GROUP BY char_id, date)""")
log.info(f"Removed {c.rowcount:,} duplicate deaths")
log.debug("Removing orphaned deaths...")
c.execute("""DELETE FROM char_deaths
WHERE char_id NOT IN
(SELECT id FROM chars)""")
log.info(f"Removed {c.rowcount:,} orphaned deaths")
# Clean up level ups
log.debug("Removing duplicate level ups...")
c.execute("""SELECT min(rowid), min(date), max(date)-min(date) as diff, count() as c, char_id, level
FROM char_levelups
GROUP BY char_id, level HAVING c > 1 AND diff < 30""")
rows = c.fetchall()
count = 0
for rowid, date, diff, _count, char_id, level in rows:
c.execute("""DELETE FROM char_levelups
WHERE rowid != ? AND char_id = ? AND level = ? AND date-30 < ? AND date+30 > ?""",
(rowid, char_id, level, date, date))
count += c.rowcount
log.info(f"Removed {count:,} duplicate level ups")
log.debug("Removing orphaned level ups...")
c.execute("""DELETE FROM char_levelups
WHERE char_id NOT IN
(SELECT id FROM chars)""")
log.info(f"Removed {c.rowcount:,} orphaned levelups")
# Clean up event participants
log.debug("Removing duplicate event participants...")
c.execute("""DELETE FROM event_participants
WHERE rowid NOT IN
(SELECT min(rowid) FROM event_participants GROUP BY event_id, char_id)""")
log.info(f"Removed {c.rowcount:,} duplicate event participants")
log.debug("Removing orphaned event participants...")
c.execute("""DELETE FROM event_participants
WHERE char_id NOT IN
(SELECT id FROM chars)""")
log.info(f"Removed {c.rowcount:,} orphaned event participants")
# Clean up event subscribers
log.debug("Removing duplicate event subscribers...")
c.execute("""DELETE FROM event_subscribers
WHERE rowid NOT IN
(SELECT min(rowid) FROM event_subscribers GROUP BY event_id, user_id)""")
log.info(f"Removed {c.rowcount:,} duplicate event subscribers")
# Remove server properties
log.debug("Removing duplicate server properties...")
c.execute("""DELETE FROM server_properties
WHERE rowid NOT IN
(SELECT min(rowid) FROM server_properties GROUP BY server_id, name)""")
log.info(f"Removed {c.rowcount:,} duplicate server properties")
def vacuum_database(cursor: sqlite3.Cursor) -> None:
cursor.execute("VACUUM")
def check_element_DB(element_name: str, c: sqlite3.Cursor) -> bool:
results = c.execute("SELECT * FROM TransE WHERE qid='" + element_name + "'").fetchone()
return not results is None
def insert_xls_db(cursor: sqlite3.Cursor, xls_tuple):
sql = '''INSERT INTO jobdata_by_state (area_title, occ_code, occ_title, tot_emp, h_pct25, a_pct25, unique_id)
VALUES (?,?,?,?,?,?,?)'''
cursor.execute(sql, xls_tuple)
def setup(c: sqlite3.Cursor) -> None:
for q in QUERIES:
c.execute(q)
def v8_create_zaznam_historie_all(cur: sqlite3.Cursor, adr=''):
if adr == '':
for r in cur.execute(
"select cislozaznamu, id, adr_zarizeni, hw_pozice_zaznamu from zapojeni where hw_typ <> 1 order by master desc, adr asc"
).fetchall():
try:
pos = r[0]
id = r[1]
adr_zarizeni = r[2]
hw_pos = r[3]
if pos > HISTORIE_MAX_ZAZNAMU_V8 or pos < 0:
pos = 0
v = conv_str_hex_adr_to_int(adr_zarizeni)
v8_call_get_history_posl_ulozena_pozice(cur, v)
if pos > hw_pos:
while pos <= HISTORIE_MAX_ZAZNAMU_V8:
v8_call_zaznam_historie(
cur, v, pos,
APP_TYP_DOTAZU.V8_HISTORIE_DATA_ALL.value)
pos += 1
pos = 0
while pos <= hw_pos:
v8_call_zaznam_historie(
cur, v, pos, APP_TYP_DOTAZU.V8_HISTORIE_DATA_ALL.value)
pos += 1
except Exception as e:
print("v8_create_zaznam_historie_all(): Ex: {}".format(str(e)),
flush=True)
else:
cur.execute(
"select id, cislozaznamu, hw_pozice_zaznamu from zapojeni where adr_zarizeni=? and hw_typ <> 1",
(adr, ))
f = cur.fetchone()
if f:
id = f[0]
pos = f[1]
hw_pos = f[2]
if pos > HISTORIE_MAX_ZAZNAMU_V8 or pos < 0:
pos = 0
v = conv_str_hex_adr_to_int(adr)
v8_call_get_history_posl_ulozena_pozice(cur, v)
if pos > hw_pos:
while pos <= HISTORIE_MAX_ZAZNAMU_V8:
v8_call_zaznam_historie(
cur, v, pos, APP_TYP_DOTAZU.V8_HISTORIE_DATA_ALL.value)
pos += 1
pos = 0
while pos <= hw_pos:
v8_call_zaznam_historie(
cur, v, pos, APP_TYP_DOTAZU.V8_HISTORIE_DATA_ALL.value)
pos += 1
def find_element_DB(element_name: str, c: sqlite3.Cursor) -> List[int]:
query_result = c.execute("SELECT * FROM TransE WHERE qid='" + element_name + "'").fetchone()
if query_result:
return list(query_result)
else:
return None
def query(self, cur: sqlite3.Cursor, query: str, args=[]):
res = cur.execute(query, args)
return res.fetchone()[0]
def write_to_sql(self, cursor: Cursor):
fields = [
"species_name",
"variant_name",
"primary_type",
"secondary_type",
"hp",
"attack",
"defense",
"speed",
"special_attack",
"special_defense",
"national_dex_num",
"rby_dex_num",
"gsc_dex_num",
"rse_dex_num",
"frlg_dex_num",
"dp_dex_num",
"plat_dex_num",
"hgss_dex_num",
"bw_dex_num",
"b2w2_dex_num",
"xy_central_dex_num",
"xy_coastal_dex_num",
"xy_mountain_dex_num",
"oras_dex_num",
"sm_dex_num",
"usum_dex_num",
"lets_go_dex_num",
"swsh_dex_num",
"height_meters",
"weight_kilos",
"color",
"shape",
"kind",
"flavor_text",
"leveling_rate",
"base_exp_yield",
"effort_hp",
"effort_attack",
"effort_defense",
"effort_speed",
"effort_special_attack",
"effort_special_defense",
"catch_rate",
"base_friendship",
"primary_egg_group",
"secondary_egg_group",
"male_rate",
"steps_to_hatch_lower",
"steps_to_hatch_upper",
"egg_cycles",
]
fields_str = ", ".join(fields)
values_str = ", ".join("?" * len(fields))
update_str = ",\n".join([f"{i}=?" for i in fields])
entry_sql = (
"INSERT INTO\n\tSpecies (" + fields_str + ")\nVALUES\n\t(" +
values_str +
") ON CONFLICT (species_name, variant_name) DO UPDATE SET\n" +
update_str + ";")
t = self.get_sql_tuple()
cursor.execute(entry_sql, tuple(chain(t, t)))
_id = cursor.lastrowid
self.dex_entry.write_abilities_sql(cursor, _id)
self.move_info.write_moves_sql(cursor, _id)
def _get_namespaces(cursor: sqlite3.Cursor) -> t.Iterable[str]:
cursor.execute('''SELECT name FROM sqlite_master
WHERE type = 'table' AND name NOT like 'sqlite_%';''')
yield from (x[0] for x in _fetch_all(cursor))
def read_all_pooled_address(cur: Cursor) -> set:
"""get all pooled address"""
cur.execute("SELECT `ck` FROM `pool`")
return {addr for (addr, ) in cur}
