SQLite Row Factories (Dict-Like Column Access)
By default, SQLite returns tuples (`row[0]`). Configuring `conn.row_factory = sqlite3.Row` lets you access columns by name (`row['username']`) with zero performance penalty 📋.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row # Enable dict-like column access! 📋
cur = conn.cursor()
cur.execute("CREATE TABLE users (id INT, email TEXT)")
cur.execute("INSERT INTO users VALUES (1, 'dev@xicae.com')")
row = cur.execute("SELECT id, email FROM users WHERE id = 1").fetchone()
print(f"User email: {row['email']} 📋") # Access by column name!
conn.close()
# 18. SQLite Row Factories (Dict-Like Column Access) By default, SQLite returns tuples (`row[0]`). Configuring `conn.row_factory = sqlite3.Row` lets you access columns by name (`row['username']`) with zero performance penalty 📋. ```python import sqlite3 conn = sqlite3.connect(":memory:") conn.row_factory = sqlite3.Row # Enable dict-like column access! 📋 cur = conn.cursor() cur.execute("CREATE TABLE users (id INT, email TEXT)") cur.execute("INSERT INTO users VALUES (1, 'dev@xicae.com')") row = cur.execute("SELECT id, email FROM users WHERE id = 1").fetchone() print(f"User email: {row['email']} 📋") # Access by column name! conn.close() ``` Using `sqlite3.Row` prevents breaking bugs when SQL queries reorder column selections (`SELECT email, id` vs `SELECT id, email`)! 📋 --- ### Question What advantage does `conn.row_factory = sqlite3.Row` provide over standard tuples? 🧠- [ ] a. Columns can be accessed both by index and case-insensitive column name like a dictionary 📋 - [ ] b. Automatically encrypts sensitive table data in RAM - [ ] c. Disables the need to commit database transactions - [ ] d. Concurrently streams query results over WebSockets --- ### Challenge Connect to `:memory:`. Set `conn.row_factory = sqlite3.Row`. Create table `tasks (title TEXT, points INT)`. Insert `('Refactor', 50)`. Fetch row and print `f"{row['title']}: {row['points']}"`. 🎯 <details> <summary>Solution</summary> ```python import sqlite3 conn = sqlite3.connect(":memory:") conn.row_factory = sqlite3.Row cur = conn.cursor() cur.execute("CREATE TABLE tasks (title TEXT, points INT)") cur.execute("INSERT INTO tasks VALUES ('Refactor', 50)") row = cur.execute("SELECT title, points FROM tasks").fetchone() print(f"{row['title']}: {row['points']}") conn.close() ``` </details>