# Sqlite3: consider deprecating combining named placeholders with sequence of params

**URL:** <https://discuss.python.org/t/sqlite3-consider-deprecating-combining-named-placeholders-with-sequence-of-params/22450>\
**Category:** Core Development\
**Tags:** stdlib\
**Created:** [January 4, 2023, 11:40pm UTC](https://discuss.python.org/t/sqlite3-consider-deprecating-combining-named-placeholders-with-sequence-of-params/22450 "2023-01-04T23:40:32Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\
**Post date:** [January 4, 2023, 11:40pm UTC](https://discuss.python.org/t/sqlite3-consider-deprecating-combining-named-placeholders-with-sequence-of-params/22450/1 "2023-01-04T23:40:32Z")

</div>

Currently, `sqlite3` disallows using nameless (aka qmark) placeholders with params supplied as `dict`s:

```python
>>> import sqlite3
>>> cx = sqlite3.connect(":memory:")
>>> cx.execute("select ?", {"a": 42})
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Binding 1 has no name, but you supplied a dictionary (which has only names).

```

OTOH, named placeholders combined with sequences are, unfortunately, silently allowed:

```python
>>> cx.execute("select :name", [42]).fetchall()
[(42,)]

```

This is possible, because when you supply a sequence, `sqlite3` simply maps the items in the sequence onto the placeholders by index.

Now, let’s introduce a common source of confusion: numeric placeholders. PEP-249 uses the following format for numeric placeholders: `:<number>`. SQLite, OTOH, uses the following format: `?<number>`. What happens if you pass _PEP-249_ numeric placeholders to the SQLite library? The placeholders are interpreted by SQLite as _named_ placeholders:

```python
>>> cx.execute("select :1", {"1": 42}).fetchall()
[(42,)]

```

Unfortunately, if one misses the fact that these are named, and not numeric, placeholders, bugs may sneak in:

```python
>>> cx.execute("select :1", ("first",)).fetchall()
[('first',)]
>>> cx.execute("select :1, :2", ("first", "second")).fetchall()
[('first', 'second')]
>>> cx.execute("select :2, :1", ("first", "second")).fetchall() # Unexpected result follows
[('first', 'second')]

```

IMO, it would better if `sqlite3` disallowed combining named placeholders with sequences of params, similar to how it disallows nameless placeholders and dicts. Of course, we’d have to introduce a deprecation warning, wait a couple of releases, and only then start raising an exception.

Now, this _might_ break some code out there (Hyrum’s Law), but there is value in preventing new bugs to sneak into people’s code bases.

Thoughts?

---

<div class="post-metadata">

**Author:** ![gpshead](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/gpshead/32/54_2.png) [@gpshead](https://discuss.python.org/u/gpshead)\
**Post date:** [January 5, 2023, 9:04pm UTC](https://discuss.python.org/t/sqlite3-consider-deprecating-combining-named-placeholders-with-sequence-of-params/22450/2 "2023-01-05T21:04:35Z")

</div>

Overall I agree, alleviating this API confusion and conflation of concepts in both directions would be ideal and could be accomplished through a deprecation period.

**Q:** How do other database frontends in Python fare?

[PEP 249 – Python Database API Specification v2.0 | peps.python.org](https://peps.python.org/pep-0249/) doesn’t really specify what constitutes a `:name` which I guess means that implementation(s) probably treat name as any sequence of non-space non-colon characters? yuck.

Alternatively to accomplish a lesser disambiguation if your original larger idea doesn’t pan out: Issue a DeprecationWarning when a numeric name is detected for a couple of releases before making a numeric :name an error. I’m assuming it will be rare for people to intentionally use numbers as names and that forcing the issue may expose problematic code.

---

<div class="post-metadata">

**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\
**Post date:** [January 6, 2023, 10:35am UTC](https://discuss.python.org/t/sqlite3-consider-deprecating-combining-named-placeholders-with-sequence-of-params/22450/3 "2023-01-06T10:35:48Z")

</div>

> [@gpshead](#):
>
> **Q:** How do other database frontends in Python fare?

I haven’t investigated that yet, but I’ll have a look at the most popular ones. I’ll also hear with the SQLAlchemy folks; I assume they have a pretty good picture of this.

> [@gpshead](#):
>
> [PEP 249 – Python Database API Specification v2.0 | peps.python.org](https://peps.python.org/pep-0249/) doesn’t really specify what constitutes a `:name` which I guess means that implementation(s) probably treat name as any sequence of non-space non-colon characters? yuck.

For `sqlite3`, we don’t parse the placeholder; the entire query string is just passed onto SQLite, so a name is what the underlying SQLite library says is a name.

> [@gpshead](#):
>
> Alternatively to accomplish a lesser disambiguation if your original larger idea doesn’t pan out: Issue a DeprecationWarning when a numeric name is detected for a couple of releases before making a numeric :name an error.

I don’t plan to detect PEP 249 style _numeric_ placeholders specifically; I want to (in the end) raise an error if any _named_ placeholder is used together with parameters supplied as a sequence.

> [@gpshead](#):
>
> I’m assuming it will be rare for people to intentionally use numbers as names and that forcing the issue may expose problematic code.

+1

---

<div class="post-metadata">

**Author:** ![steve.dower](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/steve.dower/32/56_2.png) [@steve.dower](https://discuss.python.org/u/steve.dower)\
**Post date:** [January 6, 2023, 12:03pm UTC](https://discuss.python.org/t/sqlite3-consider-deprecating-combining-named-placeholders-with-sequence-of-params/22450/4 "2023-01-06T12:03:00Z")

</div>

Does this require us to start parsing the SQL statement in order to provide the validation? If we aren’t already (reliably) parsing the statement, I’d rather not start doing it.

We’d have to be very cautious to not reject valid statements (including ones that haven’t been invented yet… not that SQL is known for moving quickly), or to false-positive on comments or string literals. Such a parser could become complex very quickly.

Perhaps we should just document the inconsistency between SQLite and PEP 249?

> [@erlendaasland](#):
>
> Currently, `sqlite3` disallows using nameless (aka qmark) placeholders with params supplied as `dict`s:
> 
> ```auto
> >>> import sqlite3
> >>> cx = sqlite3.connect(":memory:")
> >>> cx.execute("select ?", {a: 42})
> Traceback (most recent call last):
> File "<stdin>", line 1, in <module>
> NameError: name 'a' is not defined
> 
> ```

This is a `NameError` because you didn’t quote `'a'` in the dict definition. The result actually reads:

```auto
>>> import sqlite3
>>> cx = sqlite3.connect(":memory:")
>>> cx.execute("select ?", {"a": 42})
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Binding 1 has no name, but you supplied a dictionary (which has only names).

```

This error is clearly in response to `sqlite3_bind_parameter_name` failing after `sqlite3_bind_parameter_count` succeeded, which means SQLite has determined the parameters. This is fine.

Unfortunately, I don’t see any `bind_*` functions from SQLite that may help determine if parameters were named before you set them by index. The best option seems to be to request the index of parameter `'1'` and if it’s found, warn if passed a sequence rather than a dict.

I don’t see any reason to ever promote this to an error. If linters reckon they can detect it reliably enough, I’d leave it to them, but I suspect most aren’t going to flow around enough context to handle anything but trivial cases.

---

<div class="post-metadata">

**Author:** ![malemburg](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/malemburg/32/50_2.png) [@malemburg](https://discuss.python.org/u/malemburg)\
**Post date:** [January 6, 2023, 12:38pm UTC](https://discuss.python.org/t/sqlite3-consider-deprecating-combining-named-placeholders-with-sequence-of-params/22450/5 "2023-01-06T12:38:00Z")

</div>

There are plans to simplify the binding parameter styles for DB-API 3 and it’s very likely that we’ll only continue to support _named_ (dictionary parameters) and _qmark_ (sequence parameters) going forward, perhaps even requiring support for both (but this still has to be discussed).

A consequence of this would be to have database modules raise an error in case they get passed in a sequence for _named_ style or a dictionary for _qmark_ style.

Note that parsing for these two styles is not hard. You don’t need a full SQL parser, just have to pay attention to standard SQL string literal quoting.

---

<div class="post-metadata">

**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\
**Post date:** [January 6, 2023, 1:26pm UTC](https://discuss.python.org/t/sqlite3-consider-deprecating-combining-named-placeholders-with-sequence-of-params/22450/6 "2023-01-06T13:26:51Z")

</div>

> [@steve.dower](#):
>
> Does this require us to start parsing the SQL statement in order to provide the validation? If we aren’t already (reliably) parsing the statement, I’d rather not start doing it.

No, it does not; we can use the SQLite C API to easily detect if a given placeholder is nameless or named. I’ll go to great lengths to avoid having to parse SQL in the `sqlite3` module 🙂

> [@steve.dower](#):
>
> This is a `NameError` because you didn’t quote `'a'` in the dict definition. The result actually reads:

Thanks! 🙂 🤦‍♂️ I’ve updated the OP.

> [@steve.dower](#):
>
> The best option seems to be to request the index of parameter `'1'` and if it’s found, warn if passed a sequence rather than a dict.

Yes, we can use `sqlite3_bind_parameter_name` for this. It would work roughly like this: if a sequence of params was passed _and_ `sqlite3_bind_parameter_name` returns non-NULL for any of the params, we issue a warning.

> [@steve.dower](#):
>
> don’t see any reason to ever promote this to an error. If linters reckon they can detect it reliably enough, I’d leave it to them, but I suspect most aren’t going to flow around enough context to handle anything but trivial cases.

I would be fine with `sqlite3` simply issuing a warning.

---

<div class="post-metadata">

**Author:** ![storchaka](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/storchaka/32/217_2.png) [@storchaka](https://discuss.python.org/u/storchaka)\
**Post date:** [January 6, 2023, 7:19pm UTC](https://discuss.python.org/t/sqlite3-consider-deprecating-combining-named-placeholders-with-sequence-of-params/22450/7 "2023-01-06T19:19:33Z")

</div>

It clearly looks like a bug to me. I think that a deprecation warning is not necessary, we can just raise an exception (although it may be not worth to backport this change). If you want to be a super-nice and emit a deprecation warning, it is good too.

---

<div class="post-metadata">

**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\
**Post date:** [January 6, 2023, 7:25pm UTC](https://discuss.python.org/t/sqlite3-consider-deprecating-combining-named-placeholders-with-sequence-of-params/22450/8 "2023-01-06T19:25:00Z")

</div>

> [@storchaka](#):
>
> If you want to be a super-nice and emit a deprecation warning, it is good too.

I want to be super-nice.

---

<div class="post-metadata">

**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\
**Post date:** [February 9, 2023, 7:53am UTC](https://discuss.python.org/t/sqlite3-consider-deprecating-combining-named-placeholders-with-sequence-of-params/22450/9 "2023-02-09T07:53:38Z")

</div>

I created an [issue](https://github.com/python/cpython/issues/101693) and a [PR](https://github.com/python/cpython/pull/101698).