def commaParseFile(filename): f=open(filename, "r") line_list = f.read().splitlines() insertBooks = 'INSERT INTO Book VALUES\n' insertIntoWrittenBy = "INSERT INTO WrittenBy VALUES\n" i = 0 while i < len(line_list): line = line_list[i] comma_split = line.split(",") isbn = comma_split[0].strip() title = comma_split[4].strip() publisher = comma_split[5].strip() yearPublished = comma_split[6].strip().split("/")[2] # print(yearPublished) insertBooks += "(\'{}\', \'{}\', {}, {}),\n".format(isbn, title, yearPublished, publisher) if line_list[i+1][0:2] == ' ': insertIntoWrittenBy += parseAuthorIds(line_list[i+1].strip(), isbn) i+=1 i += 1 print(insertIntoWrittenBy) print("------------------") print(insertBooks[-2]) insertIntoWrittenBy = insertIntoWrittenBy[:-2] + ';' insertMembers = insertBooks[:-2] + ';' writeFile(insertMembers, "SQLScripts/insertBooks.sql" ) writeFile(insertIntoWrittenBy, "SQLScripts/insertIntoWrittenBy.sql")
def commaParseFile(filename): f = open(filename, "r") line_list = f.read().splitlines() insertMembers = 'INSERT INTO Member VALUES\n' insertIntoBorrowedBy = "INSERT INTO BorrowedBy VALUES\n" i = 0 while i < len(line_list): line = line_list[i] comma_split = line.split(",") print(comma_split[1].strip().split(" ")) memberId = comma_split[0] firstName = comma_split[1].strip().split(" ")[0] lastName = comma_split[1].strip().split(" ")[1] Gender = comma_split[2].strip() DOB = comma_split[3] DOB = reformatDate(DOB) insertMembers += '({}, \'{}\', \'{}\', \'{}\', \'{}\'),\n'.format( memberId, firstName, lastName, DOB, Gender) while line_list[i + 1][0:2] == ' ': comma_split = line_list[i + 1].split(",") isbn = comma_split[0] checkoutDate = comma_split[1] checkoutDate = reformatDate(checkoutDate) try: checkinDate = comma_split[2] checkinDate = reformatDate(checkinDate) insertIntoBorrowedBy += '(\'{}\', {}, \'{}\', \'{}\'),\n'.format( isbn.strip(), memberId, checkoutDate, checkinDate) except (IndexError): checkinDate = 'Null' insertIntoBorrowedBy += '(\'{}\', {}, \'{}\', {}),\n'.format( isbn.strip(), memberId, checkoutDate, checkinDate) i += 1 try: line_list[i + 1][0:2] except (IndexError): break i += 1 print(insertMembers) print("--------------") print(insertIntoBorrowedBy) insertMembers = insertMembers[:-2] + ";" insertIntoBorrowedBy = insertIntoBorrowedBy[:-2] + ";" writeFile(insertMembers, "SQLScripts/7_insertMembers.sql") writeFile(insertIntoBorrowedBy, "SQLScripts/10_insertIntoBorrowedBy.sql")
def commaParseFile(filename, library): f = open(filename, "r") line_list = f.read().splitlines() insertBooks = 'INSERT INTO Book VALUES\n' insertIntoWrittenBy = "INSERT INTO WrittenBy VALUES\n" insertIntoStoredOn = "INSERT INTO StoredOn VALUES\n" insertIntoShelf = "INSERT INTO Shelf VALUES\n" i = 0 seenFloors = [] while i < len(line_list): line = line_list[i] comma_split = line.split(",") isbn = comma_split[0].strip() numCopies = comma_split[1].strip() shelf = comma_split[2].strip() floor = comma_split[3].strip() title = comma_split[4].strip() publisher = comma_split[5].strip() yearPublished = comma_split[6].strip().split("/")[2] # print(yearPublished) insertBooks += "(\'{}\', \'{}\', {}, {}),\n".format( isbn, title, yearPublished, publisher) insertIntoStoredOn += "(\'{}\', \'{}\', {}, {}),\n".format( isbn, library, shelf, numCopies) shelfFloor = (shelf, floor) if (shelfFloor not in seenFloors): seenFloors.append(shelfFloor) insertIntoShelf += "(\'{}\', {}, {}),\n".format( library, shelf, floor) if line_list[i + 1][0:2] == ' ': insertIntoWrittenBy += parseAuthorIds(line_list[i + 1].strip(), isbn) i += 1 i += 1 print(insertIntoWrittenBy) print("------------------") print(insertBooks[-2]) insertIntoWrittenBy = insertIntoWrittenBy[:-2] + ';' insertMembers = insertBooks[:-2] + ';' insertIntoStoredOn = insertIntoStoredOn[:-2] + ';' insertIntoShelf = insertIntoShelf[:-2] + ";" writeFile(insertMembers, "SQLScripts/{}_insertBooks.sql".format(library)) writeFile(insertIntoWrittenBy, "SQLScripts/{}_insertIntoWrittenBy.sql".format(library)) writeFile(insertIntoStoredOn, "SQLScripts/{}_insertIntoStoredOn.sql".format(library)) writeFile(insertIntoShelf, "SQLScripts/{}_insertIntoShelf.sql".format(library))
def removeDuplicates(file1, file2): f = open(file1, "r") line_list = f.read().splitlines()[1:] lineSet = set() for line in line_list: lineSet.add(line) f.close() f = open(file2, "r") line_list = f.read().splitlines()[1:] for line in line_list: lineSet.add(line) print(lineSet) f.close() insertIntoBooks = "INSERT INTO WrittenBy VALUES\n" for line in lineSet: insertIntoBooks += line + "\n" print(insertIntoBooks) writeFile(insertIntoBooks, "SQLScripts/9_All_inserttoWrittenBy.sql")
def commaParseFile(filename): f = open(filename, "r") line_list = f.read().splitlines() insertPublishers = 'INSERT INTO Publisher VALUES\n' insertPublishersToPhoneNumber = 'INSERT INTO PublisherToPhoneNumber VALUES\n' insertPhoneNumbersPublishers = "INSERT INTO Phone VALUES\n" for line in line_list: split_line = line.split(",") pubID = int(split_line[0]) name = split_line[1][1:] pubPhoneNumber = split_line[2][1:].split(" ")[0] insertPublishers += "({}, \'{}\'),\n".format(pubID, name) parsedPhoneNumbers = parsePhoneNumbers(split_line[2:], pubID) insertPublishersToPhoneNumber += parsedPhoneNumbers[0] insertPhoneNumbersPublishers += parsedPhoneNumbers[1] f.close() insertPublishers = insertPublishers[:-2] + ";" insertPublishersToPhoneNumber = insertPublishersToPhoneNumber[:-2] + ";" writeFile(insertPublishers, "SQLScripts/2_insertPublishers.sql") writeFile(insertPublishersToPhoneNumber, "SQLScripts/5_insertPhoneNumbersToPublishers.sql") writeFile(insertPhoneNumbersPublishers, "SQLScripts/4_insertPhoneNumbersPublishers.sql")