def getPropertyFromTable(cur,conn,property_Obj,status, long_Address): try: ## Preparing the statement to selecta regsister from property table. exists_in_master = "" args = (property_Obj.nodeId, property_Obj.propertyId,property_Obj.propertyName,property_Obj.propertyXLabel,property_Obj.propertyYLabel,property_Obj.propertyXUnit,property_Obj.propertyYUnit,property_Obj.propertyFactor) cur.callproc("SP_SELECT_FROM_property_table",(args)) result = cur.fetchone() ## If there is not register (was not found) with the given PK try to insert it asnew one. if(result != None): print("Send : Status : ",status) ## Prepare the time to save into the new register. str_now = datetime.now().strftime('%Y-%m-%d') registerintoval(property_Obj.nodeId,property_Obj.propertyId,status,time.strftime('%H:%M:%S'),str_now,0) else: ## If finds register with such PK, try to locate it in the Master. args = (property_Obj.nodeId,exists_in_master) cur.callproc("SP_SELECT_FROM_node_table",(args)) result = cur.fetchone() exists_in_master = result[0] ## If the register eve doesn't exist in Master: if(exists_in_master == 1): print("Error : No entry in property table.") sendErrorToPanstamp(property_Obj.nodeId,errorCode=2,long_Address=long_Address) else: print("Error : No entry in both tables.") sendErrorToPanstamp(0,errorCode=3, long_Address=long_Address) except pymysql.Error() as e: conn.rollback() raise finally: cur.close() conn.close()
def getPropertyFromTable(property_Obj, status, Source_Address, endpt, databaseHandler, databaseCursor): try: exists_in_master = "" args = (property_Obj.nodeId, property_Obj.propertyId, property_Obj.propertyName, property_Obj.propertyXLabel, property_Obj.propertyYLabel, property_Obj.propertyXUnit, property_Obj.propertyYUnit, property_Obj.propertyFactor) databaseCursor.callproc("SP_SELECT_FROM_property_table", (args)) result = databaseCursor.fetchone() if result != None: str_now = datetime.now().strftime('%Y-%m-%d') insertIntoValueTable(property_Obj.nodeId, property_Obj.propertyId, status, time.strftime('%H:%M:%S'), str_now, databaseHandler, databaseCursor) else: args = (property_Obj.nodeId, exists_in_master) databaseCursor.callproc("SP_SELECT_FROM_node_table", (args)) result = databaseCursor.fetchone() exists_in_master = result[0] if exists_in_master == 1: print("Error : No entry in property table.", property_Obj.propertyId, property_Obj.nodeId) databaseCursor.callproc("SP_DELETE_FROM_node_table", [property_Obj.nodeId]) databaseHandler.commit() sendToDevice(0x02, 0, Source_Address, endpt) elif exists_in_master == 0: print("Error : No entry in both tables.") sendToDevice(0x02, 0, Source_Address, endpt) except pymysql.Error() as e: databaseHandler.rollback() raise
def put_required_metadata(args): #q_req = "INSERT into required_metadata_info (dataset_id,"+','.join(required_metadata_fields)+")" #q_req = q_req+" VALUES('" cursor = args.db.cursor() #for i,did in enumerate(REQ_METADATA_ITEMS['dataset_id']): for ds in DATASETS: did = DATASETS[ds] vals = [str(did)] fields = [] for key in required_metadata_fields: if did in REQ_METADATA_ITEMS and key in REQ_METADATA_ITEMS[did]: vals.append(REQ_METADATA_ITEMS[did][key]) fields.append(key) f = ",".join(fields) v = "','".join(vals) q_req = "INSERT into required_metadata_info (dataset_id," + f + ")" q_req = q_req + " VALUES('" q2_req = q_req + v + "')" logging.debug(q2_req) print(q2_req) try: cursor.execute(q2_req) except MySQLdb.Error(e): try: logging.debug("MySQL Error [%d]: %s" % (e.args[0], e.args[1])) except IndexError: logging.debug("MySQL Error: %s" % str(e)) args.db.commit()
def _commit(self): try: if self.conn: self.conn.commit() except pymysql.Error as e: # logutil.Logger().error(e) # traceback.print_exc() raise pymysql.Error("Mysql commit failure: %s" % e)
def insertIntoValueTable(node_id,property_id,property_val,time,datex,databaseHandler,databaseCursor): try: args = (node_id,property_id,property_val,time,datex) databaseCursor.callproc("SP_INSERT_INTO_value_table",(args)) databaseHandler.commit() except pymysql.Error() as e: databaseHandler.rollback() raise
def execute(self, sql_query, args=None, freq=0, print_sql=False, w_literal=False, auto_close=AUTO_CLOSE): if "cursor" not in self.thread_data.__dict__: self.thread_data.cursor = None if self.thread_data.cursor is None: self.connect() if not args: args = None if args is not None and w_literal is False: if isinstance(args, (tuple, list)) is True: args = list(map(self.literal, args)) elif isinstance(args, dict) is True: for k, v in args.items(): args[k] = self.literal(v) try: if print_sql is True: print(sql_query) handled_item = self.thread_data.cursor.execute(sql_query, args=args) except pymysql.Error as error: if self.current_transaction is True: self.end_transaction(fail=True) self.close() raise pymysql.Error(error) print(error) if freq >= 3 or error.args[0] in [ 1054, 1064, 1146, 1065, 1040 ]: # 列不存在 sql错误 表不存在 empty_query too_many_connectons raise pymysql.Error(error) self.connect() return self.execute(sql_query=sql_query, args=args, freq=freq + 1, w_literal=True, auto_close=auto_close) if auto_close is True and self.current_transaction is False: self.close() elif self.current_transaction is False: self.thread_data.conn.commit() return handled_item
def insertIntoPropertyTable(propertyObj,databaseHandler,databaseCursor): try: print("Debug: ",propertyObj.nodeId,"|",propertyObj.propertyId,"|", propertyObj.propertyName,"|", propertyObj.propertyXLabel,"|", propertyObj.propertyYLabel,"|", propertyObj.propertyXUnit,"|", propertyObj.propertyYUnit,"|",propertyObj.propertyFactor) args = (propertyObj.nodeId, propertyObj.propertyId, propertyObj.propertyName, propertyObj.propertyXLabel, propertyObj.propertyYLabel, propertyObj.propertyXUnit, propertyObj.propertyYUnit,propertyObj.propertyFactor) databaseCursor.callproc("SP_INSERT_INTO_property_table",(args)) databaseHandler.commit() except pymysql.Error() as e: databaseHandler.rollback() raise
def commit(self): try: if self.__conn: self.__conn.commit() except pymysql.Error as e: raise pymysql.Error(e) finally: if self.__conn: self.close()
def rollback(self): try: if self.__conn: self.__conn.rollback() except pymysql.Error as e: raise pymysql.Error(e) finally: if self.__conn: self.close()
def _rollback(self): try: if self.conn: self.conn.rollback() except pymysql.Error as e: # logutil.Logger().error(e) # traceback.print_exc() raise pymysql.Error("Mysql rollback failure: %s" % e) finally: self._close()
def __init__(self, host: str, user: str, password: str, database: str, port: int): try: self.conn = pymysql.connect(host=host, user=user, password=password, database=database, port=port) except pymysql.Error: raise pymysql.Error( "Something with wrong, failed to connect database")
def query_many(self, sql, args=None): try: if self.__conn is None: self.__init_conn() self.__init_cursor() self.__conn.autocommit = True self.__cursor.executemany(sql, args) self.rows_affected = self.__cursor.rowcount result = self.__cursor.fetchall() return result except pymysql.Error as e: raise pymysql.Error(e)
def exec_sql(self, sql, args=None): try: if self.__conn is None: self.__init_conn() self.__init_cursor() self.__cursor.execute(sql, args) results = self.__cursor.fetchall() return results except pymysql.Error as e: raise pymysql.Error(e) finally: if self.__conn: self.close()
def query_execute(self, sql, args=None): try: self._connect() result_list = [] self.cursor.execute(sql, args) for row in self.cursor.fetchall(): result_list.append(row) return result_list except pymysql.Error as e: # logutil.Logger().error(e) # traceback.print_exc() raise pymysql.Error("Mysql query failure: %s" % e) finally: self._close()
def dml_execute_many(self, sql, args=None): try: self._connect() self.cursor.executemany(sql, args) self._commit() affected = self.cursor.rowcount return affected except pymysql.Error as e: # logutil.Logger().error(e) # traceback.print_exc() self._rollback() raise pymysql.Error("Mysql dml many failure: %s" % e) finally: self._close()
def find(self, sql, args=None): try: if self.__conn is None: self.__init_conn() self.__init_cursor() self.__conn.autocommit = True self.__cursor.execute(sql, args) self.rows_affected = self.__cursor.rowcount results = self.__cursor.fetchall() return results except pymysql.Error as e: raise pymysql.Error(e) finally: if self.__conn: self.close()
def exec_txsql(self, sql, args=None): try: if self.__conn is None: self.__init_conn() self.__init_cursor() if self.__cursor is None: self.__init_cursor() self.rows_affected = self.__cursor.execute(sql, args) return self.rows_affected except pymysql.Error as e: raise pymysql.Error(e) finally: if self.__cursor: self.__cursor.close() self.__cursor = None
def test_db_connection(host, port, user, password, db): msgbox = QtWidgets.QMessageBox() msgbox.setMinimumWidth(500) try: if port.isnumeric(): port = int(port) conn = pymysql.connect(host=host, user=user, password=password, database=db, port=port) if conn.open: msgbox.setText('Connection Successful') msgbox.exec_() else: raise pymysql.Error('conn.open = False') conn.close() except pymysql.Error as error: print(error) msgbox.setText('Connection Failed') msgbox.exec_()
def course_to_db(self, jx0404id, xf, dwmc, jx02id, xkrs, zxs, sksj, xxrs, szkcflmc, syrs, kcmc, skls, skdd, kindName, classKind): with self.conn.cursor() as cursor: try: cursor.execute( "INSERT INTO course(" "jx0404id, xf, dwmc, jx02id, xkrs, zxs, sksj, xxrs, " "szkcflmc, syrs, kcmc, skls, skdd, kindName, classKind) " "VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s'," "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (str(jx0404id), str(xf), str(dwmc), str(jx02id), str(xkrs), str(zxs), str(sksj), str(xxrs), str(szkcflmc), str(syrs), str(kcmc), str(skls), str(skdd), str(kindName), str(classKind))) self.conn.commit() except pymysql.Error: raise pymysql.Error("Failed to insert data") cursor.close()
def connect_to_db(logging, db_host, db_port, db_user, db_pass, db_name): try: # connect to the database logging.info('Connecting to the database...', extra={'topic': 'CM'}) con = pymysql.connect(host=db_host, port=int(db_port), user=db_user, passwd=db_pass, db=db_name) logging.info('Successfully connected to the database!', extra={'topic': 'CM'}) logging.info('Preparing database...', extra={'topic': 'CM'}) cur = con.cursor() return con, cur except Exception as e: raise pymysql.Error(e.args[1])
def connect_to_db(self, db_host, db_port, db_user, db_pass, db_name): try: # connect to the database log(self.LOG_FORMAT, self.logger, 'INFO', 'SQL', 'Connecting to the database...') con = pymysql.connect(host=db_host, port=int(db_port), user=db_user, passwd=db_pass, db=db_name) log(self.LOG_FORMAT, self.logger, 'INFO', 'SQL', 'Successfully connected to the database!') cur = con.cursor() return con, cur except pymysql.Error: raise pymysql.Error(traceback.format_exc()) except Exception: raise Exception(traceback.format_exc())
def insertIntoNodeTable(node, databaseHandler, databaseCursor): try: if_Exists = "" args = (node.nodeTitle, node.endpt, node.measurability, node.nodeId, if_Exists) databaseCursor.callproc("SP_INSERT_INTO_node_table", (args)) result = databaseCursor.fetchall() if_Exists = result[0][0] if (if_Exists == 1): print( "Node already registered, Error code- 0x01 transmitted with Pre-registered node id ", result[0][1]) sendToDevice(0x01, result[0][1], node.long_address, node.endpt) else: print('New node registered, Transmitted Nodeid is -', result[0][1]) sendToDevice(0x00, result[0][1], node.long_address, node.endpt) databaseHandler.commit() except pymysql.Error() as e: databaseHandler.rollback() raise
def execute(self, sql_query, args=None, freq=0, print_sql=False): if self.cursor is None: self.connect() if isinstance(sql_query, unicode): sql_query = sql_query.encode(self.conn.unicode_literal.charset) if args is not None: if isinstance(args, dict): sql_query = sql_query % dict((key, self.literal(item)) for key, item in args.iteritems()) else: sql_query = sql_query % tuple( [self.literal(item) for item in args]) try: if print_sql is True: print(sql_query) handled_item = self.cursor.execute(sql_query) except pymysql.Error as error: print(error) if freq >= 3 or error.args[0] in [1054, 1064, 1146, 1065 ]: # 列不存在 sql错误 表不存在 empty_query raise pymysql.Error(error) self.connect() return self.execute(sql_query=sql_query, freq=freq + 1) return handled_item
def check_tables(self, con, cur, db_prefix): log(self.LOG_FORMAT, self.logger, 'INFO', 'SQL', 'Preparing database...') log(self.LOG_FORMAT, self.logger, 'INFO', 'SQL', 'Checking all tables...') try: # create/check servers table # type: # GN : General server # DB : Database server # EM : Email Server # WB : Website Server # FW : Firewall System # AD : Active Directory Server # VM : Virtual Machine/Hypervisor Server # FS : File Sharing Server # SY : Security-Based Server # MN : Monitoring-based Server # mode: # 0 : enabled # 1 : disabled # 2 : maintenance cur.execute("""CREATE TABLE IF NOT EXISTS {0}_server ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, type CHAR(2) NOT NULL, mode CHAR(1) NOT NULL, hostname VARCHAR(255) NOT NULL, port SMALLINT NOT NULL, PRIMARY KEY (id));""".format(db_prefix)) # create/check error logs table # type: # 0 : warning # 1 : error cur.execute("""CREATE TABLE IF NOT EXISTS {0}_log ( id INTEGER NOT NULL AUTO_INCREMENT, server_id INT NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, type CHAR(1) NOT NULL, msg VARCHAR(500) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (server_id) REFERENCES {0}_server(id));""". format(db_prefix)) # status: # 0: offline # 1: online # create/check ping logs table cur.execute("""CREATE TABLE IF NOT EXISTS {0}_ping ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INT NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), ping INT DEFAULT 0, PRIMARY KEY (id), FOREIGN KEY (server_id) REFERENCES {0}_server(id));""". format(db_prefix)) # status: # 0: offline # 1: online # create/check memory logs table cur.execute("""CREATE TABLE IF NOT EXISTS {0}_memory ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INT NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), ram_percent FLOAT(4,1) DEFAULT 0, ram_used FLOAT(100,2) DEFAULT 0, ram_total FLOAT(100,2) DEFAULT 0, swap_percent FLOAT(4,1) DEFAULT 0, swap_used FLOAT(100,2) DEFAULT 0, swap_total FLOAT(100,2) DEFAULT 0, PRIMARY KEY (id), FOREIGN KEY (server_id) REFERENCES {0}_server(id));""". format(db_prefix)) # status: # 0: offline # 1: online # create/check CPU logs table cur.execute("""CREATE TABLE IF NOT EXISTS {0}_cpu ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INT NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), cpu_percent FLOAT(4,1) DEFAULT 0, PRIMARY KEY (id), FOREIGN KEY (server_id) REFERENCES {0}_server(id));""". format(db_prefix)) # status: # 0: offline # 1: online # create/check network logs table cur.execute("""CREATE TABLE IF NOT EXISTS {0}_network ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INT NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), PRIMARY KEY (id), FOREIGN KEY (server_id) REFERENCES {0}_server(id));""". format(db_prefix)) # create/check network device table cur.execute("""CREATE TABLE IF NOT EXISTS {0}_network_device ( id BIGINT NOT NULL, name VARCHAR(50) DEFAULT \'none\', sent BIGINT DEFAULT 0, received BIGINT DEFAULT 0, PRIMARY KEY (id, name), FOREIGN KEY (id) REFERENCES {0}_network(id));""". format(db_prefix)) # status: # 0: offline # 1: online # create/check load logs table cur.execute("""CREATE TABLE IF NOT EXISTS {0}_load_average ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INT NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), 1m_avg FLOAT(5,2) DEFAULT 0, 5m_avg FLOAT(5,2) DEFAULT 0, 15m_avg FLOAT(5,2) DEFAULT 0, PRIMARY KEY (id), FOREIGN KEY (server_id) REFERENCES {0}_server(id));""". format(db_prefix)) # submit changes to SQL server con.commit() log(self.LOG_FORMAT, self.logger, 'INFO', 'SQL', 'All tables checked!') log(self.LOG_FORMAT, self.logger, 'INFO', 'SQL', 'Database prepared!') except pymysql.Error: raise pymysql.Error(traceback.format_exc()) except Exception: raise Exception(traceback.format_exc())
def test_kill_jobs_of_instance_query_failure(self): self.mock_cursor.execute.side_effect = pymysql.Error( 'Mock query error') self.assertFalse(self.cs.kill_jobs_of_instance('i-1-VM'))
def test_list_ha_workers_query_failure(self): self.mock_cursor.execute.side_effect = pymysql.Error( 'Mock query error') self.assertRaises(pymysql.Error, self.cs.list_ha_workers) self.mock_cursor.close.assert_called_once()
def test_connect_failure(self): self.mock_connect.side_effect = pymysql.Error('Mock connection error') self.assertRaises(pymysql.Error, CosmicSQL, server='localhost', password='******')
def check_tables(logging, con, cur, db_prefix): try: # create/check servers table # type: # GN : General server # DB : Database server # EM : Email Server # WB : Website Server # FW : Firewall System # AD : Active Directory Server # VM : Virtual Machine/Hypervisor Server # FS : File Sharing Server # SY : Security-Based Server # mode: # 0 : enabled # 1 : disabled # 2 : maintenance cur.execute("""CREATE TABLE IF NOT EXISTS {}_server ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, type CHAR(2) NOT NULL, mode CHAR(1) NOT NULL, hostname VARCHAR(255) NOT NULL, port SMALLINT NOT NULL, PRIMARY KEY(id));""".format(db_prefix)) logging.info('Checking {}_server table.'.format(db_prefix), extra={'topic': 'CM'}) # create/check error logs table # type: # 0 : warning # 1 : error cur.execute("""CREATE TABLE IF NOT EXISTS {}_log ( id INTEGER NOT NULL AUTO_INCREMENT, server_name VARCHAR(100) NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, type CHAR(1) NOT NULL, msg VARCHAR(500) NOT NULL, PRIMARY KEY(id));""".format(db_prefix)) logging.info('Checking {}_log table.'.format(db_prefix), extra={'topic': 'CM'}) # create/check ping logs table cur.execute("""CREATE TABLE IF NOT EXISTS {}_ping ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INTEGER NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), ping FLOAT(100,2), PRIMARY KEY(id));""".format(db_prefix)) logging.info('Checking {}_ping table.'.format(db_prefix), extra={'topic': 'CM'}) # create/check memory logs table cur.execute("""CREATE TABLE IF NOT EXISTS {}_memory ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INTEGER NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), ram_percent FLOAT(4,1), ram_used FLOAT(100,2), ram_total FLOAT(100,2), swap_percent FLOAT(4,1), swap_used FLOAT(100,2), swap_total FLOAT(100,2), PRIMARY KEY(id));""".format(db_prefix)) logging.info('Checking {}_memory table.'.format(db_prefix), extra={'topic': 'CM'}) # create/check CPU logs table cur.execute("""CREATE TABLE IF NOT EXISTS {}_cpu ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INTEGER NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), cpu_percent FLOAT(4,1), PRIMARY KEY(id));""".format(db_prefix)) logging.info('Checking {}_cpu table.'.format(db_prefix), extra={'topic': 'CM'}) # create/check network logs table cur.execute("""CREATE TABLE IF NOT EXISTS {}_network ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INTEGER NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), name VARCHAR(50), sent BIGINT, received BIGINT, PRIMARY KEY(id));""".format(db_prefix)) logging.info('Checking {}_network table.'.format(db_prefix), extra={'topic': 'CM'}) # create/check load logs table cur.execute("""CREATE TABLE IF NOT EXISTS {}_load_average ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INTEGER NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), 1m_avg DECIMAL(5,1), 5m_avg DECIMAL(5,1), 15m_avg DECIMAL(5,1), PRIMARY KEY(id));""".format(db_prefix)) logging.info('Checking {}_load_average table.'.format(db_prefix), extra={'topic': 'CM'}) # create/check disk logs table cur.execute("""CREATE TABLE IF NOT EXISTS {}_disk ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INTEGER NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), device VARCHAR(50), percent BIGINT, used BIGINT, total BIGINT, PRIMARY KEY(id));""".format(db_prefix)) logging.info('Checking {}_disk table.'.format(db_prefix), extra={'topic': 'CM'}) # create/check disk logs table cur.execute("""CREATE TABLE IF NOT EXISTS {}_disk_io ( id BIGINT NOT NULL AUTO_INCREMENT, server_id INTEGER NOT NULL, stamp DATETIME DEFAULT CURRENT_TIMESTAMP, status CHAR(1), io DECIMAL(5,2), PRIMARY KEY(id));""".format(db_prefix)) logging.info('Checking {}_disk_iotable.'.format(db_prefix), extra={'topic': 'CM'}) # submit changes to SQL server con.commit() logging.info('Database prepared!', extra={'topic': 'CM'}) except Exception as e: raise pymysql.Error(e.args[1])