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)