Tom Ordonez

Data Science, Machine Learning, Growing Teams

SQLite3 CheatSheet for Python


This is an ongoing SQLite3 cheatsheet for Python.

SQLite3 comes with Python and you can launch it with:

$ sqlite3

If you launch it without an argument, it will say Connected to a transient in-memory database.

The prompt changes to:

sqlite>

The .help is a good idea.

sqlite> .help

To exit do .exit.

sqlite> .exit

Create a SQLite database

I find it better to launch SQLite passing the name of the database as an argument.

$ sqlite3 sql1

Now it says SQLite version...Enter .help for usage hints.

Create a SQLite table

This is just your standard SQL. You can use uppercase or lowercase. But I prefer to follow the standard.

sqlite> CREATE TABLE Users (
   ...> name VARCHAR(128),
   ...> email VARCHAR(128)
   ...> );

I often forget the semicolon ;. It will give you an error if you forget.

Check that a TABLE was created:

sqlite> .tables
Users

You can also check the schema:

sqlite> .schema
CREATE TABLE Users (
name VARCHAR(128),
email VARCHAR(128)
);

Add values to the SQLite table

Add some data to the table:

sqlite> INSERT INTO Users VALUES('homer', '[email protected]');
sqlite> INSERT INTO Users VALUES('marge', '[email protected]');
sqlite> INSERT INTO Users VALUES('lisa', '[email protected]');
sqlite> INSERT INTO Users VALUES('bart', '[email protected]');
sqlite> INSERT INTO Users VALUES('maggie', '[email protected]');

Read data from SQLite table

This doesn't work:

sqlite> Users;

I wish that SQL was more user friendly with a variety of human-friendly options:

Show me users
Give me users
What's in Users

Instead do:

sqlite> SELECT * FROM Users;
homer|[email protected]
marge|[email protected]
lisa|[email protected]
bart|[email protected]
maggie|[email protected]
sqlite>

Exit:

sqlite> .exit

Open again:

$ sqlite3
Connected to a transient in-memory database.

Ooops forgot to open the right database.

sqlite> .open sql1
sqlite>
sqlite> .schema
CREATE TABLE Users(
name VARCHAR(128),
email VARCHAR(128)
);

Looks like the right place.

Export SQLite database table to CSV

I can't always remember how to do this:

sqlite> .mode list
sqlite> .separator ","
sqlite> .output test_sql1.csv
sqlite> SELECT * FROM Users;
sqlite> .exit

Although a good way to remember is to understand the workflow:

Import CSV to SQLite database table

Let's say we have a CSV called springfield.csv with some name, email values:

Open SQLite and import:

$ sqlite3 sql1
sqlite> .tables
Users

Workflow:

Export SQLite Table to CSV...another way

sqlite> .headers on
sqlite> .mode csv
sqlite> .once test2_sql1.csv
sqlite> SELECT * FROM Users;

Workflow:

  • Add the header
  • Set mode to CSV
  • Output to filename
  • SQL command

If you look at the .help you fill find what .once means:

"Output for the next SQL command only to FILENAME". Used like this:

.once FILENAME

Open the CSV without closing SQLite

If you are in Linux or maybe Mac(not sure):

sqlite> .system xdg-open test2_sql1.csv

This will open the CSV file in the default editor for CSV.

Insert rows into SQLite table

Open the database:

$ sqlite3 sql1
sqlite> .tables
Users
sqlite> .schema
CREATE TABLE Users (
name VARCHAR(128),
email VARCHAR(128)
);

I find it useful to see what the tables and schema are.

This is the same as before:

sqlite> INSERT INTO Users VALUES('bob', '[email protected]');

Although this syntax is optional:

sqlite> INSERT INTO Users(name, email) VALUES('bob', '[email protected]');

That one shows a comma-separated list of columns. Perhaps is a reminder of the data that you are inserting. I think is a good idea.

Delete data from a SQLite table

In our example database we have name and email. Let's delete the last row that has bob.

sqlite> DELETE FROM Users WHERE email='[email protected]';

Update a field from a SQLite table

Let's update moe and change his email to [email protected].

sqlite> UPDATE Users SET email='[email protected]' WHERE email='[email protected]';

Get records from a SQLite table

We already tried this too many times:

sqlite> SELECT * FROM Users;

What about this one:

sqlite> SELECT * FROM Users WHERE name='bart';
bart|[email protected]

Get records and sorting from a SQLite table

Use ORDER BY:

sqlite> SELECT * FROM Users ORDER BY name;
sqlite> SELECT * FROM Users ORDER BY email;

Create a SQLite connection in Python

import sqlite3
conn = sqlite3.connect('phantom.sqlite')
cur = conn.cursor()
...
do something here
cur.close()

SQLite in Python

Let's write a simple program called contacts.py that asks for names and emails:

import sqlite3

conn = sqlite3.connect('contacts.sqlite')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS Users')
cur.execute('CREATE TABLE Users(name TEXT, email TEXT)')
while True:
    name = input('Enter name: ')
    email = input('Enter email: ')
    cur.execute('INSERT INTO Users(name, email) VALUES(?, ?)', (name, email))
    conn.commit()

    try:
        more_values = input('Add more? (y/n): ')
        if more_values == 'y':
            continue
        elif more_values == 'n':
            break
    except (KeyboardInterrupt, SystemExit):
        raise
cur.close()

This line DROP TABLE IF EXISTS Users is used to drop table everytime we run the program.

Running the program:

$ python3 contacts.py
Enter name: homer
Enter email: [email protected]
Add more? y/n: y
Enter name: bart
Enter email: [email protected]
Add more? y/n: n
$ ls
contacts.py contacts.sqlite

Let's open the contacts.sqlite:

$ sqlite3 contacts.sqlite
sqlite> .tables
Users
sqlite> SELECT * FROM Users;
homer|[email protected]
bart|[email protected]

Let's export to CSV:

sqlite> .headers on
sqlite> .mode csv
sqlite> .once contacts.csv
sqlite> SELECT * FROM Users;
sqlite> .system xdg-open contacts.csv

Launches my text editor Sublime Text:

name,email
homer,[email protected]
bart,[email protected]

To be continued

This is a work in progress. I will add more soon.

If you feel I should add something more. Please comment.