This tutorial is out of date! Try my longer, updated Flask-SQLite webapp tutorial here
Okay, you have some data. Where’s it live?
Out of all of those, the database is the most powerful, and can handle the most (and most complex) data. One of the downsides of a databases, though, is they aren’t so user-friendly. They aren’t tragic, but they can just be a little opaque sometimes.
Usually when you’d like to get some information out of a database, you query it. Most of the time you’ll be using a database that supports a language called SQL - Structured Query Language.
You might have heard of SQL before, the “SQL” moniker is attached to a lot of the database systems that use it - MySQL, PostgreSQL (or Postgres), SQLite, MS SQL, etc. Each one of those can do sliiiiightly different things with SQL when you get into the more advanced stuff, but the basics are usually the same.
SQL isn’t too bad, as languages go. Let’s say we had a list of schools in New York City - the SQL query to get all the schools in Brooklyn might look like this:
SELECT * FROM schools WHERE borough = 'BK'Which would give me back
| id | school_name | borough |
|---|---|---|
| 7 | Abraham Lincoln | BK |
| 12 | Ronald Edmonds Learning Center II | BK |
| 13 | Roberto Clemente | BK |
| 41 | Francis White School | BK |
And if Abraham Lincoln moved to Queens? Well, first there might be multiple schools named “Abraham Lincoln”, so I’m going to update it by keeping an eye on the id, which should be unique.
UPDATE schools SET borough = 'QN' WHERE id = 7SQL is nice and easy enough, but I don’t want to have to sprinkle it throughout my Python code (or Ruby, or C++, etc). If only there were some way to connect that seemed kind of Python-y!
ORMs to the rescue! ORM stands for Object-Relational Management, and it’s a way of treating your database just like any other object that you’d be programming with.
For example, ActiveRecord is one I really enjoy for Ruby. If you wanted to get all of the schools from Brooklyn?
School.where(borough: 'BK')Or grab Abraham Lincoln (Public School #7), and update it to be in Queens?
lincoln = School.find(7)
lincoln.borough = 'QN'
lincoln.saveORMs have problems, sure, but they’re especially useful when building web applications when you have to do a lot of querying and searching.
I’m going to show you two ORMs for Python, SQLAlchemy and peewee.
SQLAlchemy is Python’s #1 ORM. It’s definitey powerful, but the learning curve is a little steeper than the alternatives.
Peewee is a “small, expressive orm,” which is code words for “it doesn’t support everything under the sun.” Sometimes you need to be greedy, but sometimes simple works best.