MEPX
Get started
Chapters
Python114 chapters

Basics

  1. Introduction
  2. Get Started
  3. Syntax
  4. Comments
  5. Variables
  6. Variable Names
  7. Multiple Assignment
  8. Global Variables
  9. Data Types
  10. Numbers
  11. Casting
  12. Booleans
  13. Operators
  14. User Input

Strings

  1. Strings
  2. Slicing
  3. Modify Strings
  4. Concatenation
  5. f-Strings
  6. String Formatting
  7. Escape Characters
  8. String Methods
  9. Text and Unicode

Collections

  1. Lists
  2. List Access
  3. Change Items
  4. Add Items
  5. Remove Items
  6. Loop Lists
  7. List Comprehension
  8. Sort Lists
  9. Copy Lists
  10. Join Lists
  11. List Methods
  12. Tuples
  13. Sets
  14. Dictionaries
  15. Nested Dictionaries
  16. Dictionary Methods
  17. Set Methods
  18. Dict and Set Comprehensions
  19. The collections Module

Control flow

  1. If...Else
  2. Match
  3. While Loops
  4. For Loops
  5. Break and Continue

Functions

  1. Functions
  2. Arguments
  3. args and kwargs
  4. Default Values
  5. Return Values
  6. Scope
  7. Recursion
  8. Lambda
  9. Decorators
  10. Generators
  11. Closures

Classes and objects

  1. Classes and Objects
  2. The __init__ Method
  3. The __str__ Method
  4. Methods
  5. self
  6. Inheritance
  7. Polymorphism
  8. Iterators
  9. Dataclasses
  10. Enums
  11. Magic Methods

Modules and the standard library

  1. Modules
  2. PIP
  3. Dates
  4. Math
  5. JSON
  6. RegEx
  7. itertools
  8. os and sys
  9. Random Numbers

Files and errors

  1. File Handling
  2. Read Files
  3. Write Files
  4. Delete Files
  5. Try...Except
  6. Raising Exceptions
  7. CSV Files
  8. Context Managers
  9. Paths with pathlib

Beyond the basics

  1. Virtual Environments
  2. Type Hints
  3. Async Basics
  4. NumPy Intro
  5. Pandas Intro
  6. Matplotlib Intro
  7. Working with APIs
  8. Testing
  9. Logging
  10. Command-line Arguments
  11. Debugging
  12. SQLite
  13. Packaging

Machine learning

  1. What Machine Learning Is
  2. Your First Model
  3. Features and Labels
  4. Train and Test Split
  5. Classification
  6. Regression
  7. Evaluating a Model
  8. Overfitting and Underfitting
  9. Preprocessing
  10. Pipelines
  11. Clustering
  12. Neural Networks
  13. Saving and Using a Model
  14. Where It Goes Wrong

Beyond the basicsChapter 99 of 114

SQLite

A real database in a single file, with no server, built into Python.

Back: DebuggingNext: Packaging

No server, no install

sqlite3 is in the standard library, and a whole database is one file. That makes it the right default for a desktop app, a cache, or anything where JSON has stopped being enough.

Python
import sqlite3

connection = sqlite3.connect("people.db")
cursor = connection.cursor()

cursor.execute("CREATE TABLE IF NOT EXISTS people (name TEXT, born INTEGER)")
cursor.execute("INSERT INTO people VALUES ('Ada', 1815)")
connection.commit()

cursor.execute("SELECT * FROM people")
print(cursor.fetchall())

connection.close()

Output

[('Ada', 1815)]

Pass ":memory:" instead of a filename for a database that lives only as long as the program — ideal for tests.

Never build SQL with string formatting

Python
import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("CREATE TABLE people (name TEXT, born INTEGER)")

cursor.execute("INSERT INTO people VALUES (?, ?)", ("Ada", 1815))
cursor.executemany("INSERT INTO people VALUES (?, ?)", [("Grace", 1906), ("Kat", 1918)])
connection.commit()

cursor.execute("SELECT name FROM people WHERE born > ?", (1900,))
print([row[0] for row in cursor.fetchall()])

Output

['Grace', 'Kat']

The ? placeholders are the whole point. The value is sent separately from the statement, so it can never be read as SQL.

Note the trailing comma in (1900,). Parameters go in a tuple, and a one-item tuple needs it.

Fetching

Python
import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE people (name TEXT, born INTEGER)")
connection.executemany("INSERT INTO people VALUES (?, ?)", [("Ada", 1815), ("Grace", 1906)])

cursor = connection.execute("SELECT * FROM people ORDER BY born")
print(cursor.fetchone())
print(cursor.fetchall())

for row in connection.execute("SELECT name FROM people"):
    print(row[0])

Output

('Ada', 1815)
[('Grace', 1906)]
Ada
Grace

fetchone took the first row, so fetchall returned only the rest. A cursor is an iterator, and looping over it is the memory-safe way to read a large result.

Rows by name

Tuples get unreadable fast. sqlite3.Row gives you access by column name:

Python
import sqlite3

connection = sqlite3.connect(":memory:")
connection.row_factory = sqlite3.Row
connection.execute("CREATE TABLE people (name TEXT, born INTEGER)")
connection.execute("INSERT INTO people VALUES (?, ?)", ("Ada", 1815))

row = connection.execute("SELECT * FROM people").fetchone()
print(row["name"], row["born"])
print(dict(row))

Output

Ada 1815
{'name': 'Ada', 'born': 1815}

Now adding a column cannot break code that reads by position.

Transactions

Changes are not saved until you commit, which is what makes "all or nothing" possible:

Python
import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE people (name TEXT)")

try:
    with connection:
        connection.execute("INSERT INTO people VALUES ('Ada')")
        connection.execute("INSERT INTO people VALUES ('Grace')")
        raise ValueError("something went wrong")
except ValueError:
    print("rolled back")

print(connection.execute("SELECT COUNT(*) FROM people").fetchone()[0])

Output

rolled back
0

Using the connection as a context manager commits on success and rolls back on an exception. Neither insert survived, which is exactly right — a half-finished change is usually worse than none.

Schema

Python
import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("""
    CREATE TABLE people (
        id      INTEGER PRIMARY KEY,
        name    TEXT NOT NULL UNIQUE,
        born    INTEGER
    )
""")

connection.execute("INSERT INTO people (name, born) VALUES ('Ada', 1815)")

try:
    connection.execute("INSERT INTO people (name, born) VALUES ('Ada', 1900)")
except sqlite3.IntegrityError:
    print("UNIQUE stopped the duplicate")

print(connection.execute("SELECT id, name FROM people").fetchall())

Output

UNIQUE stopped the duplicate
[(1, 'Ada')]

INTEGER PRIMARY KEY auto-numbers for you. Constraints in the schema are worth far more than checks in your code, because the database enforces them however the data arrives.

Closing

Python
import sqlite3

with sqlite3.connect(":memory:") as connection:
    connection.execute("CREATE TABLE t (n INTEGER)")
    connection.execute("INSERT INTO t VALUES (1)")
    print(connection.execute("SELECT COUNT(*) FROM t").fetchone()[0])

connection.close()
print("closed")

Output

1
closed

Test yourself

2 questions

Why use ? placeholders instead of an f-string in a query?

Show the answer

The value is sent separately, so it can never be read as SQL — A name of ' OR '1'='1 returns every row. There is no situation where the f-string is worth it.

What does using the connection as a context manager do?

Show the answer

Commits on success and rolls back on an exception — It commits the transaction but does not close the connection. Call close() yourself.

Exercises for this chapter
Next chapter

Packaging

Turn a folder of scripts into something installable.

Back: DebuggingNext: Packaging
Back: DebuggingNext: Packaging