def getAll(): db = DatabaseConnection(Friends.connection_string) data = db.query("SELECT * from friends;") all_friends = [ Friend(friend[0], friend[1], friend[2]) for friend in data ] return all_friends
def usernameUnique(self, username): functionName = 'usernameUnique' validateUN =[] try: # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db') cursor = databaseConnection.cursor() validateUN = cursor.execute('SELECT cust_id FROM Customers WHERE username = ?', (username,)) if validateUN != 0: validateUN = 'unique' # Disconnecting from the DB DatabaseConnection.CloseDBConnection(databaseConnection) except Error as e: print(__name__, ':', functionName, ':', e) raise return validateUN
def load_records(database_file): """\ Opens the specified local database file with the bank account records and store the records in-memory for usage with the aid of the dictionary data structure. Checks that the argument 'database_file' passed in is of type 'str' else an 'Exception' is raised. If the specified database file doesn't exist an 'Exception' is raised. :param database_file: Name of the database file to load records from :return: Dictionary data structure with the bank account records """ if type(database_file) != str: raise Exception( "Invalid argument: database_file of type {} should be: <class 'str'>" .format(type(database_file))) database = DatabaseConnection(database_file) records = dict() result_set = database.sql_statement(database.READ, "SELECT * FROM accounts") for record in result_set: # grab each record from the result-set records[record[4]] = BankAccount(record[0], record[1], record[2], record[3]) database.close() return records
def createBooking(self, tool, returnedUser): functionName = 'createBooking' try: # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename) cursor = databaseConnection.cursor() # get IDs cust_id = UserManager.LoadUserId(self, returnedUser) tool_id = Tools.getId(tool) cursor.execute('INSERT INTO Bookings (tool_id, cust_id) VALUES (?, ?)', (tool_id, cust_id)) databaseConnection.commit() book_id = cursor.lastrowid returnedBooking = Bookings(book_id, tool, returnedUser) # Diconnecting from the DB DatabaseConnection.CloseDBConnection(databaseConnection) return returnedBooking except Error as e: print(__name__, ':', functionName, ':', e) raise
def createTool(self, user, tool_name, tool_cat, tool_desc, price, halfDayPrice): functionName = 'createTool' try: # get ID cust_id = #'1' tool_id = #'33' # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename) cursor = databaseConnection.cursor() cursor.execute('INSERT INTO Tools (tool_id, cust_id, tool_name, tool_cat, tool_desc, price, half_price) VALUES (?, ?, ?, ?, ?, ?, ?)', (tool_id, cust_id, tool_name, tool_cat, tool_desc, price, halfDayPrice)) databaseConnection.commit() # create tool object returnedTool = Tools(tool_id, user, tool_name, tool_cat, tool_desc, price, halfDayPrice) # Disconnecting from the DB DatabaseConnection.CloseDBConnection(databaseConnection) AvailabilityChecker.get_availability(cust_id, tool_id, int(0)) return returnedTool except Error as e: print(__name__, ':', functionName, ':', e) raise
def Rent(self, tool_id, descriptionToolInput): databaseConnection = DatabaseConnection.CreateDBConnection( self.databaseFilename) cursor = databaseConnection.cursor() cursor.execute( 'SELECT book_id FROM bookings WHERE tool_id = ?', tool_id, ) numberOfBookings = cursor.fetchone() previousBooking = int(numberOfBookings) - 1 if previousBooking >= 0: printNoteStateOfItem = ( 'This tool was recently added, please see item description:\n' + descriptionToolInput) else: cursor.execute( 'SELECT note_in FROM bookings WHERE tool_id = ? AND book_id = ?', tool_id, previousBooking, ) printNoteStateOfItem = cursor.fetchone() DatabaseConnection.CloseDBConnection(self.databaseFilename) return printNoteStateOfItem
def book_out(self, tool_id, dateOfBooking, lengthOfBookingInput, Delivery): databaseConnection = DatabaseConnection.CreateDBConnection( self.databasefilename) cursor = databaseConnection.cursor() #creates list of dates to take the availability out for booking_dates = [dateOfBooking] i = 1 while i >= lengthOfBookingInput: booking_dates.append(dateOfBooking + datetime.timedelta(days=1)) i += 1 #sets availability to 0 (rented) for the number of days for dateOfBooking in booking_dates: cursor.execute( 'UPDATE Bookings SET available = 0 WHERE tool_id = ? AND date = ?', tool_id, dateOfBooking, ) #sets delivery status if Delivery == 1: for dateOfBooking in booking_dates: cursor.execute( 'UPDATE Bookings SET Delivery = 1 WHERE tool_id = ? AND date = ?', tool_id, dateOfBooking, ) databaseConnection.commit() DatabaseConnection.CloseDBConnection(self.databasefilename)
def getData(self, customer_email): # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection( self.databaseFilename) cursor = databaseConnection.cursor() cursor.execute( 'SELECT cust_id, F_name, L_name, email FROM customers WHERE email = ?', customer_email, ) customer_row = cursor.fetchone() if (customer_row != None): customer_id = customer_row[0] customer_firstname = customer_row[1] customer_lastname = customer_row[2] customer_email = customer_row[3] # Disconnecting from the DB DatabaseConnection.CloseDBConnection(self.databaseFilename) return customer_id, customer_firstname, customer_lastname, customer_email
def getEpisode(self,episodeQueue,threadID): while True: episode = episodeQueue.get() detailedEpisode = self.apiConn.getDetailedEpisodeInfo(episode['id']) if detailedEpisode: detailedEpisode['seasonID'] = episode['seasonID'] newdb = DatabaseConnection() newdb.updateEpisodeInfo(detailedEpisode) newdb.db.close() episodeQueue.task_done() return None
class SystemLog: def __init__(self): self.db = DatabaseConnection(env.DB_HOST, env.DB_UNAME, env.DB_PASSWORD, env.DB_NAME) def insert(self, function_name, msg): sql = """ insert into tb_sync_log(log_function, log_msg) values('{}', '{}') """ self.db.executeCommit(sql.format(function_name, msg))
def Return(self, noteOnReturn, bookingIdReturnInput): databaseConnection = DatabaseConnection.CreateDBConnection( self.databaseFilename) cursor = databaseConnection.cursor() cursor.execute( 'UPDATE bookings SET note_in = ? WHERE booking_id = ?', noteOnReturn, bookingIdReturnInput, ) databaseConnection.commit() DatabaseConnection.CloseDBConnection(self.databaseFilename)
def __init__(self): # self.maxRowPerWorker = env.VEN_MAX_ROW_PER_WORKER self.workerAddress = env.VEN_WORKER_ADDR self.sinkAddr = env.SINK_ADDR self.context = zmq.Context() self.sender = self.context.socket(zmq.PUSH) self.sender.bind(self.workerAddress) self.processId = os.getpid() self.system = platform.system() self.db = DatabaseConnection(env.DB_HOST, env.DB_UNAME, env.DB_PASSWORD, env.DB_NAME) self.syslog = SystemLog() self.outbox = Outbox(self.db)
def getList(self): # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection( self.databaseFilename) cursor = databaseConnection.cursor() cursor.execute('SELECT customer_email FROM customers;') mailing_list = cursor.fetchall() # Disconnecting from the DB DatabaseConnection.CloseDBConnection(self.databaseFilename) #presenting the Customer list back to us return mailing_list
def loadFutureTools(self, range_start, range_end_days = 42): functionName = 'loadFutureTools' # empty list returnedToolList = [] try: # calculationg range_end range_end = range_start + timedelta(days = range_end_days) userManager = UserManager(self.registeredUser, self.databaseFilename) # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename) cursor = databaseConnection.cursor() cursor.execute("SELECT tool_id, duration FROM Bookings WHERE start_date BETWEEN ? AND ?", (range_start, range_end)) tool_rows = cursor.fetchall() for tool in tool_rows: # Read our values from the record tool_id = tool[0] cust_id = tool[1] tool_name = tool[2] tool_cat = tool[3] tool_desc = tool[4] price = tool[5] halfDayPrice = tool[6] # get ID user = userManager.LoadUserId(cust_id) # create tool singleTool = Tools(tool_id, user, tool_name, tool_cat, tool_desc, price, halfDayPrice) returnedToolList.append(singleTool) # Disconnecting from the DB DatabaseConnection.CloseDBConnection(databaseConnection) return returnedToolList except Error as e: print(__name__, ':', functionName, ':', e) raise
def __init__(self): self.syncDB = DatabaseConnection(env.DB_HOST, env.DB_UNAME, env.DB_PASSWORD, env.DB_NAME) # self.statusDB = DatabaseConnection( # env.DB_HOST, env.DB_UNAME, env.DB_PASSWORD, env.DB_NAME) self.limitRow = env.LIMIT_PROC_ROW self.outbox = Outbox(self.syncDB) self.systemlog = SystemLog() self.inbox = Inbox(self.syncDB) self.outbox = Outbox(self.syncDB) self.clientIdStartFrom = 10 self.updateToZeroHistory = set([]) self.PKFileName = 'pk' self.nextPriToProcess = dict() self.PRIFileName = 'pri'
def getTool(self, customer_id): rental_list = [ 'Tool Name', 'Day Price', 'Rental Duration', 'Total Price' ] grand_total = 0 date = (datetime.date.today().replace(day=1) - datetime.timedelta(days=1)).strftime("%Y-%m") + "-01" date_end = datetime.date.today().replace(day=1) - datetime.timedelta( days=1) delivery_charge = 5 insurance_charge = 5 # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection( self.databaseFilename) cursor = databaseConnection.cursor() cursor.execute( '''SELECT tool_id, price, duration, cust_id, delivery, late_return FROM Bookings WHERE cust_id= ? AND strftime('%s', date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)''' ), ( customer_id, date, date_end, ) tool_row = cursor.fetchall() for booking in tool_row: #checking if customer with given id borrowed any tools and if it was more than one creating a list customer_id = booking[0] tool_id = booking[1] price = booking[2] duration = booking[3] deliveries_charge = booking[4] * delivery_charge late_charge = booking[5] total_price = int(price) * int(duration) + int(late_charge) rental_list.append(tool_id, price, duration, total_price) #rental lines creation based on retrieved data # Disconnecting from the DB DatabaseConnection.CloseDBConnection(self.databaseFilename) grand_total = grand_total + total_price + deliveries_charge + insurance_charge return rental_list, grand_total
def save_to_database(db_file, csv): conn = sqlite3.connect(db_file) # i value to start with i = 266115 for row in csv.itertuples(): comment_id = row[1] comment_content = row[2] comment_date = row[3] comment_original = row[4] last_edit_date = row[5] like_count = row[6] video_id = row[7] if pd.isnull(comment_content): translation = None language = None else: translation, language = DetectLanguage.detect_language(comment_content) data = [[i, comment_id, comment_date, comment_content, comment_original, last_edit_date, like_count, video_id, translation, language]] column_names = ["index", "comment_id", "comment_date", "comment_content", "comment_original", "last_edit_date", "like_count", "video_id", "translation", "language"] sql_dataframe = pd.DataFrame(data, columns=column_names) i += 1 if i == 0: sql_create_comments_table = """ CREATE TABLE IF NOT EXISTS comments ( index INT PRIMARY KEY, comment_id text, comment_content text , comment_date DATE , comment_original text , last_edit_date DATE , like_count FLOAT , video_id text, language text, translation text ); """ DatabaseConnection.create_table(conn, sql_create_comments_table) sql_dataframe.to_sql('comments', conn, if_exists='append', index=False) else: sql_dataframe.to_sql('comments', conn, if_exists='append', index=False) str_i = str(i) file = open(counter, 'w') file.write(str_i) file.close()
class Log: db = None host = 'localhost' username = '******' password = '******' database = 'db_ta_monitor' def __init__(self): self.rowLimit = env.LOG_ROW_LIMIT self.db = DatabaseConnection( env.DB_HOST, env.DB_UNAME, env.DB_PASSWORD, env.DB_NAME) # /////////////////////////////////////////// def getUnproceessLog(self): # query = f"""select * from tb_sync_outbox # where (status = 'waiting') or # (status = 'sent' and retry_again_at <= now()) # order by first_time_occur_at asc, priority asc""" query = f"""select * from tb_sync_outbox where (status = 'waiting') order by first_time_occur_at asc, priority asc""" if (env.LOG_ROW_LIMIT > 0): query += f' limit {env.LOG_ROW_LIMIT}' result = self.db.executeFetchAll(sql=query) return result
class BaseConnection(SockJSConnection): """Base connection implementation""" def on_open(self, info): self.db = DatabaseConnection() def on_message(self, message): body = None if '|' in message: request, body = message.split('|', 1) else: request = message callback = getattr(self, 'on_' + request) if body is not None and len(body) > 0: callback(body) else: callback() @gen.engine def on_auth(self, message): user = yield self.db.get_user(message) self.fetch_user_data(user) yield self.prepare_dataset() params = self.estimate_dataset_params() self.send_dataset(params) def on_close(self): pass # These methods should be implemented def fetch_user_data(self, user): pass def prepare_dataset(self): pass def estimate_dataset_params(self): pass def send_dataset(self, params): pass
def searchFutureBookings(self, registeredUser): functionName = 'SearchFutureBookings' # empty list RetBookingList = [] try: toolManager = ToolManager(self.databaseFilename, registeredUser) start_date = datetime.now() cust_id = #'1' # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename) cursor = databaseConnection.cursor() cursor.execute("SELECT Bookings.book_id, Bookings.tool_id FROM Bookings INNER JOIN Tools ON Bookings.tool_id = Tools.tool_id WHERE Tools.tool_start > ? AND Bookings.cust_id = ?", (start_date, cust_id)) booking_rows = cursor.fetchall() # crete bookings in the DB for Booking in booking_rows: book_id = booking_rows[0] tool_id = booking_rows[1] # load the correct tool tool = toolManager.loadToolId(tool_id) # create booking singleBooking = Booking(book_id, tool, registeredUser) RetBookingList.append(singleBooking) # Disconnect from the DB DatabaseConnection.CloseDBConnection(databaseConnection) return RetBookingList except Error as e: # Catch and display any errors that occur print(__name__, ':', functionName, ':', e) raise
def add_delivery_charge(self, book_id): # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection( self.databaseFilename) #cursor creation for talking to db cursor = databaseConnection.cursor() cursor.execute( 'UPDATE bookings SET delivery = delivery + 1 WHERE book_id = ?', book_id, ) databaseConnection.commit() # Disconnecting from the DB DatabaseConnection.CloseDBConnection(self.databaseFilename)
def searchToolByCategory(self, search_criteria): functionName = 'searchToolByCategory' # empty list returnedToolList = [] try: userManager = UserManager(self.registeredUser, self.databaseFilename) # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename) cursor = databaseConnection.cursor() cursor.execute("SELECT tool_id, cust_id, tool_name, tool_cat, price, tool_desc, half_price FROM Tools WHERE tool_cat = ?", (search_criteria)) tool_rows = cursor.fetchall() for tool in tool_rows: tool_id = tool[0] cust_id = tool[1] tool_name = tool[2] tool_cat = tool[3] price = tool[4] tool_desc = tool[5] half_price = tool[6] # get user user = userManager.LoadUserId(cust_id) # create tool single_tool = Tools(tool_id, user, tool_name, tool_cat, price, tool_desc, half_price) returnedToolList.append(single_tool) # Disconnecting from the DB DatabaseConnection.CloseDBConnection(databaseConnection) return returnedToolList except Error as e: print(__name__, ':', functionName, ':', e) raise
def __init__(self, queue, token, i,time): threading.Thread.__init__(self) self.queue = queue self.dbConn = DatabaseConnection() self.apiConn = APIConnection() self.apiConn.setToken(token) self.threadNumber = i self.episodeThreads = 15 self.lastUpdatedTime = time
def checkIfLate(self, book_id): return_date = datetime.datetime.today() charge_ratio = 2 databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db') cursor = databaseConnection.cursor() cursor.execute('SELECT end_date FROM Bookings WHERE book_id = ?', book_id,) booking_dates = cursor.fetchone() #late_days = 1 late_days = (return_date - booking_dates).days #counts how many days overdue late_charge = charge_ratio * late_days #adds the ratio for later calculation DatabaseConnection.CloseDBConnection(databaseConnection) return late_charge
def load_books(self): self.books = [] books_data = DC.get_data(self.path) for book_from_db_id in books_data: new_book = Book.empty() new_book.load(self.path + '/' + book_from_db_id) new_book.book_ID = book_from_db_id self.books.append(new_book)
def Broken(self, maToolInput, brokenNoteInput): today = datetime.date.today() databaseConnection = DatabaseConnection.CreateDBConnection( self.databaseFilename) cursor = databaseConnection.cursor() cursor.execute('SELECT tool_id FROM bookings WHERE book_id = ?', maToolInput) tool_id = cursor.fetchone() cursor.execute( 'UPDATE bookings SET note_out = ? AND available = 0 WHERE tool_id = ? AND date >= ?', brokenNoteInput, tool_id, today, ) databaseConnection.commit() DatabaseConnection.CloseDBConnection(self.databaseFilename)
def recvAck(self, data): conn = DatabaseConnection(env.DB_HOST, env.DB_UNAME, env.DB_PASSWORD, env.DB_NAME) obox = conn.executeFetchOne( f"select * from tb_sync_outbox where outbox_id = {data['query']}") ack = True if (obox['data']): if (obox['data']['msg_type'] == 'INS'): status = 'need_pk_update' else: status = 'arrived' # ack = self.outbox.update(data={ # 'status': status # }, where_clause={ # 'outbox_id': data['query'] # }) ack = conn.executeCommit( f"update tb_sync_outbox set status='{status}' where outbox_id={data['query']}" )
def __init__(self, dbhost, dbusername, dbpass, dbname, sinkaddr, skey, ivkey): self.key = skey self.iv = ivkey self.context = zmq.Context() self.receiver = self.context.socket(zmq.PULL) self.receiver.bind(sinkaddr) self.syslog = SystemLog() self.db = DatabaseConnection(dbhost, dbusername, dbpass, dbname) self.inbox = Inbox(self.db) self.outbox = Outbox(self.db)
def main(): db_connection = DatabaseConnection.create_connection( r"C:\Users\ba051652\OneDrive - Otto-Friedrich-Universität Bamberg\SS 20\Seminar\Shared folder\sentiwordnet\Database\languages.db") now = datetime.now() current_time = now.strftime("%H:%M:%S") print("Start Time =", current_time) csv_file = read_csv(comments_file) save_to_database(db_connection, csv_file) now = datetime.now() current_time = now.strftime("%H:%M:%S") print("End Time =", current_time)
class ProductDao(object): def __init__(self): self.databaseConnection = DatabaseConnection() def getMaxProductId(self): lastProductId = 0 connection = self.databaseConnection.getConnection() cursor = connection.cursor() cursor.execute('SELECT MAX(PRODUCTS_ID) FROM CON_PRODUCTS') for row in cursor: lastProductId = row[0] return lastProductId def insertMany(self, products): connection = self.databaseConnection.getConnection() cursor = connection.cursor() data = [] lastProductId = self.getMaxProductId() curretDatetime = datetime.datetime.now() for product in products: lastProductId += 1 record = (lastProductId, product['FILE_ID'], product['VARIANT_ID'], product['VARIANT_CORR'], product['PARTNUMBER'], product['NAME'], product['LONGDESCRIPTION'], product['CATEGORY_CODE'], product['BRAND'], product['STATUS'], product['PRICE'], product['ORIGIN_PRICE'], product['SYNC'], product['SELLER_ID'], product['PRODUCT_SKU'], product['PRODUCT_SKU_PARENT'], curretDatetime, curretDatetime) data.append(record) cursor.executemany( """ INSERT INTO CON_PRODUCTS (PRODUCTS_ID, FILE_ID, VARIANT_ID, VARIANT_CORR, PARTNUMBER, NAME , LONGDESCRIPTION, CATEGORY_CODE, BRAND, STATUS, PRICE, ORIGIN_PRICE, SYNC, SELLER_ID, PRODUCT_SKU, PRODUCT_SKU_PARENT, CREATE_DATE, LAST_DATE) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18)""", data) connection.commit() cursor.close() connection.close()
def setUpClass(cls): with open('password.txt') as f: password = f.readline() cls.dbcon = DatabaseConnection('dbname=test user=postgres password='******''' INSERT INTO seasons (season_name, start_date, end_date, total_games) VALUES (%s, %s, %s, %s); ''', ('Test Season', '2013-05-30', '2014-04-23', 43)) cls.dbcon.cursor.execute( ''' INSERT INTO contestants (name, notes, games_played, total_winnings) VALUES (%s, %s, %s, %s); ''', ('John Smith', 'A teacher from Akron, Ohio', 2, 19400)) cls.dbcon.cursor.execute( ''' INSERT INTO contestants (name, notes, games_played, total_winnings) VALUES (%s, %s, %s, %s); ''', ('Jane Doe', 'A software engineer from Atlanta, Georgia', 1, 2000)) cls.dbcon.cursor.execute( ''' INSERT INTO contestants (name, notes, games_played, total_winnings) VALUES (%s, %s, %s, %s); ''', ('Joe Schmo', 'An accountant from San Diego, California', 1, 1000)) cls.dbcon.cursor.execute( ''' INSERT INTO games (episode_num, season_id, air_date, notes, contestant1, contestant2, contestant3, winner, score1, score2, score3) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s); ''', (1, 1, '2013-08-24', 'Tournament of Champions Game 1', 1, 2, 3, 1, 3400, 1400, 1200)) cls.dbcon.cursor.execute( ''' INSERT INTO clues (game_id, value, daily_double, round, category, clue, response, correct_contestant) VALUES (%s, %s, %s, %s, %s, %s, %s, %s); ''', (1, 400, 'No', 'Jeopardy', 'State Capitals', 'The capital of Indiana', 'Indianapolis', 1)) print('Connected:', cls.dbcon, "\n")
def markAvailability(self, tool_id): functionName = 'markAvailability' try: # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db') cursor = databaseConnection.cursor() cursor.execute('UPDATE Tools SET available = 0 WHERE tool_id = ?', (tool_id)) databaseConnection.commit() # Disconnecting from the DB DatabaseConnection.CloseDBConnection(databaseConnection) except Error as e: print(__name__, ':', functionName, ':', e) raise
def bookOutNotes(self, book_id, brokenNoteInput): functionName = 'bookOutNotes' try: # Connecting to the DB databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db') cursor = databaseConnection.cursor() cursor.execute('UPDATE Bookings SET note_out = ? WHERE book_id = ?', (brokenNoteInput, book_id)) databaseConnection.commit() # Disconnecting from the DB DatabaseConnection.CloseDBConnection(databaseConnection) except Error as e: print(__name__, ':', functionName, ':', e) raise
def load_records(database_file): """\ Opens the specified local database file with the bank account records and store the records in-memory for usage with the aid of the dictionary data structure. Checks that the argument 'database_file' passed in is of type 'str' else an 'Exception' is raised. If the specified database file doesn't exist an 'Exception' is raised. :param database_file: Name of the database file to load records from :return: Dictionary data structure with the bank account records """ if type(database_file) != str: raise Exception( "Invalid argument: database_file of type {} should be: <class 'str'>".format(type(database_file))) database = DatabaseConnection(database_file) records = dict() result_set = database.sql_statement(database.READ, "SELECT * FROM accounts") for record in result_set: # grab each record from the result-set records[record[4]] = BankAccount(record[0], record[1], record[2], record[3]) database.close() return records
for row in reader: output_data.append(row) return output_data if __name__ == '__main__': args = parse_args() service_code_file = '../resources/fcc_radio_services_map.csv' station_class_file = '../resources/fcc_station_class_map.csv' print '[+] Parsing Data Files' service_code_data = parse_file(service_code_file) station_class_data = parse_file(station_class_file) print '[+] Data Parsed Successfully...' print '[+] Connecting To Database' database = DatabaseConnection(args.db, args.host, args.port, args.username, args.password) database.connect() print '[+] Database Connection Established...' print '[+] Inserting Data' database.insert_services(service_code_data) database.insert_station_classes(station_class_data) print '[+] Data Insertion Complete...' print '[+] Committing Changes and Disconnecting' database.disconnect() print '[+] All Changes Persisted, Database Connection Terminated...' print '[+] Complete'
def on_open(self, info): self.db = DatabaseConnection()
class processSerie(threading.Thread): def __init__(self, queue, token, i,time): threading.Thread.__init__(self) self.queue = queue self.dbConn = DatabaseConnection() self.apiConn = APIConnection() self.apiConn.setToken(token) self.threadNumber = i self.episodeThreads = 15 self.lastUpdatedTime = time def run(self): while True: updatedSerie = self.queue.get() id = updatedSerie[0] lastupdated = updatedSerie[1] if self.lastUpdatedTime < lastupdated: start = datetime.datetime.now() serieData = self.apiConn.getSerieData(id) if serieData: firstCharsSerieName = serieData['seriesName'][:2] # Duplicates in the API start with ** so these should not be handled and updated. if firstCharsSerieName != "**": self.dbConn.updateSerieInfo(serieData) part1 = (datetime.datetime.now() - start).seconds episodes = self.apiConn.getUpdatedEpisodes(id) part2 = ((datetime.datetime.now() - start).seconds) - part1 EpisodeQueue = Queue() episodeCounter = 0 if episodes: distinctSeasons = self.apiConn.getDistinctSeasons(episodes) seasonInfo = self.dbConn.getSeasonInfo(distinctSeasons) for episode in episodes['data']: episode['seriesID'] = id if episode['airedSeason'] and seasonInfo: episode['airedSeason'] = int(episode['airedSeason']) episode['seasonID'] = seasonInfo[episode['airedSeason']] EpisodeQueue.put(episode) episodeCounter += 1 print "[%s]Starting: %s, %s episodes.\r" % (datetime.datetime.now().strftime('%H:%M:%S'),serieData['seriesName'],episodeCounter) for i in range(self.episodeThreads): t = threading.Thread(target=self.getEpisode,args=(EpisodeQueue,i)) t.daemon = True t.start() # print "%s: Processing %s S%sE%s - %s" % (self.threadNumber,serieData['seriesName'],episode['airedSeason'],episode['airedEpisodeNumber'],episode['episodeName'].encode('utf-8')) # detailedEpisode = self.apiConn.getDetailedEpisodeInfo(episode['id']) # self.db.updateEpisodeInfo(detailedEpisode) EpisodeQueue.join() end = (datetime.datetime.now() - start).seconds print "[%s]Finished: %s, %s episodes.\r" % (datetime.datetime.now().strftime('%H:%M:%S'),serieData['seriesName'],episodeCounter) self.queue.task_done() def getEpisode(self,episodeQueue,threadID): while True: episode = episodeQueue.get() detailedEpisode = self.apiConn.getDetailedEpisodeInfo(episode['id']) if detailedEpisode: detailedEpisode['seasonID'] = episode['seasonID'] newdb = DatabaseConnection() newdb.updateEpisodeInfo(detailedEpisode) newdb.db.close() episodeQueue.task_done() return None
for row in reader: output_data.append(row) return output_data if __name__ == '__main__': args = parse_args() state, county, file_type = parse_file_name(args.input_file) print '[+] Parsing Data File' data = parse_file(args.input_file) print '[+] Data Parsed Successfully...' print '[+] Connecting To Database' database = DatabaseConnection(args.db, args.host, args.port, args.username, args.password) database.connect() print '[+] Database Connection Established...' print '[+] Inserting Data For {0}, {1}'.format(county, state) if file_type == 'signal': database.insert_signals(state, county, data) else: database.insert_licenses(state, county, data) print '[+] Data Insertion Complete...' print '[+] Committing Changes and Disconnecting' database.disconnect() print '[+] All Changes Persisted, Database Connection Terminated...' print '[+] Complete'