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")