0

I have an SQLite database. In said table there exists a column that has a "-" / minus sign in it. This SQLite database was created from python 3.6 with pandas (SQLAlchemy as the engine). The table and this column is created without problem. However when I want to build a query on this table I don't know how to escape the "-" character. Here is a short example:

#imports
import numpy as np
import pandas as pd
from sqlalchemy import create_engine

#create df
df = pd.DataFrame(np.random.rand(10,2),columns=['Column1','Prob-Column'])
# create engine to connect to db
engine = create_engine('sqlite://')
#create table in db
df.to_sql('my_table',engine,if_exists='replace')

# variables
vals = '(?)'
fil = ('key',)

# create sql string
sq = 'SELECT * FROM {t} WHERE {c1} IN {vals} GROUP BY {c2}'\
.format(t='my_table',c1='Column1',c2='Prob-Column',vals = vals)

#write query to pandas df
df = pd.read_sql_query(sq,engine,params=(fil))

the trace is as follows:

OperationalError: (sqlite3.OperationalError) no such column: Prob [SQL: 'SELECT * FROM my_table WHERE Column1 IN (?) GROUP BY Prob-Column'] [parameters: ('key',)] (Background on this error at: http://sqlalche.me/e/e3q8)
tripkane
  • 47
  • 12
  • 1
    Since you are manually building an SQL statement, you are on your own when it comes to quoting: [How do you escape strings for SQLite table/column names in Python?](https://stackoverflow.com/questions/6514274/how-do-you-escape-strings-for-sqlite-table-column-names-in-python) – Ilja Everilä Oct 20 '18 at 10:17

1 Answers1

1

Here is the solution. The column name just needs double-quotes around it i.e. on the inside of the single quotes such that c2='"Prob-Column"'. Anyway hope this helps someone else.

#imports
import numpy as np
import pandas as pd
from sqlalchemy import create_engine

#create df
df = pd.DataFrame(np.random.rand(10,2),columns=['Column1','Prob-Column'])
# create engine to connect to db
engine = create_engine('sqlite://')
#create table in db
df.to_sql('my_table',engine,if_exists='replace')

# variables
vals = '(?)'
fil = ('key',)

# create sql string
sq = 'SELECT * FROM {t} WHERE {c1} IN {vals} GROUP BY {c2}'\
.format(t='my_table',c1='Column1',c2='"Prob-Column"',vals = vals)

#write query to pandas df
df = pd.read_sql_query(sq,engine,params=(fil))
tripkane
  • 47
  • 12