sqlite3 in-memory databases - Python

The sqlite3 module in the standard library provides a Python interface for working with and managing SQLite databases.

SQLite is a lightweight database engine which is widely used in various applications.

SQLite allows an entire database to be stored and managed in RAM. Such databases are known as in-memory databases.

The fact that in-memory databases are stored in RAM means that they are temporary and they won't persist between different executions. This makes them good for testing or for exploring SQLite features but obviously not for permanent storage. All in-memory databases will be removed from memory when the program exits.

To create and open a connection with an in-memory database, use the ':memory:' in place of database filename when creating the database connection.

Create an in-memory database

#import the sqlite3 module
import sqlite3

#create a tble
with sqlite3.connect(':memory:') as conn:
  #create a table
  conn.execute('''
    CREATE TABLE point(
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      x INTEGER,
      y INTEGER
      )
    ''')

  #populate table
  conn.executescript('''
    INSERT INTO point (x, y) VALUES (3, 4);
    INSERT INTO point (x, y) VALUES (2, 3);
    INSERT INTO point (x, y) VALUES (-2, 5);
    ''')

  #retrieve data
  cursor = conn.cursor()
  cursor.execute('SELECT x, y FROM point;')

  print(cursor.fetchall())
  cursor.close()

In the above example we:

1. Imported the sqlite3 module
2. Started an in-memory database connection using sqlite3.connect(':memory:')
3. Created a database table called point, with three columns id, x, and y. 
4. Inserted three rows into the point table.
5. Created a cursor object.
6. Executed a SELECT sql statements and retrieved all returned rows using the cursor.fetchall() method.

Using the connection object as a context manager ( in a with statement) ensures that the connection is automatically closed when the block is exited or if an error is encountered.

Export database contents

To export the contents of the database, use the conn.iterdump() method which returns the statements that you should execute to replicate the database. This can be used to create a persisting replica of the database in permanent memory .

#import the sqlite3 module
import sqlite3

#create a tble
with sqlite3.connect(':memory:') as conn:
  #create a table
  conn.execute('''
    CREATE TABLE point(
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      x INTEGER,
      y INTEGER
      )
    ''')

  #populate table
  conn.executescript('''
    INSERT INTO point (x, y) VALUES (3, 4);
    INSERT INTO point (x, y) VALUES (2, 3);
    INSERT INTO point (x, y) VALUES (-2, 5);
    ''')

  for text in conn.iterdump():
    print(text)

Conclusion

The sqlite3.connect(':memory:') statement creates an in-memory SQLite database. Note that multiple calls will lead to creation of multiple distinct in-memory databases.

Us the conn.iterdump() method to export the contents of the database.