def multiple_connections():
ctx = len(app.teardown_appcontext_funcs)
db1 = cs50.SQL(db_url)
td1 = (len(app.teardown_appcontext_funcs) == ctx + 1)
db2 = cs50.SQL(db_url)
td2 = (len(app.teardown_appcontext_funcs) == ctx + 2)
return str(td1 and td2)
def main():
# Check the command-line arguments
if (len(sys.argv) != 2):
print("Usage: python roster.py house")
exit(1)
# Set the SQL database
db = cs50.SQL("sqlite:///students.db")
# Query database for all students in house
# output students (sorted by last name -> sort by first name) in this format: name, born year
# Use db.execute to SELECT rows from the table
# Return the value is a list of python dicts,where each dict represents a row in the table
db_output = db.execute(
"SELECT first, middle, last, birth FROM students WHERE house=%s ORDER BY last, first",
sys.argv[1])
# -> Source for "%s" format: https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/
# Print out each student's full name and birth year
for row in db_output:
first = row["first"]
middle = row["middle"]
last = row["last"]
birth = row["birth"]
# Second revision to match changes to import function.
# Turns out in Python, strings can be "falsey" :)
if not middle: # Check for NULL values for middle names
print(f"{first} {last}, born {birth}")
else:
print(f"{first} {middle} {last}, born {birth}")
exit(0)
def main():
""""""
# Check if correct number CL arguments
if len(argv) != 2:
print("ERROR!\nUSAGE: python3 roster.py house_name")
exit(1)
house = argv[1].capitalize()
# Select from database
db = cs50.SQL("sqlite:///students.db")
people = db.execute("SELECT first, middle, last, birth FROM students\
WHERE house = ? ORDER BY last, first", house)
# Print output
for person in people:
# List comprehension
names_to_print = [person[key] for key in person.keys()\
if key != 'birth' and person[key] != None]
# Create names string
names_string = " ".join(names_to_print)
print(names_string, end=',')
print(" born", person['birth'])
def main():
# program should accept the name of a CSV file as a command-line argument & if incorrect print error and exit
while True:
if len(argv) != 2:
print("Error with command_line_arg: ")
exit(1)
else:
# open a database
open("students.db", "w").close()
#give it to sqlite3 as a database
db = cs50.SQL("sqlite:///students.db")
db.execute(
"CREATE TABLE students (id INTEGER PRIMARY KEY AUTOINCREMENT, first VARCHAR(255), middle VARCHAR(255), last VARCHAR(255),house VARCHAR(10), birth INTEGER)"
)
with open(argv[1], "r") as csvfile:
file = DictReader(csvfile)
for row in file:
for name in row["name"].split(','):
char_name = row["name"].split()
first, *second, surname = char_name
second = second[0] if second else None
#print(char_name)
db.execute(
"INSERT INTO students(first, middle, last, house, birth) VALUES (?, ?, ?, ?, ?)",
first, second, surname, row["house"], row["birth"])
return True
def main():
# check for correct number of arguments
if len(argv) != 2:
print("Usage: import.py file.csv")
return 1
# open database
db = cs50.SQL("sqlite:///students.db")
# open csv file from argv
with open(argv[1], "r") as csv_file:
# create dictreader
reader = csv.DictReader(csv_file)
# loop through rows of csv file and add info to table in database
for row in reader:
names = extract_names(row["name"])
if len(names) == 2:
db.execute(
"INSERT INTO students (first, last, house, birth) VALUES(?, ?, ?, ?)",
names[0], names[1], row["house"], row["birth"])
else:
db.execute(
"INSERT INTO students (first, middle, last, house, birth) VALUES(?, ?, ?, ?, ?)",
names[0], names[1], names[2], row["house"], row["birth"])
def main():
if len(argv) < 2:
print("No house specified")
exit(1)
elif argv[1] not in ["Gryffindor", "Hufflepuff", "Ravenclaw", "Slytherin"]:
print("Incorrect house")
exit(1)
db = cs50.SQL("sqlite:///students.db")
for record in db.execute(
"""
SELECT
first, middle, last, birth
FROM
students
WHERE
house = ?
ORDER by desc
""",
argv[1]
):
full_name = "{} {} {}".format(record["first"],
record["middle"] if record["middle"] != "NULL" else "\b",
record["last"])
print(f"{full_name}, born {record['birth']}")
input("Press 'Enter' to close")
print("")
def main():
# check number of inputs in command line
if len(argv) != 2:
print("Usage: roster.py house")
# open database file
db = cs50.SQL("sqlite:///students.db")
# execute sql command which selects rows with correct house
students = db.execute(
"SELECT first, middle, last, birth FROM students WHERE house == '" +
argv[1] + "' ORDER BY last, first")
# loop through out of sql and print results
for student in students:
first = student['first']
middle = student['middle']
last = student['last']
birth = student['birth']
if middle == None:
print(f"{first} {last}, born {birth}")
else:
print(f"{first} {middle} {last}, born {birth}")
def main():
# Check correct usage
if len(argv) != 2:
print("Usage: roster.py house")
exit(1)
# Make sure house given in correct format
house = argv[1].lower().capitalize()
# Check command line argument is a valid house
houses = ["Gryffindor", "Slytherin", "Hufflepuff", "Ravenclaw"]
if house not in houses:
print("Give valid house name.")
exit(1)
# Open students database
db = cs50.SQL("sqlite:///students.db")
# Get sorted list of students in given house; rows is a list of dictionaries, each of which represents a student
rows = db.execute("SELECT first, middle, last, birth FROM students WHERE house = :hs ORDER BY last, first", hs=house)
# Print students
for row in rows:
if row['middle'] is not None:
print(f"{row['first']} {row['middle']} {row['last']}, born {row['birth']}")
else:
print(f"{row['first']} {row['last']}, born {row['birth']}")
exit(0)
def main():
"""Import data from csv file to database"""
# Check for correct number of CL arguments
if (len(argv) != 2):
print("USAGE: python3 import.py csv_file")
exit(1)
# Open csv file and store results in list
students = []
with open(argv[1], "r") as file:
reader = csv.DictReader(file)
for row in reader:
students.append(row)
# Insert into database
db = cs50.SQL("sqlite:///students.db")
for student in students:
first, middle, last = find_name(student)
house = student['house']
birth = student['birth']
db.execute(
"INSERT INTO students (first, middle, last, house, birth)\
VALUES(?, ?, ?, ?, ?)", first, middle, last, house, birth)
exit(0)
def main():
# Ensure that the number of command-line arguments == 2
if len(argv) != 2:
print("Error in Usage ==> filename.py 'house' ")
exit(1)
# store the database into a variable db
db = cs50.SQL("sqlite:///students.db")
# store the house in question into a variable --> not "house" to avoid confusion
stdin_house = argv[1]
# store the SQL output into a variable
stdout_students = db.execute(
"SELECT * FROM students WHERE house = ? ORDER BY last, first",
stdin_house)
# loop through the output to print out the students and birth year
for student in stdout_students:
# store the name and birth year into variables
first_name = student["first"]
middle_name = student["middle"]
last_name = student["last"]
birth = student["birth"]
# if middle name exists, print accordingly. Else, exclude "None"
if middle_name:
print(f"{first_name} {middle_name} {last_name}, born {birth}")
else:
print(f"{first_name} {last_name}, born {birth}")
def main():
if len(argv) != 2:
print("Usage: python import.py data.csv")
exit(1)
# open db
db = cs50.SQL("sqlite:///students.db")
# open and read file
with open(argv[1], "r") as csvfile:
reader = csv.DictReader(csvfile, delimiter=",")
for row in reader:
nameSplit = row["name"].split()
if len(nameSplit) == 2:
firstName = nameSplit[0]
middleName = None
lastName = nameSplit[1]
else:
firstName = nameSplit[0]
middleName = nameSplit[1]
lastName = nameSplit[2]
house = row["house"]
birth = row["birth"]
db.execute(
"insert into students (first, middle, last, house, birth) values(?, ?, ?, ?, ?)",
firstName, middleName, lastName, house, birth)
def insertMovesFromGame(selectedRow, games):
global moveNumber
game = games[selectedRow - 1][columnName]
gameMoves = game.split(" ")
# create moves table
#with open(f"pgn-database.db", "a"):
moveDB = cs50.SQL("sqlite:///pgn-database.db")
#moveDB.execute("CREATE TABLE moves (game_id, moveID INTEGER PRIMARY KEY AUTOINCREMENT, moveNumber INT, color TEXT, move TEXT, FOREIGN KEY(game_id) REFERENCES games(GameID))")
for i in range(len(gameMoves)):
regex = "\d{1,}\.(.*)"
match = re.search(regex, gameMoves[i])
if match:
# print("White move on moveNum ",moveNumber,": ", match.group(1))
moveDB.execute(
"INSERT INTO moves (game_id, moveNumber, color, move) VALUES(?, ?, ?, ?)",
selectedRow, moveNumber, "White", match.group(1))
else:
if (gameMoves[i] == ""):
# game is finished
# print("Result: ", gameMoves[i+1])
break
# print("Black move on moveNum ",moveNumber,": ", gameMoves[i])
moveDB.execute(
"INSERT INTO moves (game_id, moveNumber, color, move) VALUES(?, ?, ?, ?)",
selectedRow, moveNumber, "Black", gameMoves[i])
moveNumber += 1
def main():
# Check correct number of command line arguments
if len(argv) != 2:
print("Usage: import.py filename.csv")
exit(1)
# Open students database
db = cs50.SQL("sqlite:///students.db")
# Read csv file
with open(argv[1], "r") as file:
# Create DictReader
reader = csv.DictReader(file)
# Iterate over csv file
for row in reader:
# Parse name into first, middle, last
first, middle, last = parse_name(row["name"])
db.execute("INSERT INTO students (first, middle, last, house, birth) VALUES (?, ?, ?, ?, ?)",
first, middle, last, row["house"], row["birth"])
exit(0)
def main():
if len(argv) != 2:
print("missing comman-line argument")
exit(1)
# import SQL from cs50, connect database in python
db = db = cs50.SQL("sqlite:///students.db")
# argv[0] is roster.py script
housename = str(argv[1])
# select systax returns a list of dictionaries
# note the use of "?" in select syntax
result = list()
result = db.execute(
"select * from students where house = ? order by last, first",
housename)
for row in result:
firstname = row["first"]
middlename = row["middle"]
lastname = row["last"]
year = row["birth"]
# should be None here, not 'NULL'
if middlename == None:
print(f"{firstname} {lastname}, born {year}")
else:
print(f"{firstname} {middlename} {lastname}, born {year}")
def main():
"""
Main function that imports data from a CSV spreadsheet and
puts it into a SQLite database.
"""
# Check input
if len(sys.argv) != 2:
print("Usage: python import.py [filename.csv]")
sys.exit(1)
# Create empty database
open("students.db", "w").close()
# Open the database for SQlite and initialize values
db = cs50.SQL("sqlite:///students.db")
db.execute("CREATE TABLE students (first TEXT, middle TEXT, last TEXT, house TEXT, birth NUMERIC)")
# Read student database
with open(sys.argv[-1], "r") as f:
# Create reader
reader = csv.DictReader(f, delimiter=',')
# Iterate through database
for row in reader:
names = row['name'].split(' ')
# Check name
if len(names) != 3:
names.insert(1, None)
# Insert into database
db.execute("INSERT INTO students (first, middle, last, house, birth) VALUES(?, ?, ?, ?, ?)",
names[0], names[1], names[-1], row['house'], int(row['birth']))
def main():
# Confirm user input
argc = len(argv)
if argc < 2:
print("Enter house name")
return 1
house_input = argv[1]
db = cs50.SQL("sqlite:///students.db")
data = db.execute(
'SELECT * FROM students WHERE house = ? ORDER BY last ASC, first ASC',
house_input)
for row in data:
first = row['first']
last = row['last']
birth = str(row['birth'])
if row['middle'] == None:
print(first + " " + last + ", born " + birth)
else:
middle = row['middle']
print(first + " " + middle + " " + last + ", born " + birth)
def main():
# Check command line arguments
if len(argv) != 2:
print("Usage: python roster.py [house]")
exit(1)
# Extract specified house
house = argv[1]
# Connect to db
db = cs50.SQL("sqlite:///students.db")
# Query and get a list of dict
sql_string = "SELECT first, middle, last, birth FROM students WHERE house LIKE '" + house + "'" + " ORDER BY last, first ASC"
query_result_list = db.execute(sql_string)
# Print the contents
for row in query_result_list:
# Extract name string
name_string = None
if row["middle"] is None:
name_string = row["first"] + " " + row["last"]
elif row["middle"] is not None:
name_string = row["first"] + " " + row["middle"] + " " + row["last"]
# Print contents
print(name_string + ", born " + str(row["birth"]))
def main():
if len(argv) != 2:
print("Usage: python impot.py data.csv")
exit(1)
dict_database = loadDatabase(argv[1])
# print (dict_database)
STR = []
# STRCount = []
for key in dict_database[0].keys():
STR.append(key)
# print (STR)
open(f"students.db", "w").close() # Making sure that the database is empty
db = cs50.SQL("sqlite:///students.db")
# Creating the database
db.execute(
"CREATE TABLE students (id INT, first TEXT, middle TEXT, last TEXT, house TEXT, birth NUMERIC, PRIMARY KEY(id))"
)
for i in range(len(dict_database)):
name = str(dict_database[i].get('name'))
name = name.split(" ")
if len(name) != 2:
db.execute(
"INSERT INTO students (id, first, middle, last, house, birth) VALUES(?, ?, ?, ?, ?, ?)",
i, name[0], name[1], name[2], dict_database[i].get('house'),
dict_database[i].get(
'birth')) # Loading Data into the database
# name[1] = name[len(name)-1]
else:
db.execute(
"INSERT INTO students (id, first, last, house, birth) VALUES(?, ?, ?, ?, ?)",
i, name[0], name[1], dict_database[i].get('house'),
dict_database[i].get(
'birth')) # Loading Data into the database
def main():
# check number of arguments
if len(sys.argv) != 2:
print("Usage: python roster.py house_name")
exit(1)
house = sys.argv[1]
# open database
db = cs50.SQL("sqlite:///students.db")
# query for given house in alphabetical order
rows = db.execute(
"SELECT first, middle, last, birth FROM students WHERE house = ? ORDER BY last ASC, first ASC",
house)
if len(rows) == 0:
print(f"Sorry, no students match house {house}.")
exit(1)
# print student roster
for row in rows:
first, middle, last, birth = row["first"], row["middle"], row[
"last"], row["birth"]
print(f"{' '.join(filter(None,(first, middle, last)))}, born {birth}")
def main():
if len(argv) != 2:
print("missing command-line arguments")
exit(1)
# import SQL from cs50, connect database in python
db = cs50.SQL("sqlite:///students.db")
#open CSV file
with open(argv[1], "r") as file:
reader = csv.DictReader(file, delimiter=",")
for row in reader:
if row["name"]:
fullname = row["name"].split(" ") # fullname is a list
firstname = fullname[0]
if len(fullname) == 3:
middlename = fullname[1]
lastname = fullname[2]
else:
middlename = None
lastname = fullname[1]
birthday = int(row["birth"])
db.execute(
"insert into students (first, middle, last, house, birth) values (?, ?, ?, ?, ?)",
firstname, middlename, lastname, row["house"], birthday)
def main():
db = cs50.SQL("sqlite:///students.db")
if len(argv) != 2:
print("Incorrect number of arguments.")
exit(1)
with open(argv[1], "r") as file:
reader = csv.DictReader(file)
for row in reader:
name = row["name"].split(" ")
if len(name) == 3:
first = name[0]
middle = name[1]
last = name[2]
elif len(name) == 2:
first = name[0]
middle = None
last = name[1]
house = row["house"]
birth = row["birth"]
db.execute(
"INSERT INTO students (first, middle, last, house, birth) VALUES(?, ?, ?, ?, ?)",
first, middle, last, house, birth)
def getGamesFromDatabase():
global games
with open(f'' + mainDatabase, "r"):
db = cs50.SQL("sqlite:///" + mainDatabase)
games = db.execute("SELECT " + columnName + " FROM " + tableName)
return games
def getMovesFromDatabase(mainDatabase, tableName, columnName, idColumn,
selectedRow):
# read a single move from database
with open(f'' + mainDatabase, "r"):
db = cs50.SQL("sqlite:///" + mainDatabase)
move = db.execute("SELECT " + columnName + " FROM " + tableName +
" WHERE " + idColumn + "=" + str(selectedRow))
return move
def main():
# Check command line arguments
if len(argv) != 2:
print("Usage: python import.py [characters.csv]")
exit(1)
# Open new connection to db. Do not create new DB as we do not have the CREATE statements
db = cs50.SQL("sqlite:///students.db")
# Clear elements inside table
db.execute("DELETE FROM students")
# Open csv file
csv_location = argv[1]
with open(csv_location, "r") as file:
# Create DictReader
# Automatically handles first header row
reader = csv.DictReader(file, delimiter=",")
# Iterate over CSV file
for row in reader:
name_string = row["name"]
house_string = row['house']
birth_string = row["birth"]
# Extract first, middle, last names
first_name, middle_name, last_name = None, None, None
split_string = name_string.split()
if (len(split_string) == 2):
# Only first and last name
first_name = split_string[0]
last_name = split_string[1]
middle_name = None
elif (len(split_string) == 3):
# Have all 3 names
first_name = split_string[0]
last_name = split_string[2]
middle_name = split_string[1]
# Extract birth year
birth_year = int(birth_string)
# Insert elements into SQL table
db.execute(
"INSERT INTO students (first, middle, last, house, birth) \
VALUES(?, ?, ?, ?, ?)", first_name, middle_name,
last_name, house_string, birth_year)
def get_data_from_database(house):
# open that file for SQLite
db = cs50.SQL("sqlite:///students.db")
rows = db.execute(
"SELECT * FROM students WHERE house = ? ORDER BY last, first", house)
for row in rows:
if (row['middle'] == None):
print(f"{row['first']} {row['last']}, born {row['birth']}")
else:
print(
f"{row['first']} {row['middle']} {row['last']}, born {row['birth']}"
)
def store_data_in_db(reader):
# open that file for SQLite
db = cs50.SQL("sqlite:///students.db")
for row in reader:
name = row['name'].split(" ")
first_name = name[0]
middle_name = name[1] if (len(name) == 3) else None
last_name = name[len(name) - 1]
db.execute(
"INSERT INTO students (first, middle, last, house, birth) VALUES (?, ?, ?, ?, ?)",
first_name, middle_name, last_name, row['house'], row['birth'])
def main():
# Initialize local variable(s)
names = []
# Check the command-line arguments
if len(sys.argv) != 2:
print("Usage: python import.py characters.csv")
exit(1)
# Set the SQL database
db = cs50.SQL("sqlite:///students.db")
# Open the CSV characters file, read contents into memory.
with open(sys.argv[1]) as csvfile:
# Create DictReader
characters_csv = csv.DictReader(
csvfile) # Python's CSV module has reader and DictReader.
# Iterate over CSV file
for row in characters_csv:
# For each row, parse name
# Use split methods on strings to split into words
name = row["name"].split()
# debug print(f"Currently Processing: {name}")
# Second submission to change middle name to None, not 'None' if not existing
house = row["house"]
birth = row["birth"]
if (len(name) == 3): # Student has a middle name
first = name[0]
middle = name[1]
last = name[2]
# Insert each student into the "students table of "students.db"
db.execute(
"INSERT INTO students (first, middle, last, house, birth) VALUES(?, ?, ?, ?, ?)",
first, middle, last, house, birth)
else: # Student does not have a middle name
first = name[0]
last = name[1]
# Second submission to change middle name to None, not 'None' if not existing (simply leave blank)
db.execute(
"INSERT INTO students (first, last, house, birth) VALUES(?, ?, ?, ?)",
first, last, house, birth)
exit(0)
def main():
# Reads the first argument if it exists
if (sys.argv.__len__() > 1):
file = sys.argv[1]
open(f"pgn-database.db", "w").close()
db = cs50.SQL("sqlite:///pgn-database.db")
db.execute(
"CREATE TABLE games (GameID INTEGER PRIMARY KEY AUTOINCREMENT, Event TEXT, Site TEXT, Date TEXT, Round TEXT, White TEXT, Black TEXT, Result TEXT, WhiteElo INT, BlackElo INT , ECO TEXT, PGN TEXT)"
)
with open(file, "r") as source:
for line in source:
files.append(line.strip())
print("files: ", files)
for i in range(len(files)):
global flag
flag = False
with open(directory + files[i] + ".pgn", "r") as dataFile:
print(directory + files[i] + ".pgn")
global game
while (not flag):
readMetaData(dataFile)
# one empyt line
dataFile.readline()
#then read the game pgn
readGamePGN(dataFile)
# clear data first
if (game[7]):
game[7] = int(game[7])
else:
game[7] = 0
if (game[8]):
game[8] = int(game[8])
else:
game[8] = 0
# insert datas to db
db.execute(
"INSERT INTO games (Event, Site, Date, Round, White, Black, Result, WhiteElo, BlackElo , ECO, PGN) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
game[0], game[1], game[2], game[3], game[4], game[5],
game[6], game[7], game[8], game[9], game[10])
game = []
def main():
if len(argv) != 2:
print("Usage: python roster.py house")
exit(1)
# open db
db = cs50.SQL("sqlite:///students.db")
results = db.execute(
"select first, middle, last, birth from students where house = ? order by last asc, first asc",
argv[1])
for result in results:
if result["middle"] == None:
print(result["first"], result["last"], "born", result["birth"])
else:
print(result["first"], result["middle"], result["last"], "born",
result["birth"])
def main():
if len(argv) != 2:
print("Usage: import.py houseName")
exit(1)
houseName = argv[1]
db = cs50.SQL("sqlite:///students.db")
students = db.execute("SELECT first, middle, last, birth FROM students WHERE house=? ORDER BY last, first", houseName)
for student in students:
name = getName(student)
birth = student['birth']
print(f"{name}, born {birth}")
