Quoting Petr Viktorin in PR 27940, https://github.com/python/cpython/pull/27940#discussion_r703424148:

- db is not set to NULL if init fails.
- This segfaults for me:

  import sqlite3

  conn = sqlite3.connect(':memory:')
  conn.execute('CREATE TABLE foo (bar)')

  try:
      conn.__init__('/bad-file/')
  except sqlite3.OperationalError:
      pass

  conn.execute('INSERT INTO foo (bar) VALUES (1), (2), (3), (4)')


Other issues:
- reinitialisation is not handled gracefully
- __init__ is messy; members are initialised here and there


Suggested to reorder connection __init__ in logical groups to more easily handle errors:
  1. handle reinit
  2. open and configure database
  3. create statement LRU cache and weak ref cursor list
  4. initialise members in the order they're defined in connection.h
  5. set isolation level, since it's a weird case anyway

This is a minefield. If anyone has a use case for it, I'd *love* to hear it, but to me it seems that proper reinit support will be a lot of work (now and in future maintenance) for no gain. You can always create a new connection object.

Consider instead deprecating reinitialization, documenting it as unpredictable & removing it in Python 3.13.


Here's another thing to test:
```python
import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.execute('CREATE TABLE foo (bar)')

try:
    conn.__init__('/bad-file/')
except sqlite3.OperationalError:
    pass

cursor.execute('INSERT INTO foo (bar) VALUES (1), (2), (3), (4)')
```

And here's the kind of behavior that should be specified (and tested), and might even change to make more sense:
```python
import sqlite3

conn = sqlite3.connect(':memory:')
conn.text_factory=bytes
conn.row_factory = sqlite3.Row
cursor = conn.execute('CREATE TABLE foo (bar)')
cursor.execute('INSERT INTO foo (bar) VALUES ("1"), ("2"), ("3"), ("4")')
cursor.execute('SELECT bar FROM foo')

for row in cursor:
    print(row, list(row))
    break

conn.__init__(':memory:')
conn.execute('CREATE TABLE foo (bar)')
conn.execute('INSERT INTO foo (bar) VALUES ("a"), ("b"), ("c"), ("d")')

# Currently this uses the old database, old row_factory, but new text_factory"
for row in cursor:
    print(row, list(row))
```

We should also do a review of all places `connection` is used from Cursor, Statement, etc. to ensure everything continue to work with a different `db`. This can be done (and maybe you already did), but I'm also worried how well future maintainers can keep reinitialization in mind.

And all this will also need to be done for Cursor.

I modified your second example slightly:

```
import sqlite3

conn = sqlite3.connect(":memory:")
conn.text_factory=bytes
conn.row_factory = sqlite3.Row
cursor = conn.execute("CREATE TABLE foo (bar)")
numbers = range(4)
cursor.executemany("INSERT INTO foo (bar) VALUES (?)", ((str(v),) for v in numbers))
cursor.execute("SELECT bar FROM foo")

print("first fetch")
for row in cursor.fetchmany(2):
    print(type(row[0]))

conn.__init__(":memory:")
conn.execute("CREATE TABLE foo (bar)")
letters = "a", "b", "c", "d"
conn.executemany("INSERT INTO foo (bar) VALUES (?)", ((v,) for v in letters))

# Currently this uses the old database, old row_factory, but new text_factory"
print("second fetch")
for row in cursor.fetchall():
    print(type(row[0]))
```

Here's the output:
first fetch
<class 'bytes'>
<class 'bytes'>
second fetch
<class 'str'>
<class 'str'>

Note: I ran that with PR 28227.


OTOH: I do agree that there's a lot of pitfalls here, especially in the future. In the long run, it is probably best to deprecate reinit, and disable it in Python 3.13.

Modifying the loops to also print the values:

first fetch
b'0' <class 'bytes'>
b'1' <class 'bytes'>
second fetch
2 <class 'str'>
3 <class 'str'>

FYI, I've expanded the reinit tests and added a deprecation warning to the PR.

I think a deprecation should be discussed a bit more widely than on bpo, so I opened a thread here: https://discuss.python.org/t/deprecating-sqlite-object-reinitialization/10503

Great, thanks.

As for the effort to fix this:

If we deprecate this, there should be no new users of it in 3.11+.

If we deprecate and also fix this, and we happen to introduce bugs or behavior changes, then people that use it now will need to:
1) adapt to the new behavior
2) find a different way to do things (before 3.13)

If we deprecate but keep the buggy behavior it as it is, (1) is not needed. Less work for both us and the users.

> If we deprecate but keep the buggy behavior it as it is, (1) is not needed. Less work for both us and the users.

Indeed.

There's still a ref leak I'd like to take care of, though: if the first audit fails, database_obj leaks.

I'll save the cleanup till Python 3.13 dev is started. I've opened a PR for fixing the ref leak (should be backported), and a draft PR for deprecating Connection and Cursor reinitialisation.

Updated title to include sqlite3.Cursor as well, since cursors and connections are very much entwined.

New changeset c78d5ca3806d02e26f9f3fa92ff567f0805eac4c by Erlend Egeberg Aasland in branch 'main':
bpo-45126: Fix ref. leak in `sqlite3.Connection.__init__` (GH-28231)
https://github.com/python/cpython/commit/c78d5ca3806d02e26f9f3fa92ff567f0805eac4c

New changeset aa6dd54d43dffbdf883c083e361f6ccf8642d66e by Erlend Egeberg Aasland in branch '3.10':
[3.10] bpo-45126: Fix ref. leak in `sqlite3.Connection.__init__` (GH-28231). (GH-28298)
https://github.com/python/cpython/commit/aa6dd54d43dffbdf883c083e361f6ccf8642d66e

New changeset 5d28bb699a305135a220a97ac52e90d9344a3004 by Miss Islington (bot) in branch '3.9':
[3.10] bpo-45126: Fix ref. leak in `sqlite3.Connection.__init__` (GH-28231). (GH-28298)
https://github.com/python/cpython/commit/5d28bb699a305135a220a97ac52e90d9344a3004

New changeset 9d6215a54c177a5e359c37ecd1c50b594b194f41 by Erlend Egeberg Aasland in branch 'main':
bpo-45126: Harden `sqlite3` connection initialisation (GH-28227)
https://github.com/python/cpython/commit/9d6215a54c177a5e359c37ecd1c50b594b194f41

The remaining PR (GH 28234) deprecates reinitialisation. I'm not sure it is a good idea to pursue this, so I'm leaning towards closing this as fixed, and also closing GH 28234.

I think it's a good idea, but without the "will be disallowed in Python 3.13" part -- we should tell people that it's discouraged, but there's not much point in removing it.

But there's no consensus whether that's a good way to handle things, in general. So I'll leave it up to you.