[Python] Connecting to and Disconnecting from SQLite3 Databases: File vs. In-Memory

2025年12月25日

Using the Python standard library sqlite3, you can create and manage lightweight relational databases that run on local files or in memory, without needing to set up a separate server.

This article explains how to connect to a database file (for data persistence) and how to connect to a temporary in-memory database (where data is not saved).

1. Connecting to and Disconnecting from a File Database

You can connect to a database by specifying a local file (e.g., company_data.db). If the file does not exist, it will be automatically created.

After processing, you must call the .close() method to close the connection. This releases database locks and cleans up resources.

Source Code

import sqlite3
import os

# Database file name (Extensions like .db or .sqlite3 are common)
db_path = "company_data.db"

print(f"--- Connecting: {db_path} ---")

# 1. Connect to the database
# sqlite3.connect(path) creates a connection object
connection = sqlite3.connect(db_path)

try:
    # 2. Create a cursor (Object to execute SQL)
    cursor = connection.cursor()
    
    # Check connection: Try retrieving the SQLite version
    cursor.execute("SELECT sqlite_version();")
    version = cursor.fetchone()
    print(f"SQLite Version: {version[0]}")
    print("Successfully connected to the database.")

    # You would perform table creation and data manipulation here...

except sqlite3.Error as e:
    print(f"An error occurred: {e}")

finally:
    # 3. Close the connection
    # Always close it at the end, regardless of whether an error occurred
    if connection:
        connection.close()
        print("Connection closed.")

# Verify if the file was created after execution
if os.path.exists(db_path):
    print(f"Check: '{db_path}' exists in the current directory.")

Execution Result

--- Connecting: company_data.db ---
SQLite Version: 3.42.0
Successfully connected to the database.
Connection closed.
Check: 'company_data.db' exists in the current directory.

2. Connecting to an In-Memory Database (:memory:)

To create a database that runs only in main memory (RAM) without creating a file, specify the special keyword ":memory:" as the connection path.

When the program ends or the connection is closed, all data is lost. This is ideal for running test code or processing temporary data.

Source Code

import sqlite3

print("--- Using In-Memory DB ---")

# Specify the special name ":memory:"
conn = sqlite3.connect(":memory:")

try:
    cursor = conn.cursor()
    
    # Create a table and insert data
    cursor.execute("CREATE TABLE temp_logs (id INTEGER, message TEXT)")
    cursor.execute("INSERT INTO temp_logs VALUES (1, 'Temporary Data')")
    
    # Check data
    cursor.execute("SELECT * FROM temp_logs")
    print(f"Retrieved Data: {cursor.fetchone()}")
    
    print("This data is not saved to a file.")

finally:
    conn.close()
    print("In-memory connection closed (Data destroyed).")

Explanation

Connection Options (connect)

database: The file path.
timeout: The number of seconds to wait if the database is locked (default is 5.0 seconds).
isolation_level: Transaction control mode. If set to None, it enters autocommit mode.

Behavior of the `with` Statement

When using sqlite3.connect() in a with statement, it behaves differently from the file operation open(). It does not automatically close the connection.

Instead, the with statement for connection objects handles transaction management: