New submission from Simon Willison <swilli...@gmail.com>:
Bit of an obscure bug this one. SQLite allows column names to contain [ and ]
characters, even though those are often used as delimiters in SQLite. Here's
how to create such a database with bash:
```
sqlite3 /tmp/demo.db <<EOF
BEGIN TRANSACTION;
CREATE TABLE "data" (
"MTU (CET)" TEXT,
"Day-ahead Price [EUR/MWh]" TEXT
);
INSERT INTO "data" VALUES('01.01.2016 00:00 - 01.01.2016 01:00','23.86');
COMMIT;
EOF
```
If you query that database from sqlite3 in Python, the `[Eur/MWh]` piece is
removed from that column name:
```
In [1]: import sqlite3
In [2]: conn = sqlite3.connect("/tmp/demo.db")
In [3]: cursor = conn.cursor()
In [4]: cursor.execute("select * from data")
Out[4]: <sqlite3.Cursor at 0x10c70a0a0>
In [5]: cursor.fetchall()
Out[5]: [('01.01.2016 00:00 - 01.01.2016 01:00', '23.86')]
In [6]: cursor.description
Out[6]:
(('MTU (CET)', None, None, None, None, None, None),
('Day-ahead Price', None, None, None, None, None, None))
In [7]: conn.row_factory = sqlite3.Row
In [8]: cursor = conn.cursor()
In [9]: cursor.execute("select * from data")
Out[9]: <sqlite3.Cursor at 0x10c7a8490>
In [10]: row = cursor.fetchall()
In [12]: row
Out[12]: <sqlite3.Row at 0x10c3fe670>
In [15]: row.keys()
Out[15]: ['MTU (CET)', 'Day-ahead Price']
```
As you can see, it is missing from both `cursor.description` and from
`row.keys()` here.
But... if you query that database using SQLite directly (with `.headers on` so
you can see the name of the columns) it works as expected:
```
$ sqlite3 /tmp/demo.db
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE IF NOT EXISTS "data" (
"MTU (CET)" TEXT,
"Day-ahead Price [EUR/MWh]" TEXT
);
sqlite> .headers on
sqlite> select * from data;
MTU (CET)|Day-ahead Price [EUR/MWh]
01.01.2016 00:00 - 01.01.2016 01:00|23.86
sqlite>
```
It looks to me like this is a bug in Python's SQLite3 module.
This was first reported here: https://github.com/simonw/sqlite-utils/issues/86
----------
components: Extension Modules
messages: 362081
nosy: simonw
priority: normal
severity: normal
status: open
title: sqlite3 bug handling column names that contain square braces
versions: Python 2.7, Python 3.5, Python 3.6, Python 3.7, Python 3.8, Python 3.9
_______________________________________
Python tracker <rep...@bugs.python.org>
<https://bugs.python.org/issue39652>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe:
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
