sqlite3 connect parameter "check_same_thread" not documented

[DaveSawyer]

BPO	27113
Nosy	@orsenthil, @takluyver, @berkerpeksag
Files	doc_parm.patch issue27113-rev1.patch issue27113-rev2.patch issue27113-rev2.patch issue27113-rev3.patch

^{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 2016-06-03.07:05:05.850>
created_at = <Date 2016-05-24.21:36:29.765>
labels = ['type-feature', 'docs']
title = 'sqlite3 connect parameter "check_same_thread" not documented'
updated_at = <Date 2016-06-03.19:24:50.485>
user = 'https://bugs.python.org/DaveSawyer'

bugs.python.org fields:

activity = <Date 2016-06-03.19:24:50.485>
actor = 'takluyver'
assignee = 'docs@python'
closed = True
closed_date = <Date 2016-06-03.07:05:05.850>
closer = 'orsenthil'
components = ['Documentation']
creation = <Date 2016-05-24.21:36:29.765>
creator = 'Dave Sawyer'
dependencies = []
files = ['42978', '43052', '43053', '43063', '43116']
hgrepos = []
issue_num = 27113
keywords = ['patch']
message_count = 17.0
messages = ['266281', '266352', '266664', '266672', '266679', '266731', '266952', '266954', '266955', '266960', '266961', '267010', '267065', '267066', '267067', '267143', '267146']
nosy_count = 6.0
nosy_names = ['orsenthil', 'docs@python', 'python-dev', 'takluyver', 'berker.peksag', 'Dave Sawyer']
pr_nums = []
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue27113'
versions = ['Python 3.5', 'Python 3.6']

[DaveSawyer]

The sqlite3.connect method has 6 parameters. 5 of them are documented. See below and search for "check_same_thread". Patch adds documentation for this parameter.

sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements])
Opens a connection to the SQLite database file database. You can use ":memory:" to open a database connection to a database that resides in RAM instead of on disk.

When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).

For the isolation_level parameter, please see the Connection.isolation_level property of Connection objects.

SQLite natively supports only the types TEXT, INTEGER, REAL, BLOB and NULL. If you want to use other types you must add support for them yourself. The detect_types parameter and the using custom converters registered with the module-level register_converter() function allow you to easily do that.

detect_types defaults to 0 (i. e. off, no type detection), you can set it to any combination of PARSE_DECLTYPES and PARSE_COLNAMES to turn type detection on.

By default, the sqlite3 module uses its Connection class for the connect call. You can, however, subclass the Connection class and make connect() use your class instead by providing your class for the factory parameter.

Consult the section SQLite and Python types of this manual for details.

The sqlite3 module internally uses a statement cache to avoid SQL parsing overhead. If you want to explicitly set the number of statements that are cached for the connection, you can set the cached_statements parameter. The currently implemented default is to cache 100 statements.

[berkerpeksag]

This is a duplicate of bpo-16509, but I'm going to keep this open since there is a patch attached here.

[orsenthil]

Thank you for the patch, Dave.

Following the language style of rest of the docs, check_same_thread, should be explained in a passive voice.

Also, mentioning the version requirement at this place, does not seem suitable to me. When we provide this feature, it assumed that the underlying sqlite linked has the support for it.

Also, (3.3.1 alpha) of sqlite was released in 2006-01-16
Database connections can now be used by multiple threads, not just the thread in which they were created.

Here is a modified patch which captures the main idea from your patch. Please review this and share your suggestions.

[orsenthil]

We should be able to simplify this further based on the comments in the review for the first patch.

Please find my patch bpo-27113-rev2.patch attached.

[DaveSawyer]

The revised patch says "connections" plural for true and "connection" singular for false. How about "the connection" since the method returns a connection. I'm wondering though about the lack of explanation or WHY for this parameter. As written it seems like the default value was poorly chosen and everyone should just set it to true. Likewise, if everyone should set it to true, why is it even provided as an option?

[orsenthil]

Thanks for the review. Made it both singular in the updated patch.

• As for why the default value was chosen to be to True, I think, the reason is pysqlite, from sqlite3 module was derived, did not support this option when it was introduced. I still do not find that in pysqlite external library.

That being said, if we want to change the default to looking into multiple threads, we could attempt that change and introduce it. This will be a separate feature request ticket.

[DaveSawyer]

The user probably has a recent enough version. This is guaranteed on Windows since Python bundles 3.6 or later. On mac or Linux it will use the version installed on the machine. I'll make a separate patch to check the version in sqlite3.py so it will give an error if attempted on too early a version. That's better than asking the user to verify ahead of time. The serialized mode is default on both Mac and Windows so we can probably skip validating that. I did like mentioning the user needs to serialize the writes. They could use one thread for writing only or use locking. So, I just said to serialize.

[DaveSawyer]

Changed doc to note that not only must it be used on 1 thread if true, but that thread must be the thread that created it.

[orsenthil]

Dave, your patch looks good to me.

Did you see the comment from Trevor on your first patch?

> According to the sqlite docs (http://www.sqlite.org/threadsafe.html), the
default mode is 'serialized', which does the necessary locking for threads to
share a connection. Unless Python is overriding this to use one of the less
protected modes, it shouldn't be necessary to use extra locks around it.

This had prompted me to remove extra line on asking users to take care of synchronization.

[takluyver]

Having watched the video of Dave's PyCon talk (thanks Dave), I think he's talking about using locking to control transactions, which presumably the sqlite bindings don't handle by themselves. But I don't *think* you need manual locking just to maintain database integrity. If that's the case, it's probably helpful to clarify it.

I'd probably suggest that if you want to write from multiple threads and control transactions (i.e. more than just individual INSERTs fired from different threads), it's probably easier to use separate connections and let sqlite handle the synchronisation than to synchronise them yourself.

[takluyver]

That comment contained far too much 'probably'. Time for me to sleep...