How to query columns with [] inside the names?

Question

I am working with python and sqlite3 to develop a query tool for an specific database. In this database, some columns have brackets inside the name to indicate the units of the value, such as: current_[mA].

At some point I do a query with multiple columns such as: "Select Name,Number,current_[mA],voltage_[V] FROM table"

But whenever I try to query a column with the brackets it fails to query or it does a wrong one.

I have tried to process the column name whenever it contains brackets, but I have not found a solution that works.

columns_to_query = ['Name', 'Number', 'current_[mA]', 'voltage_[V]']
query = 'SELECT * FROM table'
for col in columns_to_query:
    if '[' in col:
        colstr += ",[" + str(col) + ']'
    else:
        colstr += "," + str(col)
    colstr += ' '
    query = self.query.replace(self.query.split(' ')[1], colstr)

When I try to execute the query, I get the following error: Could not execute query. Error description --> No such column: current_

As you can see it stops on the opening bracket

What do I need to do in order to query these columns?

Thank you!

You can escape such names with `col = '"{}"'.format(col.replace('"', '""'))` (double up any double quote characters in the name, then wrap the whole in double quotes). — Martijn Pieters, Feb 04 '19 at 15:24
You also want to learn about `str.join()` and list comprehensions: `", ".join(['"{}"'.format(col.replace('"', '""')) for col in columns_to_query])`. — Martijn Pieters, Feb 04 '19 at 15:25

Alex Howansky · Accepted Answer · 2019-02-04T15:24:11.350

0

I'm not positive about SQLite3, but usually you just backquote the field names if they contain special chars or reserved words:

Select Name,Number,`current_[mA]`,`voltage_[V]` FROM table

Also note that if you build SQL queries with string concatenation, then you are likely open to SQL injection attacks. You'll want to instead use prepared statements with bound parameters.

edited Feb 04 '19 at 15:24

answered Feb 04 '19 at 15:21

Alex Howansky

50,515
8
78
98

2

While SQLite does support this, it really is a MySQL-specific syntax. Use `"`, double quote, that's the SQL standard. – Martijn Pieters Feb 04 '19 at 15:22
It worked using back quotes. Thanks! :) – Fernando Feb 04 '19 at 15:25
It also works with the double quotes as @MartijnPieters said. I will use that approach since is SQL standard. Thank you all! – Fernando Feb 04 '19 at 15:34

How to query columns with [] inside the names?

1 Answers1