Python sqlite3: how to check if connection is an in-memory database?

Question

From Python's sqlite3 library, how can we determine if a connection belongs to a in-memory database?

import sqlite3

conn = sqlite3.connect(':memory:')

def is_in_memory_connection(conn):
    # How to check if `conn` is an in-memory connection?

Is it possible to check the filename of an on-disk database? If so, I would presume that it would return None for an in-memory database.

@zedfoxus Yes, but I was hoping for some way to check directly from the conn instead of executing a query. If I receive no answer from that perspective in a day or two I will mark yours as correct. — Matthew Moisen
– Matthew Moisen, Commented Mar 30, 2022 at 3:25
I see. I couldn’t find anything about the connection that could tell me about whether the db is using disk or memory. There’s a .show command through CLI, but we can’t run dot commands from Python connection. — zedfoxus
– zedfoxus, Commented Mar 30, 2022 at 12:22
@zedfoxus Too bad. And apparently there is a C API to get the filename of the connection (which is NULL for in memory databases), but it is not exposed in python's sqlite3. — Matthew Moisen
– Matthew Moisen, Commented Mar 30, 2022 at 15:41
You got it. Depending on your use case, you could use Python's subprocess to run sqlite3 CLI and capture stdout and then act upon it. But, that may have its own set of complexities. — zedfoxus
– zedfoxus, Commented Mar 30, 2022 at 17:17

zedfoxus · Accepted Answer · 2022-03-29 01:34:09Z

This is the closest I could come up with:

import sqlite3
 
def is_in_memory_connection(conn):
    local_cursor = conn.cursor()
    local_cursor.execute('pragma database_list')
    rows = local_cursor.fetchall()                                                       
    print(rows[0][2])
    return rows[0][2] == ''
 
#database = 'test.sqlite'
database = ':memory:'
conn = sqlite3.connect(database)
result = is_in_memory_connection(conn)
 
print(result)

If you have an in-memory database, database_list will show equivalent of this:

sqlite> pragma database_list;
seq  name  file
---  ----  ----
0    main

If you are opening a file that's on disk, it'll show the path of the file equivalent of this:

sqlite> pragma database_list;
seq  name  file                      
---  ----  --------------------------
0    main  /home/testing/test.sqlite

Taking advantage of this, you could call pragma database_list to show the file. If the path is empty, the database is not associated with a file.

https://sqlite.org/pragma.html#pragma_database_list

Collectives™ on Stack Overflow

Python sqlite3: how to check if connection is an in-memory database?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related