def main ():
download = get_string("CSV location: ")
db = SQL("sqlite:///main.db")
with open(download, mode='r') as infile:
reader = csv.DictReader(infile)
for row in reader:
country = row["name"]
latitude = row["latitude"]
longitude = row["longitude"]
iso_country = row["country"]
db.execute("CREATE TABLE IF NOT EXISTS countries (country TEXT, latitude REAL, longitude REAL, iso_country TEXT)")
db.execute("INSERT INTO 'countries'('country', 'latitude', 'longitude', 'iso_country') VALUES(:country, :latitude, :longitude, :iso_country)",
country = country, latitude = latitude, longitude = longitude, iso_country = iso_country)
print("All countries entered successfully")
def import1():
"""import.py correctly imports Harry Potter"""
empty_db()
check50.run("python3 import.py students.csv").exit(timeout=100)
db = SQL("sqlite:///students.db")
rows = db.execute("SELECT first, middle, last, house, birth FROM students WHERE first = 'Harry'")
expected = [{"first": "Harry", "middle": "James", "last": "Potter", "house": "Gryffindor", "birth": 1980}]
if rows != expected:
raise check50.Mismatch(str(expected), str(rows))
def main():
# avoid invalid input
if len(sys.argv) != 2:
print("Invalid input")
return exit(1)
filename = sys.argv[1]
# set up database connection
db = SQL("sqlite:///students.db")
with open(filename, "r") as data:
reader = csv.DictReader(data)
count = 0
for row in reader:
name = row["name"].split(" ")
count += 1
if len(name) == 2:
id = count
first = name[0]
middle = None
last = name[1]
house = row["house"]
birth = row["birth"]
elif len(name) == 3:
id = count
first = name[0]
middle = name[1]
last = name[2]
house = row["house"]
birth = row["birth"]
else:
print("Insertion failed")
return exit(2)
# execute SQL queries
db.execute(
"INSERT INTO students(id, first, middle, last, house, birth) VALUES(?, ?, ?, ?, ?, ?)",
id, first, middle, last, house, birth)
def main():
# Check for (im)proper amount of command-line arguments
if (len(sys.argv) != 2):
print("ERROR")
return
# People dictionary
people = {}
with open(sys.argv[1], 'r') as file:
# DictReader
reader = csv.DictReader(file)
# Loop through each row
for row in reader:
values = []
# Add to values array
values.append(row['house'])
values.append(row['birth'])
people[row['name']] = values
# Database
db = SQL("sqlite:///students.db")
# Loop through people dictionary
for key in people:
# Split the name
nameArr = key.split()
# If the person has a middle name or not
# Add to the SQL table
if (len(nameArr) == 2):
db.execute(
"INSERT INTO students(first, middle, last, house, birth) VALUES (?, ?, ?, ?, ?)",
nameArr[0], None, nameArr[1],
people.get(key)[0],
people.get(key)[1])
else:
db.execute(
"INSERT INTO students(first, middle, last, house, birth) VALUES (?, ?, ?, ?, ?)",
nameArr[0], nameArr[1], nameArr[2],
people.get(key)[0],
people.get(key)[1])
def register():
if request.method == "GET":
return render_template("register.html")
else:
name = request.form.get("name")
if not name:
return render_template("apology.html",
message="You must provide a name.")
email = request.form.get("email")
if not email:
return render_template(
"apology.html",
message="You must provide a valid email address.")
db = SQL("sqlite:///lecture.db")
db.execute(
"INSERT INTO registrants (name, email) VALUES(:name, :email)",
name=name,
email=email)
return redirect("/")
def register():
session.clear()
db = SQL("sqlite:///finance.db")
# Passes in the image for background only on register and login
home_index = "home_index"
if request.method == "GET":
return render_template("register.html", home_index=home_index)
else:
username = request.form.get("username")
if not username:
return apology("You must provide a username")
password = request.form.get("password")
if not password:
return apology("You must provide a password")
confirmation = request.form.get("confirmation")
if not confirmation:
return apology("You must provide a password")
if confirmation != password:
return apology("Passwords do not match")
rows = db.execute(
"INSERT INTO users (username, hash) VALUES (:username, :hash)",
username=username,
hash=generate_password_hash(password))
# Query database for username
rows = db.execute("SELECT * FROM users WHERE username = :username",
username=username)
# Make sure username exists and password is correct
if len(rows) != 1 or not check_password_hash(rows[0]["hash"],
password):
return apology("invalid username and/or password")
# Remember which user has logged in
session["user_id"] = rows[0]["id"]
# Add flash message
flash('Registered!')
# Redirect to home page
return redirect('/')
def get_stocks(
user_id): # returns list with all stocks owned (just the tickers)
db = SQL("sqlite:///finance.db")
stock_list = []
transactions = db.execute(
"SELECT * from transactions WHERE user_id = :user", user=user_id)
for transaction in transactions:
if transaction["ticker"] not in stock_list:
stock_list.append(transaction["ticker"])
return stock_list
def run_query(filename):
try:
with open(filename) as f:
query = f.read().strip()
query = sqlparse.format(query, strip_comments=True).strip()
db = SQL("sqlite:///movies.db")
result = db.execute(query)
return result
except Exception as e:
raise check50.Failure(f"Error when executing query: {str(e)}")
def main():
db = SQL("sqlite:///students.db")
selection = \
db.execute(f'SELECT first, middle, last, birth FROM students WHERE house="{sys.argv[1]}" ORDER BY last, first')
for student in selection:
print(f"{student['first']}", end=" ")
if student['middle']:
print(f"{student['middle']}", end=" ")
print(f"{student['last']}, born {student['birth']}")
def get_symbols():
db = SQL("sqlite:///tranfac.db")
symbol_data = db.execute(''' SELECT Symbol FROM HtfUniprot''')
symbol_list = []
for x in symbol_data:
symbol_list.append(x['Symbol'])
return symbol_list
def dashboard():
db = SQL("sqlite:///database.db")
row = db.execute("SELECT * FROM health_infos WHERE user_id= :sessionId",
sessionId=session["user_id"])
now = datetime.datetime.now()
day = now.strftime("%A")
month = now.strftime("%B")
dayNum = now.strftime("%d")
year = now.strftime("%Y")
food_rows = db.execute(
"SELECT * FROM food_entries WHERE user_id= :sessionId ",
sessionId=session["user_id"])
rem_cals = int(row[0]["total_calories"])
rem_prot = int(row[0]["total_proteins"])
rem_carbs = int(row[0]["total_carbs"])
rem_fats = int(row[0]["total_fats"])
for r in food_rows:
rem_cals -= int(r["calories"])
rem_prot -= int(r["proteins"])
rem_carbs -= int(r["carbs"])
rem_fats -= int(r["fats"])
row_len = len(food_rows)
return render_template("dashboard.html",
calories=int(row[0]["total_calories"]),
proteins=int(row[0]["total_proteins"]),
carbs=int(row[0]["total_carbs"]),
fats=int(row[0]["total_fats"]),
c_weight=row[0]["current_weight"],
g_weight=row[0]["goal_weight"],
day=day,
month=month,
dayNum=dayNum,
year=year,
food_rows=food_rows,
row_len=row_len,
rem_cals=rem_cals,
rem_prot=rem_prot,
rem_carbs=rem_carbs,
rem_fats=rem_fats)
def main():
# Open database
open(f"database.db", "w").close()
db = SQL("sqlite:///database.db")
# Check for arguments (parameter_import.py, *.json, **.csv)
# .py is the converter, *.json is the renamed .parameter file, **.csv is the output file
if len(argv) != 3:
print("Usage: python parameter_import.py *.json **.csv")
exit()
# Create tables
db.execute("""CREATE TABLE parameter (Title TEXT, VSM TEXT, Main_Inlet TEXT, Section_1 TEXT,
Section_2 TEXT, Section_3 TEXT, Section_4 TEXT, Section_5 TEXT,
Section_6 TEXT, Section_7 TEXT, Section_8 TEXT, Section_9 TEXT,
Section_10 TEXT, Section_11 TEXT, Section_12 TEXT, Section_13 TEXT)
""")
# Flatten the .json file and export it as a .csv
json_convert(argv[1])
# Open CSV file by command line argument and read it
with open(argv[2]) as database:
# Read it into memory as a list
database_reader = reader(database)
for row in database_reader:
# Insert the data into the csv database
# Have to look inside the csv to see the format to choose the right column locator for row[i]
db.execute("""INSERT INTO parameter (Title, VSM, Main_Inlet, Section_1,
Section_2, Section_3, Section_4, Section_5,
Section_6, Section_7, Section_8, Section_9,
Section_10, Section_11, Section_12, Section_13)
VALUES(?,?,?,?,
?,?,?,?,
?,?,?,?,
?,?,?,?)""",
row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7],
row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15]
)
def main():
# check to ensure that the correct command has been issued (2 args and file is csv)
if len(argv) < 2:
sys.exit("Usage: import.py <file>")
elif len(argv) > 2:
sys.exit("Usage: import.py <file>")
CSVFILE = argv[1]
if not (CSVFILE.endswith(".csv")):
sys.exit("file must be .csv")
# ignore this. I was trying to use some fancy functions that would work exactly the same way
# the database to write to later
# database = "students.db"
# dat = sqlite3.connect(database)
# cursor for the database so I can navigate it if needs be
# cursor = dat.cursor()
database = SQL("sqlite:///students.db")
# open the CSV file with all info on students, and copy all info into a list called 'students'
with open(CSVFILE) as CSVfile:
students = reader(CSVfile)
for row in students:
if row[0] == 'name':
continue # skip the first line because we don't want the titles
# divide up the name into first, middle and last, using a space as the divider
name = row[0].split()
# if the student has 3 names, then put in NULL for the middle name
if len(name) < 3:
database.execute("INSERT INTO students(first, last, house, birth) VALUES(?, ?, ?, ?)",
name[0], name[1], row[1], row[2])
else:
database.execute("INSERT INTO students(first, middle, last, house, birth) VALUES(?, ?, ?, ?, ?)",
name[0], name[1], name[2], row[1], row[2])
def edit():
db = SQL("sqlite:///passwordmanager.db")
data = request.get_json(force="True")
id_entry = data['id']
name = data['name']
link = data['link']
username = data['username']
password = data['hash']
db.execute(
"UPDATE data SET name = :name, link = :link, username = :username, hash = :hash WHERE id = :id",
name=name,
link=link,
username=username,
hash=password,
id=id_entry)
return "ok", 200
def index():
"""Show portfolio of stocks"""
db = SQL("sqlite:///finance.db")
# Get user transactions
stocks = db.execute(
"SELECT symbol, SUM(shares) as total_shares FROM transactions WHERE user_id=? GROUP BY symbol Having total_shares>0",
(session["user_id"]))
print(stocks)
# Get Current Price
quotes = {}
for stock in stocks:
quotes[stock["symbol"]] = lookup(stock["symbol"])["price"]
# Get user cash
cash = db.execute("SELECT * FROM users WHERE id=?",
session["user_id"])[0]["cash"]
# Go to Portfolio
return render_template("index.html",
stocks=stocks,
quotes=quotes,
cash=cash)
def firstentry():
if request.method == "POST":
db = SQL("sqlite:///pomodoro.db")
db.execute(
"INSERT INTO daily_history (table_id, date, display_date, tomato_count, task, notes) VALUES(?, ?, ?, ?, ?, ?)",
session["table_id"],
request.form.get("date"),
request.form.get("date-displayed"),
request.form.get("tomatoes"),
request.form.get("task"),
request.form.get("notes"),
)
global new_table
new_table = True
return redirect("/home")
else:
return render_template("firstentry.html")
def main():
if len(argv) != 2:
print("Error")
exit(1)
db = SQL("sqlite:///students.db")
with open(argv[1], "r") as characters:
reader = csv.DictReader(characters)
for row in reader:
names = []
for part in row["name"].split(" "):
names.append(part)
names.append(row["house"])
names.append(row["birth"])
row['name'].split(" ")
if len(names) == 5:
db.execute("INSERT INTO students (first, middle, last, house, birth) VALUES(?, ?, ?, ?, ?)", names[0:5])
if len(names) == 4:
db.execute("INSERT INTO students (first, last, house, birth) VALUES(?, ?, ?, ?)", names[0:4])
def delete():
"""Delete todo item"""
# Connect to database
db = SQL("sqlite:///todo3.db")
# User reached route via POST (as by submitting a form via POST)
if request.method == "POST":
# Copy the selected task, due date, and user id from the tasks database and insert them into the completed database
db.execute(
"INSERT INTO completed (task, date_due, id) VALUES((SELECT task FROM tasks WHERE todo=?), (SELECT date FROM tasks WHERE todo=?), (SELECT id FROM tasks WHERE todo=?))",
(request.form["task_to_delete"]), (request.form["task_to_delete"]),
(request.form["task_to_delete"], ))
# Delete the task entirely from the tasks database
db.execute("DELETE FROM tasks WHERE todo=?",
(request.form["task_to_delete"], ))
return redirect("/")
def index():
db = SQL("sqlite:///users.db")
rows = db.execute("""
SELECT username FROM users WHERE id = :ide""",
ide=session["user_id"])
username = rows[0]["username"]
return render_template("index.html", username=username)
def decorated_function(*args, **kwargs):
key = request.cookies.get('key')
if key is None:
return redirect("/login")
foodie = SQL(os.environ['DATABASE_URL'])
exists = foodie.execute("SELECT COUNT(*) AS c FROM users WHERE key=:key",
key=key)[0]["c"]
foodie.conn.close()
if not exists:
return redirect("/login")
return f(*args, **kwargs)
def main():
if len(argv) != 2:
print("Usage: python import.py characters.csv")
return
db = SQL("sqlite:///students.db")
with open(argv[1], "r") as characters:
reader = csv.reader(characters)
header = next(reader)
for person in reader:
person_name = person[0].split(" ")
if len(person_name) == 3:
db.execute(
f"INSERT INTO students (first, middle, last, house, birth) VALUES ('{person_name[0]}', '{person_name[1]}', '{person_name[2]}', '{person[1]}', '{person[2]}')"
)
else:
db.execute(
f"INSERT INTO students (first, middle, last, house, birth) VALUES ('{person_name[0]}', NULL, '{person_name[1]}', '{person[1]}', '{person[2]}')"
)
def main():
if len(argv) < 2:
exit(1)
# create new database
open("students.db", "w").close()
db = SQL("sqlite:///students.db")
#create students table
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 students:
reader = csv.DictReader(students)
for row in reader:
name = row["name"].split(" ")
first_name = name[0]
last_name = name[-1]
if len(name) > 2:
middle_name = name[1]
else:
middle_name = None
house = row["house"]
birth = row["birth"]
print(f"{first_name} {middle_name} {last_name} {house} {birth}")
db.execute("INSERT INTO students (first, middle, last, house, birth) VALUES(?, ?, ?, ? ,?)",
first_name, middle_name, last_name, house, birth)
def cart():
if session.get("logged_in") == True:
logged_in = True
db = SQL("sqlite:///user.db")
if not request.form.get("mbp13"):
mbp13 = 0
else:
mbp13 = int(request.form.get("mbp13")[1:])
if not request.form.get("mba13"):
mba13 = 0
else:
mba13 = int(request.form.get("mba13")[1:])
if not request.form.get("mbp16"):
mbp16 = 0
else:
mbp16 = int(request.form.get("mbp16")[1:])
if not request.form.get("macmini"):
macmini = 0
else:
macmini = int(request.form.get("macmini")[1:])
if not request.form.get("imac21"):
imac21 = 0
else:
imac21 = int(request.form.get("imac21")[1:])
if not request.form.get("ipad7"):
ipad7 = 0
else:
ipad7 = int(request.form.get("ipad7")[1:])
if not request.form.get("prodxdr"):
prodxdr = 0
else:
prodxdr = int(request.form.get("prodxdr")[1:])
cart = mbp13 + mba13 + mbp16 + macmini + imac21 + ipad7 + prodxdr
db.execute(
"UPDATE users SET Cart = ? WHERE Email = ? AND Password = ?", cart,
email, password)
return redirect("/profile")
else:
session["logged_in"] = False
logged_in = False
redirect("/")
def main():
args = sys.argv
if len(args) != 2:
print("Wrong args number. \nExample: python import.py [file.csv]")
exit(1)
db = SQL(db_addres)
with open(args[1], "r") as input_data:
reader = csv.DictReader(input_data)
for row in reader:
name_parts = row[name_key].split()
first = name_parts[0]
if len(name_parts) > 2:
mid = name_parts[1]
last = name_parts[2]
else:
mid = 'NULL'
last = name_parts[1]
house = row[house_key]
birth = int(row[birth_key])
db.execute(ISERT_SQL_STATEMENT, first, mid, last, house, birth)
def add_question():
db = SQL("sqlite:///quiz.db")
if request.method == 'GET':
rows = []
for i in db.execute(
'SELECT papername FROM papers WHERE user_id = :user',
user=session['user_id']):
rows.append(i['papername'])
return render_template('addquestion.html', papers=rows)
else:
id = db.execute('SELECT id FROM papers WHERE papername = :papername',
papername=request.form.get('paper'))[0]['id']
db.execute(
'INSERT INTO questions(question, answer, wa1, wa2, wa3, paper_id) VALUES (:question, :answer, :wa1, :wa2, :wa3, :paper_id)',
question=request.form.get('question'),
answer=request.form.get('answer'),
wa1=request.form.get('wa1'),
wa2=request.form.get('wa2'),
wa3=request.form.get('wa3'),
paper_id=id)
return redirect(url_for('index'))
def csv_import(csv_file):
# We're considering the csv file exists and it's not empty.
file = open(csv_file, "r")
db = SQL("sqlite:///students.db")
# Getting the entries in a dictionary
reader = csv.DictReader(file)
for rows in reader:
name = rows['name'].split()
# If the student doesn't have a middle name, use None as value for that column
if len(name) == 3:
db.execute(
"INSERT INTO students (first,middle,last,house,birth) VALUES (?,?,?,?,?)",
name[0], name[1], name[2], rows['house'], rows['birth'])
else:
db.execute(
"INSERT INTO students (first,middle,last,house,birth) VALUES (?,?,?,?,?)",
name[0], None, name[1], rows['house'], rows['birth'])
file.close()
def check_user():
db = SQL("sqlite:///passwordmanager.db")
data = request.get_json(force="True")
user_list = db.execute("SELECT username FROM users")
users = []
for row in user_list:
users.append(row['username'])
if data in users:
return "ok", 208
else:
return "ok", 200
def completed():
"""Display completed todo items"""
# Connect to database
db = SQL("sqlite:///todo3.db")
# Select all completed user specific tasks and sort them in the table by most recently completed
completed_tasks = db.execute(
"SELECT * FROM completed WHERE id=:id ORDER BY date_complete ASC",
id=session["user_id"])
return render_template("completed.html", completes=completed_tasks)
def addToDatabase(reader):
# Gets into the SQL database
db = SQL("sqlite:///students.db")
db.execute("DELETE FROM students")
for row in reader:
nameNumb = countNames(row[0])
# If there are 3 names it checks row for a first middle and last name
if (nameNumb == 3):
first, middle, last = row[0].split(' ')
# Adds in the seperated names, house, and date into the database
db.execute(
"INSERT INTO students (first, middle, last, house, birth) VALUES(?, ?, ?, ?, ?)",
first, middle, last, row[1], row[2])
# If there are 2 names it only checks for a first and last name then sets middle to none
else:
first, last = row[0].split(' ')
# Adds in the first and last names, house, and date into the database
db.execute(
"INSERT INTO students (first, last, house, birth) VALUES(?, ?, ?, ?)",
first, last, row[1], row[2])
def main():
"""
Imports a csv with file into the sqlite3 database.
The name of each character is split into first middle and last.
Middle name might be null.
"""
# Validate the number of arguments.
if (len(argv) != 2):
print("Wrong number of arguments")
exit(1)
# Stores the command line argument.
characters_csv_filename = argv[1]
# Loads the csv file into the memory.
fields, rows = read_csv(characters_csv_filename, True)
# Create a connection to sqlite3 database.
db = SQL("sqlite:///students.db")
for row in rows:
# Reads the data from the csv row.
full_name, house, birth = row
# Parses the name to identify if there is a middle name.
full_name_array = full_name.split()
if len(full_name_array) == 2:
first, last = full_name_array
# Stores the data into the sqlite3 database using the existing schema.
db.execute(
"INSERT INTO students (first, last, house, birth) VALUES (?, ?, ?, ?)",
first, last, house, birth)
else:
first, middle, last = full_name_array
# Stores the data into the sqlite3 database using the existing schema.
db.execute(
"INSERT INTO students (first, middle, last, house, birth) VALUES (?, ?, ?, ?, ?)",
first, middle, last, house, birth)
