Embedded Relational DBs: sqlite3 Connection & Cursor
Python includes SQLite in its standard library (`sqlite3`). It requires zero server configuration, storing entire relational SQL databases in a single local file 🗄️.
import sqlite3
# Connect to database file (or ':memory:' for RAM):
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
conn.commit()
cursor.execute("SELECT id, name FROM users")
print(cursor.fetchall()) # [(1, 'Alice')] 🗄️
conn.close()
# 16. Embedded Relational DBs: sqlite3 Connection & Cursor Python includes SQLite in its standard library (`sqlite3`). It requires zero server configuration, storing entire relational SQL databases in a single local file 🗄️. ```python import sqlite3 # Connect to database file (or ':memory:' for RAM): conn = sqlite3.connect(":memory:") cursor = conn.cursor() cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)") cursor.execute("INSERT INTO users (name) VALUES ('Alice')") conn.commit() cursor.execute("SELECT id, name FROM users") print(cursor.fetchall()) # [(1, 'Alice')] 🗄️ conn.close() ``` Always remember `conn.commit()` after executing `INSERT`, `UPDATE`, or `DELETE`. Without committing, your changes vanish the moment the connection closes! 🗄️ --- ### Question Why does SQLite not require installing a separate database server daemon like PostgreSQL or MySQL? 🧠 - [ ] a. SQLite is an embedded database engine compiled directly into the application process 🗄️ - [ ] b. It runs on cloud servers automatically - [ ] c. It only stores data in browser cookies - [ ] d. It converts SQL queries into JSON text files in /tmp --- ### Challenge Connect to an in-memory SQLite database (`:memory:`). Create table `items (name TEXT)`. Insert `'Widget'`. Commit, fetch the row, and print the item name. 🎯 <details> <summary>Solution</summary> ```python import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() cur.execute("CREATE TABLE items (name TEXT)") cur.execute("INSERT INTO items VALUES ('Widget')") conn.commit() cur.execute("SELECT name FROM items") row = cur.fetchone() print(row[0]) conn.close() ``` </details>