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