- LPU MCA Semester 1
- CAB106
- Unit 6
CAB106 · Unit 6: Application development using OOP
Database connectivity using SQLite
13 practice questions
Key points
- sqlite3 needs no server; connect, cursor, execute, commit
- Use ? placeholders to prevent SQL injection
- fetchone returns None when rows run out
- with con commits on success and rolls back on error
SQLite is a file-based database engine that needs no server, and Python's standard sqlite3 module connects to it. The usual steps are: con = sqlite3.connect("app.db") (or ":memory:" for a temporary database), cur = con.cursor(), cur.execute(sql, params), con.commit() and con.close().
| Call | Purpose |
|---|---|
execute / executemany | Run one statement / run it for many parameter tuples |
fetchone() | Next row as a tuple, or None when finished |
fetchall() | Remaining rows as a list of tuples |
commit() / rollback() | Save / undo changes since the last commit |
row_factory = sqlite3.Row | Access columns by name and by index |
Always pass values as `?` placeholders (INSERT INTO t VALUES (?, ?)) rather than building SQL from text, which prevents SQL injection. cursor.rowcount tells how many rows an UPDATE changed. Using the connection in a with block commits on success and rolls back if an exception occurs. A query on a missing table raises sqlite3.OperationalError, and breaking a constraint raises sqlite3.IntegrityError. GROUP BY returns one row per group, so COUNT(*) counts rows in each group.
Exam trap: changes are not saved until commit(); closing the connection without committing loses them.
Try two questions
Which call makes the changes from INSERT statements permanent?
- commit()
- close()
- execute()
- fetchall()
Show the answer
A. connection.commit() writes the current transaction to the database.
Why not B: Closing a connection without committing discards pending changes.
Why not C: execute() only runs a statement inside the current transaction.
Why not D: fetchall() reads results and does not save changes.
Why should a query use ? placeholders instead of joining user text into the SQL string?
- Placeholders make every query run faster than any literal text
- Placeholders allow a SELECT statement to change stored rows
- Placeholders convert every value to text before it is stored
- The driver passes the values separately, which prevents SQL injection
Show the answer
D. Bound parameters are treated as data and never as SQL, so injected text cannot change the query.
Why not A: Speed is not the purpose of placeholders.
Why not B: A SELECT statement does not modify rows because of placeholders.
Why not C: Values keep their types when they are bound.