7.2. SQLite3 Connect

Important

• File database - persistent storage

• In-memory - very fast, but volatile

• sqlite3.connect() -> connection

• connection.close()

7.2.1. In-Memory Database

• Useful for tests, development and demonstrations

• Very fast (do not write any data to disk)

• Remember to close connection

Connection to in-memory database:

✘

>>> import sqlite3
>>>
>>>
>>> db = sqlite3.connect(':memory:')
>>> db.close()

7.2.2. File Database

• Connection will create file if not exists

• Remember to close connection

Connection to database file:

✘

>>> import sqlite3
>>>
>>>
>>> db = sqlite3.connect('/tmp/myfile.db')
>>> db.close()

7.2.3. Context Managers

• Prefer using context manager

• No need to remember about closing connection

• Prepare your data and statements before connection

• Works with either in-memory or file database

✘

>>> import sqlite3
>>>
>>>
>>> DATABASE = ':memory:'
>>>
>>> with sqlite3.connect(DATABASE) as db:
...     pass

7.2.4. Debug

• conn.set_trace_callback(print)

• Registers trace_callback to be called for each SQL statement that is actually executed by the SQLite backend.

• The only argument passed to the callback is the statement (as string) that is being executed.

✘

>>> import sqlite3
>>>
>>>
>>> DATABASE = ':memory:'
>>>
>>> with sqlite3.connect(DATABASE) as db:
...     db.set_trace_callback(print)