def insert_into(connection: MySQLConnection, table, ip_ranges: Deque[CIDR], comment: str, delete_old: bool = False):
try:
count_where_list = f"SELECT COUNT(*) FROM {table} WHERE list = %s"
cursor = connection.cursor()
cursor.execute(count_where_list, (FIREWALL_LIST,))
old_count = cursor.fetchone()[0]
cursor.close()
if not delete_old:
print(f"Před vložením nových dat je v tabulce {table} {old_count} řádků.")
else:
print(f"Z tabulky {table} bude odstraněno {old_count} řádků.")
delete_where_list = f"DELETE FROM {table} WHERE list = %s"
cursor = connection.cursor()
cursor.execute(delete_where_list, (FIREWALL_LIST,))
cursor.close()
insert_query = (f"INSERT INTO {table} (address, mask, list, comment, disabled) "
"VALUES (%s, %s, %s, %s, %s)")
cursor = connection.cursor()
cursor.executemany(insert_query,
[(ip.ip, ip.suffix, FIREWALL_LIST, comment, 0) for ip in ip_ranges])
connection.commit()
cursor.close()
print(f"Bylo vloženo {len(ip_ranges)} řádků.")
except mysql.connector.Error:
connection.rollback()
raise
def insert_block(conn: MySQLConnection, block: Block, coin_type: str):
c = conn.cursor()
try:
b = block
with profiler.profile("sql: insert block"):
c.execute(
INSERT_BLOCK,
(b.height, b.block_size, b.block_weight, b.difficulty,
b.cumulative_difficulty, b.hash, b.long_term_weight,
b.major_version, b.minor_version, b.nonce, b.reward,
b.timestamp, b.wide_cumulative_difficulty, b.wide_difficulty))
bt = b.miner_tx
with profiler.profile("insert cb transaction"):
insert_transaction(c, bt, coin_type)
# c.execute(INSERT_TRANSACTION, (bt.version, bt.hash_hex, bt.fee, bt.block_height, bt.unlock_time))
bt.tx_id = c.lastrowid
for t in b.txs:
# hash_value = int(t.hash_hex, 16)
with profiler.profile("insert transaction"):
insert_transaction(c, t, coin_type)
# c.execute(INSERT_TRANSACTION, (t.version, t.hash_hex, t.fee, t.block_height, t.unlock_time))
# t.tx_id = c.lastrowid
# print(f"inserted transaction, rows updated{c.rowcount}")
# for tx_in in t.tx_ins:
# c.execute(INSERT_TXIN, (t.tx_id, tx_in.amount, tx_in.key_image, tx_in.coinbase))
# tx_in.txin_id = c.lastrowid
# for keyoffset in tx_in.key_offsets:
# c.execute(INSERT_KEY_OFFSET, (tx_in.txin_id, keyoffset))
# for od in tx_in.out_details:
# od.tx_id = t.tx_id
# c.execute(INSERT_OUTPUT_DETAILS, (od.tx_id, od.height, od.key_hex, od.mask_hex, od.unlocked))
# for tx_out in t.tx_outs:
# c.execute(INSERT_TXOUT, (t.tx_id, tx_out.amount, tx_out.target_key))
conn.commit()
c.close()
except Exception as e:
logger.exception(f"ERROR AT HEIGHT:{block.height}"
) #should automatically dump the stack
conn.rollback()
c.close()
class MySqlDB:
def __init__(self, settings):
self.settings = dict(**settings)
self.connection = None
def init_connection(self):
if self.connection is None:
self.connection = MySQLConnection(
host = self.settings.get('DB_HOST', '127.0.0.1'),
port = self.settings.get('DB_PORT', 3306),
user = self.settings.get('DB_USER', 'root'),
password = self.settings.get('DB_PASSWORD', ''),
database = self.settings.get('DB_NAME', 'mysql'),
)
def is_connected(self):
if self.connection is None:
self.init_connection()
return self.connection.is_connected()
def reconnect(self, attempts, delay):
if self.connection is None:
self.init_connection()
return self.connection.reconnect()
def rollback(self):
if self.connection is None:
self.init_connection()
self.connection.rollback()
def commit(self):
if self.connection is None:
self.init_connection()
self.connection.commit()
@contextlib.contextmanager
def cursor(self, buffered=True, rollback_on_error=True, **kwargs):
if self.connection is None:
self.init_connection()
cursor = None
try:
kwargs = kwargs or {}
kwargs.setdefault("dictionary", True)
kwargs.setdefault("buffered", buffered)
cursor = self.connection.cursor(**kwargs)
yield cursor
except MySqlError as error:
if rollback_on_error:
self.connection.rollback()
raise error from None
finally:
if cursor:
cursor.close()
def fetchone(self, query, params=None, **kwargs):
if self.connection is None:
self.init_connection()
cursor = None
try:
kwargs = kwargs or {"dictionary": True}
kwargs.setdefault("buffered", True)
cursor = self.connection.cursor(**kwargs)
cursor.execute(query, params)
result = cursor.fetchone()
return result
finally:
if cursor:
cursor.close()
def fetchall(self, query, params=None, **kwargs):
if self.connection is None:
self.init_connection()
cursor = None
try:
kwargs = kwargs or {"dictionary": True}
kwargs.setdefault("buffered", True)
cursor = self.connection.cursor(**kwargs)
cursor.execute(query, params)
result = cursor.fetchall()
return result
finally:
if cursor:
cursor.close()
def close(self):
if self.connection is not None:
self.connection.close()
