Title: Supporting native backup facility of SQLite
Type: enhancement Stage: resolved
Components: Extension Modules Versions: Python 3.8, Python 3.7
Status: closed Resolution: fixed
Dependencies: Superseder:
Assigned To: Nosy List: anish.shah, berker.peksag, ced, ebarry, ghaering, lelit, palaviv, r.david.murray, serhiy.storchaka
Priority: normal Keywords: patch

Created on 2016-07-28 17:48 by lelit, last changed 2018-03-18 20:01 by berker.peksag. This issue is now closed.

File name Uploaded Description Edit
issue27645.patch lelit, 2016-07-28 18:37 Preliminar patch review
issue27645-doc.patch lelit, 2016-07-28 20:18 Documentation review
Pull Requests
URL Status Linked Edit
PR 377 closed python-dev, 2017-03-01 09:26
PR 4238 merged lelit, 2017-11-02 17:57
PR 6064 merged miss-islington, 2018-03-10 22:09
PR 6067 merged berker.peksag, 2018-03-11 00:19
PR 6068 merged miss-islington, 2018-03-11 00:39
PR 6131 merged palaviv, 2018-03-17 11:24
PR 6138 merged miss-islington, 2018-03-18 00:49
Messages (42)
msg271574 - (view) Author: Lele Gaifax (lelit) * Date: 2016-07-28 17:48
It would be nice if the sqlite3 stdlib module could expose the SQLite Online Backup API.

I'm willing to implement it, as encouraged by Paul Moore.

See also:
msg271575 - (view) Author: Lele Gaifax (lelit) * Date: 2016-07-28 17:56
Here is a preliminary implementation:
msg271576 - (view) Author: Emanuel Barry (ebarry) * Date: 2016-07-28 18:12
That's really nice, thank you for doing this! To get your code reviewed, though, you should upload a patch here. With git, you can do 'git diff > my_patch_file_name.patch' in your local repo, then you can upload the resulting file here. If you haven't done so yet, you'll need to sign a contributor agreement as well: :)

(Also, you might want to remove the 'index ...' lines in the patch file that git generates for each modified file; since there are three files, there should be three lines to remove. I'm not sure if you really have to, but I always do it just to be sure :)
msg271578 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2016-07-28 18:44
Thanks for the patch! I haven't had a chance to review the patch yet, but we also need documentation updates to Doc/library/sqlite3.rst.
msg271580 - (view) Author: Lele Gaifax (lelit) * Date: 2016-07-28 20:18
For the documentation see or the attached patch.
msg271582 - (view) Author: Lele Gaifax (lelit) * Date: 2016-07-28 20:24
WRT to the agreement form, I guess I'll have to compile it even if I already contributed to Python decades ago (ObjC, readline, NeXT support...), right?

Will try to do whatever is needed in the next days...
msg271584 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2016-07-28 20:26
If you have a copy of your original agreement you could fax it to the PSF, but it is probably easier to just to sign the electronic one.
msg271676 - (view) Author: Lele Gaifax (lelit) * Date: 2016-07-30 10:14
Ok, the agreement is fullfilled.
msg272848 - (view) Author: Lele Gaifax (lelit) * Date: 2016-08-16 08:57
I guess the chance of getting this merged before the 3.6 betas is very low, but if there is *anything* I could do to raise it, please tell :-)
msg288632 - (view) Author: Lele Gaifax (lelit) * Date: 2017-02-27 10:40
Now that we are is officially on GH, would you welcome a PR rebasing this patch on top of the master branch?
msg288723 - (view) Author: Aviv Palivoda (palaviv) * Date: 2017-02-28 19:57
I actually looked at the patch and have a few comments:
1. You need to put Py_BEGIN_ALLOW_THREADS and Py_END_ALLOW_THREADS before the sqlite3 calls (especially the sleep).
2. I think that the `pysqlite_connection_backup` function will look a lot better if you will have a cleanup/error label.

I am not a core developer but I think you should open the PR as it will be easier for the CR.
msg288750 - (view) Author: Lele Gaifax (lelit) * Date: 2017-03-01 09:35
Thank you Aviv, I applied your suggestions and opened a PR.
msg295455 - (view) Author: Lele Gaifax (lelit) * Date: 2017-06-08 16:36
Is there any chance this could be accepted for Python 3.7?
msg295472 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2017-06-09 00:48
There's a good chance, yes.  You'll have to keep periodically pinging the issue (say once a month :), and if you can specifically talk someone into doing a review your chances go up :)   For it to go in we need a review from a core-dev, but one or more reviews from non-core-devs will help it move along as well (less for the core-dev to do when they find the time to do the review).
msg298868 - (view) Author: Lele Gaifax (lelit) * Date: 2017-07-22 19:39
Monthly offer to do whatever is needed to easy the path to adoption :-)
msg303782 - (view) Author: Lele Gaifax (lelit) * Date: 2017-10-05 19:52
I rebased my v2 set of changesets into a new branch:

I really don't know if anybody is interested beyond me, I did everything has been suggested/requested, and honestly I feel a bit discouraged: in the good'n'old days even potentially disrupting and invasive patches of mine have been accepted by the one&only core developer, now for an harmless, tested and documented single new feature an year and half has passed with almost no progress....

Anyway, I guess that this is my last attempt, let's hope... Let me know if I should close the PR#377 and reopen a new one, or whatever.
msg303789 - (view) Author: R. David Murray (r.david.murray) * (Python committer) Date: 2017-10-05 23:28
If you are talking about Gerhard, if he was still around you'd probably get a similar response, but he hasn't been around much.  So this is somewhat of an orphaned module currently and it takes longer for an issue to get traction.  One of the drawbacks of the open-source-volunteer model :(
msg304789 - (view) Author: Cédric Krier (ced) * Date: 2017-10-23 09:59
I'm using sqlitebck which provides similar functionality but instead of using a file name to store the backup it uses connection instances.
I find it very useful. Here is my use case: to run tests of an application that requires a database filled, I do a 'copy' of an existing sqlite backup in a ':memory:' database.
But with the proposed API, it is not possible to use the resulted ':memory:' database because it will be delete when the connection is closed.
So I propose to add the option to pass a connection instead of a filename.
msg304894 - (view) Author: Lele Gaifax (lelit) * Date: 2017-10-24 10:29
Reasonable and quite simple to implement: done in commit
msg305444 - (view) Author: Lele Gaifax (lelit) * Date: 2017-11-02 17:59
As suggested by Brett Cannon, I closed the original PR#377 and opened a new one against a rebased version of the implementation.
msg307647 - (view) Author: Lele Gaifax (lelit) * Date: 2017-12-05 08:19
Just to keep the door open, I'm willing to to whatever is needed to see this accepted and merged.
msg307840 - (view) Author: Serhiy Storchaka (serhiy.storchaka) * (Python committer) Date: 2017-12-08 09:03
It seems to me that the code could be much simpler (and more bugfree) if support only a Connection instance as a target. It is easy to create a Connection instance in Python:

    with sqlite3.connect(filename) as dest:

But this would save around 50 lines of complex C code. And I'm not sure this code is correct.
msg308022 - (view) Author: Lele Gaifax (lelit) * Date: 2017-12-11 09:47
I need advice on Serhiy's proposal of dropping support to plain file name (see also

Wrt the other point I filed issue #32274.
msg308068 - (view) Author: Serhiy Storchaka (serhiy.storchaka) * (Python committer) Date: 2017-12-11 19:33
I prefer to keep Connection.backup() simpler. Additional features can be implemented in pure Python.
msg308073 - (view) Author: Lele Gaifax (lelit) * Date: 2017-12-11 19:56
Thank you Serhiy, ok: will simplify the method, hopefully tomorrow.
msg310669 - (view) Author: Lele Gaifax (lelit) * Date: 2018-01-25 10:08
I suspect this won't land in 3.7...

Let me know if I can do something to make that happen, or instead if I should try to rebase the change on top of current master and rectify references to the Python version.
msg313558 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-10 22:08
New changeset d7aed4102d2a40c74553240c7f03585624d27aea by Berker Peksag (Emanuele Gaifas) in branch 'master':
bpo-27645: Add support for native backup facility of SQLite (GH-4238)
msg313560 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-10 22:29
New changeset e8a5a92037b1f27809806bb87c17976d2d48d3e9 by Berker Peksag (Miss Islington (bot)) in branch '3.7':
bpo-27645: Add support for native backup facility of SQLite (GH-4238)
msg313561 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-10 22:32
Thanks, Lele.

Note that Ned gave his permission to get this into 3.7.0b3 at We can, of course, still revert it before 3.7.0 final.
msg313562 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-10 22:48

FAIL: test_bad_target_in_transaction (sqlite3.test.backup.BackupTests)
Traceback (most recent call last):
  File "/home/dje/cpython-buildarea/3.x.edelsohn-debian-z/build/Lib/sqlite3/test/", line 44, in test_bad_target_in_transaction
AssertionError: OperationalError not raised
msg313563 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-10 22:52
From test.pythoninfo:

msg313564 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-10 23:09

sqlite3.sqlite_version: 3.21.0 (passed)

Travis CI:

sqlite3.sqlite_version: 3.8.2 (passed)

sqlite3.sqlite_version: 3.8.2 (passed)
msg313569 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-11 00:35
Test also passed on my MBP with SQLite 3.22.0 and the following line

    rc = _pysqlite_seterror(bck_conn, NULL);




    "SQL logic error"

Looging at, checkReadTransaction() returns

    sqlite3ErrorWithMsg(db, SQLITE_ERROR, "destination database is in use");
    return SQLITE_ERROR;

I've opened PR 6067 to skip the test under SQLite for now.

Lele, could you take a look at this please? It's almost 4 am here I won't be able to work on this in the next 10-15 hours.
msg313570 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-11 00:38
New changeset 7280a4eef5fbe17e8ac82afd71fe6e51843240a5 by Berker Peksag in branch 'master':
bpo-27645: Skip test_bad_target_in_transaction if SQLite == (GH-6067)
msg313571 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-11 00:59
New changeset c546a62c4d7b37ead10f986554a01d6d593227a1 by Berker Peksag (Miss Islington (bot)) in branch '3.7':
bpo-27645: Skip test_bad_target_in_transaction if SQLite == (GH-6067)
msg313635 - (view) Author: Lele Gaifax (lelit) * Date: 2018-03-12 07:05
Sorry, I could not find an easy enough way to compile against SQLite, being on Debian sid myself (3.22). I hope to find some time to try harder.
msg313991 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-17 09:46
FYI, I will have some time to debug the test failure this weekend. If I (or Lele or someone else) can't find the problem by Monday, I'm going to revert the patch from 3.7 branch (and probably from master too)
msg313994 - (view) Author: Aviv Palivoda (palaviv) * Date: 2018-03-17 11:27
The problem is that change was part of sqlite version 3.8.8
I opened a PR with a fix.
msg313995 - (view) Author: Lele Gaifax (lelit) * Date: 2018-03-17 11:43
Thank you Berker&Aviv, I'm sorry I could not find the time to investigate the problem by myself.
msg314018 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-18 00:49
New changeset bbf7bb7a636b3112ef6f6b31df385606d52517ce by Berker Peksag (Aviv Palivoda) in branch 'master':
bpo-27645: Fix version number in 'database in transaction' fallback (GH-6131)
msg314026 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-18 06:24
New changeset 429ca448d2a36040f229ad9edc67e31fc6d18bf4 by Berker Peksag (Miss Islington (bot)) in branch '3.7':
bpo-27645: Fix version number in 'database in transaction' fallback (GH-6131)
msg314052 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2018-03-18 20:01
Buildbots look happy, closing this one as 'fixed'. Thanks, Aviv!
Date User Action Args
2018-03-18 20:01:03berker.peksagsetstatus: open -> closed
resolution: fixed
messages: + msg314052

stage: patch review -> resolved
2018-03-18 06:24:41berker.peksagsetmessages: + msg314026
2018-03-18 00:49:26miss-islingtonsetpull_requests: + pull_request5895
2018-03-18 00:49:03berker.peksagsetmessages: + msg314018
2018-03-17 11:43:33lelitsetmessages: + msg313995
2018-03-17 11:27:25palavivsetmessages: + msg313994
2018-03-17 11:24:33palavivsetpull_requests: + pull_request5892
2018-03-17 09:46:20berker.peksagsetmessages: + msg313991
2018-03-12 07:05:44lelitsetmessages: + msg313635
2018-03-11 00:59:13berker.peksagsetmessages: + msg313571
2018-03-11 00:39:11miss-islingtonsetpull_requests: + pull_request5829
2018-03-11 00:38:52berker.peksagsetmessages: + msg313570
2018-03-11 00:35:39berker.peksagsetstage: resolved -> patch review
2018-03-11 00:35:15berker.peksagsetresolution: fixed -> (no value)
messages: + msg313569
stage: patch review -> resolved
2018-03-11 00:19:04berker.peksagsetstage: resolved -> patch review
pull_requests: + pull_request5828
2018-03-10 23:09:29berker.peksagsetmessages: + msg313564
2018-03-10 22:52:50berker.peksagsetmessages: + msg313563
2018-03-10 22:48:07berker.peksagsetstatus: closed -> open

messages: + msg313562
2018-03-10 22:32:03berker.peksagsetstatus: open -> closed
versions: + Python 3.8
messages: + msg313561

resolution: fixed
stage: patch review -> resolved
2018-03-10 22:29:21berker.peksagsetmessages: + msg313560
2018-03-10 22:09:48miss-islingtonsetpull_requests: + pull_request5825
2018-03-10 22:08:34berker.peksagsetmessages: + msg313558
2018-01-25 10:08:58lelitsetmessages: + msg310669
2017-12-11 19:56:29lelitsetmessages: + msg308073
2017-12-11 19:33:40serhiy.storchakasetmessages: + msg308068
2017-12-11 09:47:21lelitsetmessages: + msg308022
2017-12-08 09:03:44serhiy.storchakasetnosy: + serhiy.storchaka
messages: + msg307840
2017-12-05 08:19:18lelitsetmessages: + msg307647
2017-11-02 17:59:01lelitsetmessages: + msg305444
2017-11-02 17:57:10lelitsetpull_requests: + pull_request4203
2017-10-24 10:29:12lelitsetmessages: + msg304894
2017-10-23 09:59:34cedsetmessages: + msg304789
2017-10-05 23:28:27r.david.murraysetmessages: + msg303789
2017-10-05 19:52:02lelitsetmessages: + msg303782
2017-07-22 19:39:57lelitsetmessages: + msg298868
2017-06-09 00:48:06r.david.murraysetmessages: + msg295472
versions: + Python 3.7, - Python 3.6
2017-06-08 16:36:00lelitsetmessages: + msg295455
2017-03-03 07:44:59anish.shahsetnosy: + anish.shah
2017-03-01 09:35:09lelitsetmessages: + msg288750
2017-03-01 09:26:52python-devsetpull_requests: + pull_request315
2017-02-28 19:57:30palavivsetnosy: + palaviv
messages: + msg288723
2017-02-27 10:40:38lelitsetmessages: + msg288632
2016-08-17 20:26:43cedsetnosy: + ced
2016-08-16 21:02:18ned.deilysetnosy: + ghaering
2016-08-16 08:57:47lelitsetmessages: + msg272848
2016-07-30 10:14:10lelitsetmessages: + msg271676
2016-07-28 20:26:54r.david.murraysetnosy: + r.david.murray
messages: + msg271584
2016-07-28 20:24:12lelitsetmessages: + msg271582
2016-07-28 20:18:55lelitsetfiles: + issue27645-doc.patch

messages: + msg271580
2016-07-28 18:44:10berker.peksagsetnosy: + berker.peksag

messages: + msg271578
stage: patch review
2016-07-28 18:37:30lelitsetfiles: + issue27645.patch
keywords: + patch
2016-07-28 18:12:29ebarrysetnosy: + ebarry
messages: + msg271576
2016-07-28 17:56:36lelitsetmessages: + msg271575
2016-07-28 17:48:18lelitcreate