Basics

SQL statements

Operations on an SQL database are done using SQL statements. The three basic statements you'll need to know are CREATE TABLE, INSERT, and SELECT.

SQL quirks

There are a few quirks of SQL that you should be aware of:

• SQL keywords in almost all database engines are case-insensitive, but it is common practice to write them in uppercase.
• SQL statements end with semicolons. When you interact with a database through a library like sqlite3, they often aren't necessary—the library will handle it for you.
• Strings in SQL appear inside single-quotes (e.g. 'a string'). Double-quotes mean something else.
• Equality checks are done with =, not ==.

CREATE TABLE

CREATE TABLE statements, unsurprisingly, create new tables in the current database. For example, the following command creates a table called "accounts". Each account has a corresponding name, email, balance, and an age. Each column in this table has an associated type: name and email are strings (TEXT), balance is a float (REAL), and age is an integer (INTEGER).

CREATE TABLE accounts (
    name TEXT,
    email TEXT,
    balance REAL,
    age INTEGER
);

Sometimes, you'll want to create a table only if it doesn't already exists. You can use CREATE TABLE IF NOT EXISTS for that:

CREATE TABLE IF NOT EXISTS accounts (
    name TEXT,
    email TEXT,
    balance REAL,
    age INTEGER
);

Either way we do it, we've now create an empty accounts table in the database.

accounts

INSERT

You can use an INSERT statement to actually add rows to an existing table.

INSERT INTO accounts VALUES ('John Doe', 'john@doe.com', 250.45, 28);

We can also INSERT multiple rows at once:

INSERT INTO accounts VALUES
    ('Rose Parker', 'RoseCParker@teleworm.us', 12000, 31),
    ('Nathan Dandrea', 'NathanADandrea@dayrep.com', 50.9, 39),
    ('Flora Lewis', 'FloraKLewis@rhyta.com', 10.0, 25);

After all these insertions, our accounts table should look like this:

accounts

SELECT

SELECT statements let you query existing tables to get the data they contain. For example, you can get all the data in the accounts table by running:

SELECT * FROM accounts;

Query Result

In the query above, * means all columns. We can also refine our query by only selecting certain columns:

SELECT name, age FROM accounts;

Query Result

We can use a WHERE clause to filter out some rows based on a condition:

SELECT * FROM accounts WHERE balance > 100;

Query Result

Using SQLite with Python

To see how to run SQL commands like these from Python, please follow the official tutorial for Python's sqlite3 module. Make sure to notice the usage of con.commit() after insertions. Database engines typically work with transactions; if you update a table (e.g. with INSERT), your changes will not actually make it to the database until you finalize the transaction (with con.commit()).