Enhance sqlite3 to avoid implicit creation?

[nedbat]

BPO	46402
Nosy	@ericvsmith, @nedbat, @zware, @miss-islington, @erlend-aasland
PRs	bpo-46402: Promote SQLite URI tricks in sqlite3 docs #30660 [3.10] bpo-46402: Promote SQLite URI tricks in sqlite3 docs (GH-30660) #30661 [3.9] bpo-46402: Promote SQLite URI tricks in sqlite3 docs (GH-30660) #30662 [3.10] bpo-46402: Promote SQLite URI tricks in sqlite3 docs (GH-30660) #30671 [3.9] bpo-46402: Promote SQLite URI tricks in sqlite3 docs (GH-30660) #30672

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2022-01-18.22:15:01.743>
created_at = <Date 2022-01-16.17:42:31.926>
labels = ['type-feature', 'library', '3.11']
title = 'Enhance sqlite3 to avoid implicit creation?'
updated_at = <Date 2022-01-18.22:15:01.742>
user = 'https://github.com/nedbat'

bugs.python.org fields:

activity = <Date 2022-01-18.22:15:01.742>
actor = 'erlendaasland'
assignee = 'none'
closed = True
closed_date = <Date 2022-01-18.22:15:01.743>
closer = 'erlendaasland'
components = ['Library (Lib)']
creation = <Date 2022-01-16.17:42:31.926>
creator = 'nedbat'
dependencies = []
files = []
hgrepos = []
issue_num = 46402
keywords = ['patch']
message_count = 15.0
messages = ['410704', '410724', '410779', '410780', '410781', '410783', '410790', '410793', '410851', '410857', '410892', '410895', '410902', '410903', '410906']
nosy_count = 5.0
nosy_names = ['eric.smith', 'nedbat', 'zach.ware', 'miss-islington', 'erlendaasland']
pr_nums = ['30660', '30661', '30662', '30671', '30672']
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue46402'
versions = ['Python 3.11']

[nedbat]

The sqlite3 library implicitly creates the database if the requested file doesn't exist. I would like to be able to avoid that implicit creation. (Actually, it would be enough to know whether I had created the database, but the underlying SQLite library doesn't seem to indicate that.)

The C code currently hard-codes the SQLite flag to create the database if it doesn't exist:

    rc = sqlite3_open_v2(database, &db,
                         SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
                         (uri ? SQLITE_OPEN_URI : 0), NULL);

Could we make this an option, so the Python code could avoid implicit creation?

[ericvsmith]

I agree that would be a useful option.

[erlend-aasland]

FYI, you can already do this using the URI option:

    cx = sqlite3.connect("file:test.db?mode=rw", uri=True)

[erlend-aasland]

See also bpo-24887.

[erlend-aasland]

IMO, the URI "API" is not very pythonic; I have to look up the format every time I'm using it.

OTOH, introducing flags, or keywords, for every option will add a lot of code.

[erlend-aasland]

OTOH, implementing an API similar to apsw (adding a flags keyword) would make it easier for users to switch between that and the stdlib sqlite3.

[nedbat]

@erlend: thanks for the URI tip, I missed that as a possibility in the SQLite docs.

[erlend-aasland]

I guess we could do more to promote that trick in the docs. It’s quite useful.

[erlend-aasland]

I created #74845 in order to try to enhance the docs. Ned and Eric, would you mind taking a look at the PR?