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 UpdateRecord(db_connection: MySQLConnection, record: Record):
db_cursor = db_connection.cursor()
sql = "UPDATE records SET title=%s, summary=%s WHERE full_serial_number = %s"
values = (record.title, record.summary, record.full_serial_number)
db_cursor.execute(sql, values)
db_connection.commit()
def UpdateBusinessUnit(db_connection: MySQLConnection, business_unit: int):
db_cursor = db_connection.cursor()
sql = "UPDATE business_units SET title=%s,summary=%s WHERE business_code = %s"
values = (business_unit.title, business_unit.summary,
business_unit.specific_code)
db_cursor.execute(sql, values)
db_connection.commit()
def UpdateDocumentType(db_connection: MySQLConnection, db_cursor: MySQLCursor,
document_type: DocumentType):
sql = "UPDATE business_units SET title=%s, summary=%s WHERE document_code = %s"
values = (document_type.title, document_type.summary,
document_type.document_code)
db_cursor.execute(sql, values)
db_connection.commit()
def CreateNewRecord(db_connection: MySQLConnection,
serial_number:int,
status:str,
title:str,
record_custodian_state:str,
record_revision_state:str,
record_link_state:str,
record_sow_state:str,
record_issue_date_state:str,
record_effective_date_state:str,
record_reaffirmation_date_state:str,
record_protection_lvl_state:str,
record_ec_state:bool,
record_permit_state:str,
record_ecl_state:str,
record_eccn_state:str,
record_usml_state:str,
record_cg_state:str,
record_us_exemption_state:str,
record_ca_exemption_state:str,
record_exp_date_state:str,
summary:str ="NA" ):
db_cursor = db_connection.cursor()
str_code = str(serial_number)
business_series = str_code[0]+"0"
business_code = str_code[0:2]
document_code = str_code[2:4]
sql = "INSERT INTO records (business_series_index, business_code, document_code, full_serial_number, status, title, custodian, revision, \
link, sow_no, issue_date, effective_date, reaffirmation_date, protection_lvl, ec_technical_data, permit, ecl, eccn, usml, cg, us_exemption, \
ca_exemption, exp_date, summary) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) "
value = (business_series,
business_code,
document_code,
str_code,
status,
title,
record_custodian_state,
record_revision_state,
record_link_state,
record_sow_state,
record_issue_date_state,
record_effective_date_state,
record_reaffirmation_date_state,
record_protection_lvl_state,
record_ec_state,
record_permit_state,
record_ecl_state,
record_eccn_state,
record_usml_state,
record_cg_state,
record_us_exemption_state,
record_ca_exemption_state,
record_exp_date_state,
summary)
db_cursor.execute(sql, value)
db_connection.commit()
def UpdateRecord(db_connection: MySQLConnection, record: Record):
db_cursor = db_connection.cursor()
sql = "UPDATE records SET status=%s, title=%s, custodian=%s, revision=%s, link=%s, sow_no=%s, \
issue_date=%s, effective_date=%s, reaffirmation_date=%s, protection_lvl=%s, ec_technical_data=%s, permit=%s, ecl=%s, eccn=%s, usml=%s, cg=%s, \
us_exemption=%s, ca_exemption=%s, exp_date=%s, summary=%s WHERE full_serial_number = %s"
values = (record.status, record.title, record.custodian, record.revision, record.link, record.sow_no, record.issue_date, record.effective_date,\
record.reaffirmation_date, record.protection_lvl, record.ec_technical_data, record.permit, record.ecl, record.eccn, record.usml, record.cg, \
record.us_exemption, record.ca_exemption, record.exp_date, record.summary, record.full_serial_number)
db_cursor.execute(sql,values)
db_connection.commit()
def CreateNewDocumentType(db_connection: MySQLConnection,
document_code: int,
title: str,
summary: str = "NA"):
db_cursor = db_connection.cursor()
sql = "INSERT INTO document_types (document_code, title, summary) VALUES (%s, %s, %s) "
value = (str(document_code), title, summary)
db_cursor.execute(sql, value)
db_connection.commit()
def prepare_business_series(db_connection: MySQLConnection):
db_cursor = db_connection.cursor()
sql = "INSERT INTO business_series (business_series_index, title) VALUES (%s, %s)"
val = [('10', 'Corporate'), ('20', 'Export Controls'),
('30', 'Project Controls'), ('40', 'Engineering'),
('50', 'Operations'), ('60', 'IT'), ('70', 'QMS'),
('80', 'unassigned'), ('90', 'Office General')]
db_cursor.executemany(sql, val)
db_connection.commit()
class MysqlImport(SqlImport):
BATCH_SIZE = 1000
def __init__(self, host, port, user, password, db, log):
self.context = MySQLConnection(host=host,
port=port,
user=user,
password=password,
database=db)
self.cursor = self.context.cursor()
self.log = log
def run(self, parser, limit=0):
lines = []
keys = []
pbar = tqdm(total=parser.get_size())
count = 0
while limit == 0 or count <= limit:
line = parser.parse_line()
if line:
lines.append(tuple(line.values()))
keys = line.keys()
count += 1
if len(lines) >= self.BATCH_SIZE or not line:
try:
self.cursor.executemany(self.build_insert(parser, keys),
lines)
pbar.update(len(lines))
except IntegrityError as e:
self.log.error(str(e))
lines = []
if not line:
break
self.context.commit()
def run_script(self, filepath):
for line in open(filepath):
self.cursor.execute(line)
self.log.info('Ran script', filepath)
def truncate_table(self, table):
self.cursor.execute('TRUNCATE TABLE ' + table)
def close(self):
self.context.commit()
self.cursor.close()
self.context.close()
def CreateNewBusinessUnit(db_connection: MySQLConnection,
title: str,
code: int,
summary: str = "NA"):
db_cursor = db_connection.cursor()
str_code = str(code)
business_series = str_code[0] + "0"
sql = "INSERT INTO business_units (title, summary, business_code, business_series_index) VALUES (%s, %s, %s, %s) "
value = (title, summary, str_code, business_series)
db_cursor.execute(sql, value)
db_connection.commit()
def prepare_document_types(db_connection: MySQLConnection):
db_cursor = db_connection.cursor()
sql = "INSERT INTO document_types (document_code, title, summary) VALUES (%s, %s, %s)"
val = [('0', 'Template', 'NA'), ('01', 'Manuals/Policies', 'NA'),
('02', 'Workflow', 'NA'), ('03', 'Process/Procedure', 'NA'),
('04', 'Work Instruction', 'NA'), ('05', 'Form/Checklist', 'NA'),
('06', 'Presentation', 'NA'), ('07', 'Specification', 'NA'),
('08', 'Report', 'NA'), ('09', 'Drawing', 'NA'),
('10', 'Cover Sheet', 'NA'), ('97', 'Logs', 'NA'),
('98', 'Emails and Communication Records', 'NA'),
('99', 'Non-Communication Records', 'NA')]
db_cursor.executemany(sql, val)
db_connection.commit()
def CreateNewRecord(db_connection: MySQLConnection,
serial_number: int,
title: str,
summary: str = "NA"):
db_cursor = db_connection.cursor()
str_code = str(serial_number)
business_series = str_code[0] + "0"
business_code = str_code[0:2]
document_code = str_code[2:4]
sql = "INSERT INTO records (business_series_index, business_code, document_code, full_serial_number, title, summary) VALUES (%s, %s, %s, %s, %s, %s) "
value = (business_series, business_code, document_code, str_code, title,
summary)
db_cursor.execute(sql, value)
db_connection.commit()
class MySQL:
def __init__(self):
self.host = "localhost"
self.user = "******"
self.password = "******"
self.database = "RaspberryData"
def Conexion(self):
try
self.mydb = MySQLConnection(
host=self.host,
user=self.user,
password=self.password,
database=self.database
)
except Exception as e
self.mydb = MySQLConnection(
host="localhost",
user="******",
password="******",
database="RaspberryData"
)
return "Conexion a MySQL exitosa"
# DHT----------------------------------------------------------------
def guardarDatos(self, data):
self.sql = "insert into Sensors (IDName, Data, Type) values (%s, %s, %s)"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql, (data['name'], str(data['data']), data['type']))
self.mydb.commit()
def getSensors(self):
self.sql = "select * from sensors_inst"
try:
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql)
data = self.mycursor.fetchall()
return data
except Exception as e:
raise e
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 MySQLConnector:
def __init__(self):
try:
self.connection = MySQLConnection(**config)
print("Connection created")
self.cursor = self.connection.cursor(dictionary=True)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
def query(self, query, args=None):
if args is None:
self.cursor.execute(query)
else:
self.cursor.execute(query, args)
def fetchone(self):
return self.cursor.fetchone()
def fetchall(self):
return self.cursor.fetchall()
def insert(self, query):
self.query(query)
self.connection.commit()
def update(self, query, args):
self.query(query, args)
self.connection.commit()
def delete(self, query):
self.query(query)
self.connection.commit()
def close(self):
self.cursor.close()
self.connection.close()
print("Connection closed")
class MySQL:
def __init__(self):
self.host="localhost"
self.user="******"
self.password="******"
self.database="examen"
def Conexion(self):
self.mydb = MySQLConnection(
host=self.host,
user=self.user,
password=self.password,
database=self.database
)
return "Conexion a MySQL exitosa"
#DHT----------------------------------------------------------------
def guardarDatos(self,datos):
self.sql = "insert into SensorDHT (temperatura, humedad, fechaLectura) values (%s, %s, %s)"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql,datos)
self.mydb.commit()
#PIR----------------------------------------------------------------
def guardarDatosPIR(self,estado):
self.sql = "insert into SensorPIR (estado,fechaLectura) values (%s,%s)"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql,estado)
self.mydb.commit()
#HCR----------------------------------------------------------------
def guardarDatosHCR(self,distancia):
self.sql = "insert into SensorHCR (distancia,fechaLectura) values (%s,%s)"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql,distancia)
self.mydb.commit()
def DeleteBusinessUnit(db_connection: MySQLConnection, business_code: int):
db_cursor = db_connection.cursor()
sql = "DELETE FROM business_units WHERE business_code=" + str(
business_code)
db_cursor.execute(sql)
db_connection.commit()
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()
class MySQL:
def __init__(self):
self.host = "localhost"
self.user = "******"
self.password = ""
self.database = "Examen"
self.listaPersonas = []
self.listaPrestamos = []
self.listaMateriales = []
def Conexion(self):
self.mydb = MySQLConnection(host=self.host,
user=self.user,
password=self.password,
database=self.database)
return "Conexion a MySQL exitosa"
#MySQL Personas--------------------------------------------------------------------
def createTablePerson(self):
self.mycursor = self.mydb.cursor()
try:
self.mycursor.execute(
"create table Personas (id int primary key, nombre varchar(25) not null, apellidoPaterno varchar(25) not null, apellidoMaterno varchar(25) not null,correo varchar(50) not null, telefono bigInt not null, edad int not null)"
)
except:
"No se pudo crear"
return "Tabla creada"
def getMaxID(self):
self.mycursor = self.mydb.cursor()
self.mycursor.execute("select max(id) from Personas")
self.myresult = self.mycursor.fetchall()
for x in self.myresult:
self.y = x[0]
return self.y
def insertarPersonas(self, datos):
self.sql = "insert into Personas (id, nombre, apellidoPaterno, apellidoMaterno,correo,telefono, edad) values (%s,%s,%s,%s,%s,%s,%s)"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql, datos)
self.mydb.commit()
return "Persona insertada correctamente a MySQL"
#---------------------------------------------------------------------------------------
#MySQL Prestamos--------------------------------------------------------------------
def createTablePrestamos(self):
self.mycursor = self.mydb.cursor()
try:
self.mycursor.execute(
"create table Prestamos (id int primary key, id_persona int not null, nombre_persona varchar(25) not null,correo_persona varchar(25) not null, fechaPrestamo varchar(50) not null, fechaDevolucion varchar(50)"
)
except:
"No se pudo crear la tabla"
return "tabla prestamos creada"
def insertarPrestamos(self, datos):
self.sql = (
"insert into Prestamos (id, id_persona, nombre_persona, correo_persona, fechaPrestamo, fechaDevolucion) values (%s,%s,%s,%s,%s,%s)"
)
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql, datos)
self.mydb.commit()
return "Prestamo insertado en MySQL"
def getMaxIDPrestamo(self):
self.mycursor = self.mydb.cursor()
self.mycursor.execute("select max(id) from Prestamos")
self.myresult = self.mycursor.fetchall()
for x in self.myresult:
self.y = x[0]
return self.y
#---------------------------------------------------------------------------------------
#MySQL Materiales--------------------------------------------------------------------
def createTablaMateriales(self):
self.mycursor = self.mydb.cursor()
try:
self.mycursor.execute(
"create table Materiales (id_prestamo int not null,cantidad int not null, nombreMaterial varchar(25) not null)"
)
except:
"No se pudo crear la tabla"
return "Tabla Materiales creada"
def insertarMateriales(self, datos):
self.sql = "insert into Materiales (id_prestamo, cantidad, nombreMaterial) values (%s,%s,%s)"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql, datos)
self.mydb.commit()
return "Material registrado en MongoDB"
#---------------------------------------------------------------------------------------
#MySQL Consultas--------------------------------------------------------------------
def verPersonas(self):
self.sql = "SELECT *FROM Personas"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql)
self.myresult = self.mycursor.fetchall()
self.listaPersonas.append(self.myresult)
for self.personas in self.listaPersonas:
return self.personas
#---------------------------------------------------------------------------------------
def verPrestamos(self):
self.sql = "SELECT *FROM Prestamos"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql)
self.myresult = self.mycursor.fetchall()
self.listaPrestamos.append(self.myresult)
for self.prestamos in self.listaPrestamos:
return self.prestamos
#---------------------------------------------------------------------------------------
def verMateriales(self):
self.sql = "SELECT *FROM Materiales"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql)
self.myresult = self.mycursor.fetchall()
self.listaMateriales.append(self.myresult)
for self.materiales in self.listaMateriales:
return self.materiales
#Devolucion-------------------------------------------------------------------------
def registrarDevolucion(self, id_persona):
self.ahora = datetime.now()
self.fecha = self.ahora.strftime("%Y-%m-%d %H:%M:%S")
#self.sql = "update prestamos set fechaDevolucion ="+self.fecha+"where prestamos.id_persona ="+str(id_persona)
self.sql = "UPDATE prestamos SET fechaDevolucion = %s WHERE id_persona = %s"
self.update = (self.fecha, id_persona)
#self.sql = "update prestamos set fechaDevolucion ='devuelto' where id_persona=1"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql, self.update)
self.mydb.commit()
def DeleteDocumentType(db_connection: MySQLConnection, document_code: int):
db_cursor = db_connection.cursor()
sql = "DELETE FROM document_types WHERE document_code=" + str(
document_code)
db_cursor.execute(sql)
db_connection.commit()
if origem[2] != qnt:
cursor.execute(
'update Inventario set Qnt = %d where ID = %d' %
(origem[2] - qnt, origem[0]))
else:
cursor.execute('delete from Inventario where ID = %d' %
origem[0])
# Atualizar Log
cursor.execute(
'insert into Log(LocalID, ItemID, Delta, Quando) values(%d, %d, %d, NOW())'
% (localID, origem[1], qnt))
cursor.execute(
'insert into Log(LocalID, ItemID, Delta, Quando) values(%d, %d, %d, NOW())'
% (origem[3], origem[1], -qnt))
server.commit()
print("Sucesso!")
except Error as e:
print(e)
if opt1 == 2: # Adicionar itens
try:
cursor.execute(
"select ID, ItemID, Qnt, LocalID from Inventario")
entradas = cursor.fetchall()
cursor.execute("select ID, Nome from Itens")
itens = cursor.fetchall()
table(['ID', 'Nome'], itens)
itemID = safe_input('Escolher item: ',
[end[0] for end in itens])
def DeleteRecord(db_connection: MySQLConnection, document_code:int):
db_cursor = db_connection.cursor()
sql = "DELETE FROM records WHERE full_serial_number="+str(document_code)
db_cursor.execute(sql)
db_connection.commit()
class DBDriver:
config_path = 'lib/database.inc'
def __init__(self):
self.__connection = None
self.dirs = None
self.connect()
self.get_dirs()
def __enter__(self):
return self
def read_config(self):
"""
Reads lib/database.inc and extracts the database connection information
This assumes the strings in that file are double-quoted
but won't be bothered if there are special characters in the password or weird whitespace things.
:return: a dictionary containing the connection information
"""
config_dict = {"db_host": None,
"db_user": None,
"db_pass": None,
"db_name": None}
with open(self.config_path, 'r') as config_file:
for line in config_file:
for key in config_dict:
if key in line:
value = line.split('"')[1]
config_dict[key] = value
return config_dict
def connect(self):
"""
Connects to the db using read_db_info()
:return: a connection to the db. Remember to close it
"""
connection_info = self.read_config()
self.__connection = MySQLConnection(user=connection_info["db_user"],
password=connection_info["db_pass"],
host=connection_info["db_host"],
database=connection_info["db_name"])
def get_dirs(self):
"""
Fetches and generates paths to the base, queue, judged, and data directories
"""
curs = self.__connection.cursor() # would use a with block but it's not supported :(
curs.execute('''SELECT BASE_DIRECTORY FROM CONTEST_CONFIG''')
base_dir = curs.fetchone()[0]
self.dirs = {'base': base_dir,
'queue': os.path.join(base_dir, QUEUE_DIR_NAME),
'judged': os.path.join(base_dir, JUDGED_DIR_NAME),
'data': os.path.join(base_dir, DATA_DIR_NAME)}
return self.dirs
curs.close()
def get_forbidden(self, lang_id):
"""
Gets the forbidden words for the language with the given ID
"""
curs = self.__connection.cursor()
curs.execute('''SELECT WORD FROM FORBIDDEN_WORDS WHERE LANGUAGE_ID=%d''' % lang_id)
out = [tupl[0] for tupl in curs.fetchall()]
curs.close()
return out
def get_headers(self, lang_id):
"""
Gets the headers for the language with the given ID
"""
curs = self.__connection.cursor()
curs.execute('''SELECT HEADER FROM HEADERS WHERE LANGUAGE_ID=%d''' % lang_id)
out = [tupl[0] for tupl in curs.fetchall()]
curs.close()
return out
def get_language_info(self, language_name):
"""
:return: A row of the LANGUAGE table specified by language_name
"""
curs = self.__connection.cursor()
curs.execute('''SELECT * FROM LANGUAGE WHERE LANGUAGE_NAME="%s"''' % language_name)
out = curs.fetchone()
curs.close()
return out
def get_queued_submissions(self, queue_id=None, test_compile=False):
"""
:return: Information on all the queued submissions submitted before the contest ends
:param queue_id If specified, only fetch submissions with that ID
:param test_compile True if test compile submissions should only be fetched and false if they should be ignored
"""
curs = self.__connection.cursor()
where_clause = '''WHERE TS < (START_TS + CONTEST_END_DELAY) AND TEST_COMPILE = %s''' % test_compile
if queue_id is not None:
where_clause += ''' AND QUEUE_ID = %d''' % queue_id
curs.execute('''SELECT QUEUE_ID, TEAM_ID, PROBLEM_ID, TS, ATTEMPT, SOURCE_NAME, TEST_COMPILE
FROM QUEUED_SUBMISSIONS, CONTEST_CONFIG
%s
ORDER BY TS''' % where_clause)
out = curs.fetchall()
curs.close()
return out
def enqueue_submission(self, team_id: int, problem_id: int, timestamp: int, attempt: int, source_name: str):
"""
Inserts a submission into the QUEUED_SUBMISSIONS table
:param team_id: The ID of the team in the database
:param problem_id: the ID of the problem this submission is for
:param timestamp: the timestamp of the submission
:param attempt: how many times has this team attempted to solve this problem?
:param source_name: the name of the source file (not a full path)
"""
curs = self.__connection.cursor()
curs.execute('''INSERT INTO QUEUED_SUBMISSIONS (TEAM_ID, PROBLEM_ID, TS, ATTEMPT, SOURCE_NAME)
VALUES (%d, %d, %d, %d, '%s')''' % (team_id, problem_id, timestamp, attempt, source_name))
self.__connection.commit()
curs.close()
def empty_judged(self):
"""
Clears the JUDGED_SUBMISSIONS and AUTO_RESPONSES tables
:return: the contents of the JUDGED_SUBMISSIONS table
"""
curs = self.__connection.cursor()
curs.execute('''SELECT TEAM_ID, PROBLEM_ID, TS, ATTEMPT, SOURCE_NAME FROM JUDGED_SUBMISSIONS''')
out = curs.fetchall()
curs.execute('''TRUNCATE JUDGED_SUBMISSIONS''')
curs.execute('''TRUNCATE AUTO_RESPONSES''')
self.__connection.commit()
curs.close()
return out
def get_ignore_stderr(self):
"""
:return boolean: Whether IGNORE_STDERR is set
"""
curs = self.__connection.cursor()
curs.execute('''SELECT IGNORE_STDERR FROM CONTEST_CONFIG''')
out = curs.fetchone()[0]
curs.close()
return bool(out)
def report_pending(self, one_submission_info):
"""
Reports that the given submission is being judged by moving the row from QUEUED_SUBMISSIONS to JUDGED_SUBMISSIONS
:param one_submission_info: A row from the QUEUED_SUBMISSIONS table
:return: the id of the inserted row
"""
curs = self.__connection.cursor()
one_submission_info = list(one_submission_info) # to make it mutable
for idx, elem in enumerate(one_submission_info):
if elem is None:
one_submission_info[idx] = 'NULL' # SQL syntax
curs.execute('''INSERT INTO JUDGED_SUBMISSIONS (TEAM_ID, PROBLEM_ID, TS, ATTEMPT, SOURCE_NAME, TEST_COMPILE)
VALUES (%s, %s, %s, %s, '%s', %s)''' % tuple(one_submission_info[1:]))
row_id = curs.lastrowid
curs.execute('''DELETE FROM QUEUED_SUBMISSIONS WHERE QUEUE_ID = %d''' % one_submission_info[0])
self.__connection.commit()
curs.close()
return row_id
def report_auto_judgement(self, judged_id, judgement_code, input_file, output_file, error_no=None):
"""
Inserts a row into the AUTO_JUDGMENT table
:param judged_id: returned by report_pending
:param judgement_code: a code that indicates which judgement was made
:param input_file: The name of the file which served as input for this run of the program (may be None if error)
:param output_file: The name of the file which was outputted by the program. May contain error text.
:param error_no: the error number if applicable
"""
curs = self.__connection.cursor()
columns = ['JUDGED_ID', 'OUTPUT_FILE', 'RESPONSE_ID']
values = [judged_id, output_file, judgement_code]
formats = ['%d', "'%s'", '%d']
if input_file is not None:
columns.append('INPUT_FILE')
values.append(input_file)
formats.append("'%s'")
if error_no is not None:
columns.append('ERROR_NO')
values.append(error_no)
formats.append('%d')
columns = '(%s)' % ', '.join(columns)
formats = '(%s)' % ', '.join(formats)
value_clause = formats % tuple(values)
curs.execute('INSERT INTO AUTO_RESPONSES ' + columns + ' VALUES ' + value_clause)
self.__connection.commit()
curs.close()
def make_judgement(self, judged_id):
"""
Acts like a human judgement and makes the reccomended judgement on the given ID
:param judged_id: an ID from the JUDGED_SUBMISSIONS table
"""
curs = self.__connection.cursor()
curs.execute('''SELECT RESPONSE_ID FROM AUTO_RESPONSES WHERE JUDGED_ID = %d''' % judged_id)
judgement_codes = [row[0] for row in curs.fetchall()]
final_code = min(judgement_codes)
curs.execute('''UPDATE JUDGED_SUBMISSIONS SET RESPONSE_ID=%d, JUDGED=1 WHERE JUDGED_ID=%d''' %
(final_code, judged_id))
self.__connection.commit()
curs.close()
def get_response_codes(self):
"""
:return: A dictionary which associates response keywords with their IDs
"""
curs = self.__connection.cursor()
curs.execute('''SELECT RESPONSE_ID, KEYWORD FROM RESPONSES''')
out = {tupl[1]: tupl[0] for tupl in curs.fetchall()}
curs.close()
return out
def __exit__(self, exc_type, exc_val, exc_tb):
self.__del__()
def __del__(self):
self.__connection.close()
def registra_jogador(nome: str, cursor: CursorBase,
con: MySQLConnection) -> int:
"""Registra um novo jogador na partida.
Jogador é um dicionário com as seguintes chaves:
nome (str): O nome do jogador.
navios (list): Informações sobre os navios do jogador.
placar (int): O placar do jogador.
navios_disponíveis (dict): Pares chave valor dizendo quantos navios
de cada tipo o jogador posicionará. Os pares são do
tipo (tipo: int, quantidade: int).
maximo_pontos (str): A quantidade de pontos que o jogador precisa para ganhar.
id (int): A id do jogador na partida.
id_banco (int): A id do jogador no banco de dados.
Args:
nome (str): O nome do jogador.
current_cursor (CursorBase): Cursor aberto que executará as queries.
con (MySQLConnection): Uma conexão estabelecida com o banco de dados.
Returns:
A função retornará:
-2: Se não conseguiu ler a última ID de Jogador.
-1: Se o jogador não foi criado no banco.
0: Se já tem 2 jogadores cadastrados.
1: O jogador foi cadastrado com sucesso.
"""
jogadores = _lista_jogadores()
jogador = dict()
jogador["navios"] = list()
jogador["nome"] = nome
jogador["placar"] = 0
jogador["navios_disponiveis"] = {0: 4, 1: 3, 2: 2, 3: 1}
maximo_pontos = 0
if maximo_pontos == 0:
for navio in jogador["navios_disponiveis"]:
maximo_pontos += jogador["navios_disponiveis"][
navio] * _tamanho_navios[navio]
jogador["maximo_pontos"] = maximo_pontos
if len(jogadores) == 0:
jogador["id"] = 0
elif len(jogadores) == 1:
jogador["id"] = 1
else:
return 0
tamanho_tipo_1 = _tamanho_navios[0]
tamanho_tipo_2 = _tamanho_navios[1]
tamanho_tipo_3 = _tamanho_navios[2]
tamanho_tipo_4 = _tamanho_navios[3]
navios_tipo_1 = jogador["navios_disponiveis"][0]
navios_tipo_2 = jogador["navios_disponiveis"][1]
navios_tipo_3 = jogador["navios_disponiveis"][2]
navios_tipo_4 = jogador["navios_disponiveis"][3]
result = cria_jogador_banco(jogador["nome"], navios_tipo_1, navios_tipo_2,
navios_tipo_3, navios_tipo_4, tamanho_tipo_1,
tamanho_tipo_2, tamanho_tipo_3, tamanho_tipo_4,
0, maximo_pontos, cursor)
if result == 0:
return -1
try:
con.commit()
except Exception as e:
return -1
ultimo_id = le_ultimo_id_jogador(cursor)
if ultimo_id == -1:
return -2
jogador["id_banco"] = ultimo_id
_registra_tabuleiro(jogador, cursor)
_jogadores.append(jogador)
return 1