Models are representation of a databases table. The first thing we need to do is define our models. Peewee is your standard Python library, you can install it with pip pip install peewee Connecting with peewee schema command when we have to tell peewee what’s inside of the database, but for now we can rest easy that we have a working SQLite database. Looking good! We’ll want to remember the. schema sat_scores to see what’s hiding in the tables sqlite>. tables to see what our tables are sqlite>. Run sqlite3 schools.db to open up the SQLite console with schools.db.įirst we’ll use. No error messages? Great! Let’s take a peek at our file. NOTE: If_ you_received an error about the data not being in UTF-8, you might want to give Google Spreadsheets a try instead of Excel. csvsql -db sqlite:///schools.db -insert -table schools DOE_High_School_Directory_2016.csvĬsvsql -db sqlite:///schools.db -insert -table sat_scores SAT_College_Board_2010_School_Level_Results.csv Now we’ll use the wonderful CSVKit to convert it into a SQLite database with a schools table and a sat_scores table. So now we have: dbn, school_name, number_of_test_takers, critical_reading_mean, mathematics_mean, writing_mean I also renamed the columns to be all lowercase, with underscores.

For the SAT scores, for everything that has an s in it I just blanked out the cell (filter proved very useful).

That way we’ll be able to do math with those numbers later.

For the directory, I edited grade_span_min, grade_span_max and expgrade_span_min: I changed K to 0 and PK to -1.

Some columns are almost numbers-only, but not quite, I downloaded them both as CSV files, then cleaned them up a little. I took two datasets from the NYC data portal, one is a directory of high schools and one is their performance data. Instead of running SQL queries in your code, it allows you to connect to a database and run queries on it in a way that seems a little more Python-y. Peewee is a “simple, expressive” ORM for Python. This tutorial is out of date! Try my longer, updated Flask-SQLite webapp tutorial here Introduction to peewee Home > Tutorials > Web Applications for You and Me > Intro to peewee

Flask sqlite data tables csv