Definition
SQL is the standard language for working with relational databases, which store data in tables. SQLite is a small database that lives in one file and comes built into Python through the sqlite3 module.
In simple words
A database is a set of very strict spreadsheets that many programs can use safely. SQL is how you ask it questions: show me all students with marks above 80, sorted highest first.
Why it matters
Most of the world's business data sits in SQL databases, so it's required for nearly every data job, and often the first round in interviews.
How it works
| SQL | Does |
|---|---|
| CREATE TABLE | Makes a table |
| INSERT | Adds rows |
| SELECT ... WHERE | Reads matching rows |
| ORDER BY | Sorts |
| GROUP BY | Groups for totals |
| JOIN | Combines tables |
| UPDATE, DELETE | Changes or removes rows |
In code
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE students (name TEXT, city TEXT, mark INT)")
db.executemany("INSERT INTO students VALUES (?, ?, ?)", [
("Riya", "Pune", 91), ("Aman", "Delhi", 72), ("Sara", "Pune", 85),
])
db.execute("CREATE TABLE cities (city TEXT, state TEXT)")
db.execute("INSERT INTO cities VALUES ('Pune', 'Maharashtra'), ('Delhi', 'Delhi')")
print(db.execute("SELECT name FROM students WHERE mark > ? ORDER BY mark DESC", (80,)).fetchall())
print(db.execute("SELECT city, COUNT(*), AVG(mark) FROM students GROUP BY city").fetchall())
print(db.execute("""SELECT s.name, c.state FROM students s
JOIN cities c ON s.city = c.city""").fetchall())Output
[('Riya',), ('Sara',)]
[('Delhi', 1, 72.0), ('Pune', 2, 88.0)]
[('Riya', 'Maharashtra'), ('Aman', 'Delhi'), ('Sara', 'Maharashtra')]Common mistakes
- Using f-strings to build SQL with user data, which opens the door to SQL injection.
- Forgetting commit() on a real file database, so changes aren't saved.
Check yourself
Summary
- SQL reads and changes tables.
- SQLite comes built into Python.
- Always use placeholders for user input.
Finished reading?
Sources: Python docs: sqlite3