def print_tree_of_collection_id(cid, max_level=2): # start / begin if not msc.MSC.row_with_cid_exists(cid, 1): return None ##print TreeNode.get_formatted_node_headers() print msc.prepend_columns_string(TreeNode.get_formatted_node_headers(), "level", 8) TreeNode.rec_print_tree_db(cid, max_level, 0)
def __init__(self): self.config = "" # get host address s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM) s.connect(("8.8.8.8", 80)) host = s.getsockname()[0] self.db = MySQLConnector(host, 'test', 'MyPassword1!', 'BirthdayTracker') self.db._connect()
def rec_print_tree_db(cid, max_level, level=0): # recursive ##print TreeNode.get_node_info_with_cid(cid, level) print msc.prepend_columns_string(TreeNode.get_node_info_with_cid(cid, level), level, 8) new_level = level + 1 if new_level == max_level: return # stop recursing # get collection id of child nodes child_cids = TreeNode.get_child_node_id(cid) if msc.is_data_valid(child_cids): for cid in child_cids: TreeNode.rec_print_tree_db(cid, max_level, new_level)
def signup_handler(self, uname, pswdf, email, fname, lname, number, address, city, zipCode, comments): #validation #__reg exp__: ##pswd psswdMatch = re.compile( r'^(?=.*?\d)(?=.*?[A-Z])(?=.*?[a-z])[A-Za-z\d]{8,}$') ispswdMatch = psswdMatch.match(pswdf) if (ispswdMatch is None): return 'password' ##email emailMatch = re.compile(r'(\b[\w.]+@+[\w.]+.+[\w.]\b)') isemailMatch = emailMatch.match(email) if (isemailMatch is None): return 'email' ##userName userMatch = re.compile(r'^(?=.*?[A-Z])(?=.*?[a-z])[A-Za-z\d]{6,15}$') isnameMatch = userMatch.match(uname) if (isnameMatch is None): return 'user name' #phone number phoneMatch = re.compile(r'(?=.*?\d){10}') isphoneMatch = phoneMatch.match(number) if (isphoneMatch is None): return 'phone number' #insertion params = (str(uname), str(pswdf), str(email), str(fname), str(lname), str(address), str(city), str(number), str(zipCode), str(comments), 2) mysqlptr = MySQLConnector.MySQL_Connector() id = mysqlptr.ExecuteSP_Params('sp_insert_user', params) return id
def add_monitoring(arg, conf_path): l = arg.split('|') ip = None md5 = None for i in l: if i.startswith('ip:'): ip = i[3:] elif i.startswith('md5:'): md5 = i[4:] if md5 is not None and len(md5) != 32: print 'md5 is not valid ! Nothing will be inserted in db !' return if ip is not None: try: socket.inet_aton(ip) except socket.error: print 'ip is not valid ! Nothing will be inserted in db !' return db = MySQLConnector.MySQLConnector(conf_path).connect() cursor = db.cursor() if md5 is not None and ip is not None: cursor.execute( "INSERT INTO http_monitor (peer_ip, md5) VALUES (%s, %s)", (ip, md5)) return if md5 is not None: cursor.execute("INSERT INTO http_monitor (md5) VALUES (%s)", (md5)) return if ip is not None: cursor.execute("INSERT INTO http_monitor (peer_ip) VALUES (%s)", (ip)) return
def main(): # main ### Generate the required objects for running the application ## Connect to a MySQL database and generate an object to store all connection data ## Generate the Bank Session object and check if there is a connection ## if no connection ## do nothing ## loop until client likes to exit application ## Display main bank functions ## Get input of which function to run ## Run the function dbObject = MySQLConnector.mysqlDB(MYSQL_HOST, MYSQL_USERNAME, MYSQL_PASSWORD, MYSQL_DATABASE) bankSession = BankSession(dbObject) if bankSession.dbObj.getConnection() is False: printString("Connection to MySQL DB was UNSUCCESSFUL", length=DEFAULT_PAGE_WIDTH) return False CODE = -1 # Connection to database was successful while CODE and bankSession.dbObj.getConnection() is not False: # Display the options # Get input based on how you want to interact with the BankSession application # Run a function (Create Account, Check Balance, Deposit, Withdraw, Transfer) or exit bankSession.displayOptions() CODE = getInput(prefix="Enter a code") bankSession.selectOption(CODE) # Connection has ended or failed to connect to database printString("Connection has been terminated", length=DEFAULT_PAGE_WIDTH) printStatus(STATUS_EXIT, DEBUG) return True
def video_handler(self,video_name,user_id): #after first checking of video type on API: videoptr = Main.Main() resp_video = videoptr.vid_Analizer(str(video_name))#returns video on access #insert database mysqlptr = MySQLConnector.MySQL_Connector() params = (int(user_id),str(resp_video),str('')) resp_db = mysqlptr.ExecuteSP_Params('sp_insert_video_by_user_id',params) return
def get_parent_node_id(cid): if not msc.MSC.row_with_cid_exists(cid, 1): return None colnames = ["id", "parent_id"] results = msc.MSC.get_collection(colnames, cid) if msc.has_one_row(results): return results[0][1] # get parent_id
def create_and_link_parent_node(self): colnames = ["id", "parent_id"] results = msc.MSC.get_collection(colnames, self.cid) # create TreeNode object for the parent and link it to the current node if msc.has_one_row(results): cid, parent_id = results[0] parent_node = TreeNode(int(parent_id)) parent_node.add_child(self)
def write_rows_to_csv(filename, data, headers=None, additional_lines=None): with open(filename, 'w') as fp: writer = csv.writer(fp, delimiter=',') if additional_lines != None: for line in additional_lines: writer.writerow(line) # write headers to csv file if headers != None: writer.writerow(headers) # write data to csv file for row in data: if msc.is_list_or_tuple(row): formatted_row = row else: formatted_row = [row] writer.writerow( msc.to_utf8_string_for_csv(formatted_row) )
def background(self, fullstr, sig): self.db = MySQLConnector.MySQLConnector().connect() if self.db is None: raise ValueError("Cannot connect to db.") self.cursor = self.db.cursor() if self.cursor is None: raise ValueError("Cannot connect to db.") parser = signature_parser(self.cursor) parser.sig_to_db(fullstr, sig) self.db.close()
def create_and_link_child_nodes(self): colnames = ["id", "parent_id"] results = msc.MSC.get_child_collection(colnames, self.cid) # for each row / node in results, create TreeNode object and link to the current node if msc.is_data_valid(results): for row in results: cid, parent_id = row child_node = TreeNode(int(cid)) self.add_child(child_node)
def get_child_node_id(cid): if not msc.MSC.row_with_cid_exists(cid, 1): return None colnames = ["id"] results = msc.MSC.get_child_collection(colnames, cid) if msc.is_data_valid(results): # get and return only id from the list return [row[0] for row in results]
def get_parent_cids_of_collection_id(cid): """ get id of parent, grandparent, and so on """ # initialize tmp_cid = cid parent_cid = -1 if not msc.MSC.row_with_cid_exists(cid, 1): return None ##current_node = TreeNode.get_node_info_with_cid(cid) ##if current_node is not None: parents.append(current_node) parent_cid_list = [] # append parent node's info string to the list while parent_cid == -1 or msc.is_valid_id(tmp_cid): parent_cid = TreeNode.get_parent_node_id(tmp_cid) if msc.is_valid_id(parent_cid): parent_cid_list.append(parent_cid) tmp_cid = parent_cid parent_cid_list.reverse() # reverse the list -- root first return parent_cid_list
def get_node_info_with_cid(cid, level=0): """ result is determined by TreeNode._node_format """ if TreeNode._node_format == 1: return "{}{}".format(" " * level, cid) elif TreeNode._node_format == 2: # query to get node's data results = msc.MSC.get_collection(TreeNode.get_node_headers(), cid) if msc.has_one_row(results): for row in results: cl_id, parent_id, is_category, name = row ##return "{}{} {} {} {}".format(" " * level, cl_id, parent_id, is_category, name.encode('utf-8')) ##return TreeNode.get_node_format_data( [cl_id, parent_id, is_category, name.encode('utf-8')] ) return TreeNode.get_node_format_data([cl_id, parent_id, is_category, name])
def __init__(self, page_hit, rules_hit, rules_file, conf_file='naxsi-ui.conf'): self.db = MySQLConnector.MySQLConnector(glob_conf_file).connect() self.cursor = self.db.cursor(MySQLdb.cursors.DictCursor) self.rules_list = [] self.final_rules = [] self.base_rules = [] self.page_hit = page_hit self.rules_hit = rules_hit self.core_msg = {} self.extract_core(glob_rules_file)
def __init__(self, page_hit, rules_hit, rules_file): self.db = MySQLConnector.MySQLConnector().connect() self.cursor = self.db.cursor(MySQLdb.cursors.DictCursor) self.rules_list = [] self.final_rules = [] self.base_rules = [] self.page_hit = page_hit self.rules_hit = rules_hit self.core_msg = {} if glob_allow is True and rules_file is not None: print("glob allow from" + rules_file) self.extract_core(rules_file) else: if glob_rules_file is not None: self.extract_core(glob_rules_file)
def fill_db(filename, conf_path): fd = open(filename, 'r') mysqlh = MySQLConnector.MySQLConnector(conf_path) db = mysqlh.connect() sig = '' if db is None: raise ValueError('Cannot connect to db') cursor = db.cursor() if cursor is None: raise ValueError('Cannot connect to db') if re.match("[a-z0-9]+$", mysqlh.dbname) == False: print 'bad db name :)' exit(-2) cursor.execute("DROP DATABASE IF EXISTS %s;" % mysqlh.dbname) cursor.execute("CREATE DATABASE %s;" % mysqlh.dbname) db.select_db(mysqlh.dbname) for line in fd: fullstr = '' if 'NAXSI_FMT' in line: l = line.split(", ") date = ' '.join(l[0].split()[:2]) sig = l[0].split('NAXSI_FMT:')[1][1:] l = l[1:] request_args = {} for i in l: s = i.split(':') request_args[s[0]] = urllib.unquote(''.join(s[1:])) # print 'args are ', request_args if request_args: fullstr = request_args['request'][ 2:-1] + ' Referer : ' + request_args.get( 'referrer', ' "None"')[2:-1].strip( '"\n') + ',Cookie : ' + request_args.get( 'cookie', ' "None"')[2:-1] if sig != '' and fullstr != '': # print "adding %s (%s) " % (sig, fullstr) parser = signature_parser(cursor) parser.sig_to_db(fullstr, sig, date=date) fd.close() db.close()
def signin_handler(self, userName, password): print("Hello") psswdMatch = re.compile( r'^(?=.*?\d)(?=.*?[A-Z])(?=.*?[a-z])[A-Za-z\d]{8,}$') ispswdMatch = psswdMatch.match(password) userMatch = re.compile(r'^(?=.*?[A-Z])(?=.*?[a-z])[A-Za-z\d]{6,15}$') isnameMatch = userMatch.match(userName) ##send to MySQLConnector if (ispswdMatch is not None and isnameMatch is not None): params = (userName, password) mysqlptr = MySQLConnector.MySQL_Connector() row = mysqlptr.ExecuteSP_Params('sp_get_user', params) if (len(row) == 0): return 404 #user not found else: return row #OK else: return 100 #wrong password/username format
# Open each file in the excelFiles folder as dataframe and get back pandas dataframe for each for filename in os.listdir(excelFileDirectory): # Returns file as a pandas dataframe, fileAsDataFrame = OpenFileAsDataframe.openFileAsDataframe(filename) # Add returned dataframe to list of dataframes listOfDataFrames.append(fileAsDataFrame) # Merge list of dataframes together into one, for uploading to MySQL database # (error, inputting 100,000+ records to MySQL in one insertion operation results in missing records) # mergedDataframe = MergeDataframes.mergeDataframes(listOfDataFrames) # Check Database exists, if not create MySQLConnector.createDb() # Drop table if exists MySQLConnector.dropTable() # Check Table exists, if not create MySQLConnector.createTable() # Insert merged dataframe into MySQL database table for dataframe in listOfDataFrames: MySQLConnector.insertDataframeToTable(dataframe, tableName) print("Tables inserted to MySQL Successfully!") # Get sum of department spending per year yearSpendDF = MySQLConnector.selectSpendingPerYear(yearToSearch)
import MySQLdb as MSdb import MySQLConnector as msc # Open database connection db = MSdb.connect("128.199.212.227","flash","1q2w3e4r_","pcms_sim", charset='utf8' ) # prepare a cursor object using cursor() method cursor = db.cursor() # Prepare SQL query to INSERT a record into the database. colnames = ['id', 'name', 'parent_id'] where_clause = "is_category = '%d'" % (1) sql = msc.sql_query(colnames, "collections", where_clause) print sql try: # Execute the SQL command cursor.execute(sql) # Fetch all the rows in a list of lists. results = cursor.fetchall() for row in results: id_col, name, parent_id = row #print id_col, name, parent_id print len(results) except: print "Error: unable to fetch data" # disconnect from server db.close()
__author__ = 'Memray' # -*- coding: utf-8 -*- import sys; import os; import pymysql; from MySQLConnector import *; # Mysql Singleton CLass with pymysql # class mysql_test(object): if __name__ == '__main__': conference_name = "JCDL" outputDirectory = 'H:/Dropbox/PhD@Pittsburgh/1.Researh/NSF/20160125_analyzer_v1/textbook_analyzer/data/acm_paper/'+conference_name+'/' if(not os.path.exists(outputDirectory)): os.makedirs(outputDirectory) conn = MySQLConnector("localhost", "root", "123456", "acm", True) sql = "SELECT article_id, Acm_Article_Title, Acm_Article_Abstract " \ "FROM acm_article AS a,acm_conference_name AS c " \ "WHERE a.Acm_Conference_id=c.acm_conference_id " \ "AND c.acm_conference_name IN ('"+ conference_name + "');" articles = conn.queryrows(sql) # print(len(articles)) for article in articles: file = open(outputDirectory+article[0]+'.txt', 'w') file.write(article[1]+'\n'+article[2]) file.close()
def video_all(self): mysqlptr = MySQLConnector.MySQL_Connector() resp_db = mysqlptr.ExecuteSP('sp_get_all_videos') return resp_db
class Birthday: def __init__(self): self.config = "" # get host address s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM) s.connect(("8.8.8.8", 80)) host = s.getsockname()[0] self.db = MySQLConnector(host, 'test', 'MyPassword1!', 'BirthdayTracker') self.db._connect() def run(self): choice = 1 print("Welome to the program.") print("\nThis program will tell you their birthday or their age.") print( "\nIf the person you mentioned is not available on our database, you can choose to add the person in." ) print("\n1. Look for age 2. Look for birthday") option = int(input("What do you want to do today? ")) while choice: if option == 1: self.getAge() else: self.getBirthday() print("\n0. No 1. Yes") choice = int(input("\nDo you want to look for another person? ")) print("\nHave a good day!") exit() def getBirthday(self): self.config = input( "\nPlease input their Fname and their Lname separated by whitespace(* to show everyone in database): " ) if self.config == '*': self.searchBirthday(self.config, all=1) temp = str(self.db) print("\nHere's the info of everyone you requested for: \n" + temp) else: name = tuple(self.config.split()) result = self.searchBirthday(name) if not result: self.__noName() else: temp = str(self.db) print("\nHere's the info of the person you requested for: \n" + temp) def getAge(self): self.config = input( "\nPlease input their Fname and their Lname separated by whitespace(* to show everyone in database): " ) if self.config == '*': self.searchAge(self.config, all=1) temp = str(self.db) print("\nHere's the info of everyone you requested for: \n" + temp) else: name = tuple(self.config.split()) result = self.searchAge(name) if not result: self.__noName() else: temp = str(self.db) print("\nHere's the info of the person you requested for: \n" + temp) def __noName(self): print("The person appears to not be on our database.") print("\n0. No 1. Yes") choice = int( input( "\nDo you want to put this person into our database for future reference? " )) if choice: self.config = input( "\nPlease input the person Fname, Lname, Bdate(YYYY-MM-DD), and Phone number separated by whitespace: " ) print(self.config) print("Thank you. The data has been inserted.") config = tuple(self.config.split()) print(config) self.insert(config) else: print("\nOkay no worries! Have a good day!") exit() def insert(self, config): query = ("""INSERT INTO Birthday (Fname, Lname, Bdate, PhoneNum) VALUES (%s, %s, %s, %s);""") return self.db._query(query, config) def searchBirthday(self, name, all=0): if not all: query = ("""SELECT Fname, Lname, Bdate FROM Birthday WHERE Fname=%s AND Lname=%s;""") return self.db._query(query, name) else: query = ("""SELECT Fname, Lname, Bdate FROM Birthday;""") return self.db._query(query) def searchAge(self, name, all=0): if not all: query = ( """SELECT Fname, Lname, YEAR(CURDATE()) - YEAR(Bdate) AS age FROM Birthday WHERE Fname=%s AND Lname=%s;""") return self.db._query(query, name) else: query = ( """SELECT Fname, Lname, YEAR(CURDATE()) - YEAR(Bdate) AS age FROM Birthday;""") return self.db._query(query)
def __init__(self): super().__init__() self.mysqlc = MySQLConnector() self.psqlc = PSQLConnector() self.init_ui()
class MainWindow(QWidget): def __init__(self): super().__init__() self.mysqlc = MySQLConnector() self.psqlc = PSQLConnector() self.init_ui() def init_ui(self): """This initiates the window""" # GRID Layout self.grid = QGridLayout() self.grid.setSpacing(5) # First Row self.server_label = QLabel("Server") self.server_label.setMaximumHeight(10) self.port_label = QLabel("Port") self.port_label.setMaximumHeight(10) self.user_label = QLabel("User") self.user_label.setMaximumHeight(10) self.password_label = QLabel("Password") self.password_label.setMaximumHeight(10) self.connect_button = QPushButton("Connect", self) self.connect_button.clicked.connect(self.handle_connect) self.grid.addWidget(self.server_label, 0, 1, 1, 1) self.grid.addWidget(self.port_label, 0, 2, 1, 1) self.grid.addWidget(self.user_label, 0, 3, 1, 1) self.grid.addWidget(self.password_label, 0, 4, 1, 1) self.grid.addWidget(self.connect_button, 1, 6, 1, 2) # Second row self.connection_box = QComboBox() self.connection_box.addItem("MySQL") self.connection_box.addItem("PostgreSQL") self.connection_box.currentTextChanged.connect( self.handle_connector__changed) self.server_input = QLineEdit() self.server_input.setText("127.0.0.1") self.port_input = QLineEdit() self.port_input.setText("3306") self.port_input.setMaximumWidth(60) self.user_input = QLineEdit() self.user_input.setText("root") self.password_input = QLineEdit() self.password_input.setText("root") self.password_input.setEchoMode(QLineEdit.Password) self.grid.addWidget(self.server_input, 1, 1) self.grid.addWidget(self.port_input, 1, 2) self.grid.addWidget(self.user_input, 1, 3) self.grid.addWidget(self.password_input, 1, 4) self.grid.addWidget(self.connection_box, 1, 5, 1, 1) # Third row self.schema_tree = QTreeWidget() self.schema_tree.setColumnCount(1) self.schema_tree.setHeaderLabels(["Data bases"]) self.schema_tree.itemDoubleClicked.connect(self.handle_tree_clicked) self.query_result = QTableWidget() self.query_input = QLineEdit() self.query_button = QPushButton("Run query") self.query_button.clicked.connect(self.handle_run_query) self.info_label = QLabel("") self.info_label.setMaximumHeight(12) self.info_label.setFont(QFont("SansSerif", 8)) self.grid.addWidget(self.info_label, 2, 2, 1, 3) self.grid.addWidget(self.query_input, 3, 2, 1, 4) self.grid.addWidget(self.query_button, 3, 6, 1, 2) self.grid.addWidget(self.query_result, 4, 2, 4, 6) self.grid.addWidget(self.schema_tree, 2, 1, 6, 1) self.setLayout(self.grid) self.setWindowTitle('DB Admin') self.show() def init_schema(self): self.schema_tree.clear() for key in self.schema.keys(): db_tree = QTreeWidgetItem([key]) db_tree.identifier = "DATABASE" db_tree.text_value = key for table in self.schema[key].keys(): table_tree = QTreeWidgetItem([table]) table_tree.identifier = "TABLE" table_tree.database = key table_tree.text_value = table for column in self.schema[key][table]: col = QTreeWidgetItem([column]) col.identifier = "COLUMN" col.text_value = column table_tree.addChild(col) db_tree.addChild(table_tree) self.schema_tree.addTopLevelItem(db_tree) def draw_table(self, result): self.query_result.clear() self.query_result.setRowCount(len(result) - 1) self.query_result.setColumnCount(len(result[0])) self.query_result.setHorizontalHeaderLabels(result[0]) for row_i in range(1, len(result)): for col_i in range(0, len(result[0])): self.query_result.setItem( row_i - 1, col_i, QTableWidgetItem(str(result[row_i][col_i]))) """ EVENT HANDLERS """ def show_modal(self, text): msg = QMessageBox() msg.setText(text) msg.exec_() def handle_connector__changed(self, t): if t == "PostgreSQL": self.port_input.setText("5432") else: self.port_input.setText("3306") def handle_connect(self): if self.connection_box.currentText() == "MySQL": self.mysqlc.set_config(user=self.user_input.text(), host=self.server_input.text(), password=self.password_input.text(), port=self.port_input.text()) self.schema = self.mysqlc.get_schema() self.init_schema() self.show_modal("Connection success") else: self.psqlc.set_config(user=self.user_input.text(), host=self.server_input.text(), password=self.password_input.text()) self.schema = self.psqlc.get_schema() self.init_schema() self.show_modal("Connection success") def handle_run_query(self): if not hasattr(self, 'schema'): self.show_modal("You are not connected to a server") return if self.connection_box.currentText() == "MySQL": self.draw_table( self.mysqlc.query(self.db_use, self.query_input.text())) else: self.draw_table( self.psqlc.query(self.db_use, self.query_input.text())) def handle_tree_clicked(self, item): if self.connection_box.currentText() == "MySQL": if item.identifier == "DATABASE": self.info_label.setText(f"USE : {item.text_value}") self.db_use = item.text_value elif item.identifier == "TABLE": db = item.database table = item.text_value FormDialog.show_form(db=db, table=table, cols=self.schema[db][table], manager=self.mysqlc) query = f"SELECT * FROM {item.text_value}" self.draw_table(self.mysqlc.query(self.db_use, query)) else: if item.identifier == "DATABASE": self.info_label.setText(f"USE : {item.text_value}") self.db_use = item.text_value elif item.identifier == "TABLE": db = item.database table = item.text_value FormDialog.show_form(db=db, table=table, cols=self.schema[db][table], manager=self.psqlc) query = f"SELECT * FROM {item.text_value}" self.draw_table(self.psqlc.query(self.db_use, query)) query = f"SELECT * FROM {item.text_value}" self.draw_table(self.psqlc.query(self.db_use, query))
def get_node_format_data(row): return msc.get_columns_string(row, TreeNode.get_column_widths())
def extract_whitelists(self): self.ret = self.extract_exceptions() print type(self.ret) for d in self.ret: count = self.count_per_exception(d["id"]) d["src_count"] = count self.wls = self.gen_whitelists(d) def extract_exceptions(self): self.cursor.execute("""select exception.exception_id as id, exception.md5 as md5, exception.url as url, exception.count as count, srcpeer.peer_ip as src, dstpeer.peer_host as dst, GROUP_CONCAT("MZ:", match_zone.rule_id, "&", match_zone.zone, "&", match_zone.arg_name, "&" ) as match_zones from exception LEFT JOIN (peer as srcpeer, peer as dstpeer, connections, match_zone) on (connections.src_peer_id = srcpeer.peer_id and connections.dst_peer_id = dstpeer.peer_id and connections.exception_id = exception.exception_id and match_zone.exception_id = exception.exception_id) GROUP BY id;""") data = self.cursor.fetchall() pprint.pprint(data) return data if __name__ == '__main__': db = MySQLConnector.MySQLConnector().connect() cursor = db.cursor(MySQLdb.cursors.DictCursor) bla = signature_extractor(cursor) # bla.extract_exceptions() # bla.extract_whitelists()