I have an sqlite database with a view of several tables with a lot of columns with spaces in their names (I know, I know, not good practice, but it's out of my control).
Anyways, so the problem that I'm having is related to the spaces in the column names when using pd.read_sql('SELECT "stupid column with space" from StupidView',con=db). It keeps keeping the quotes in the column name when querying the view but not when querying the table itself! Same SQL on the table returns columns without being wrapped in quotes. Am I missing something here? Any ideas on why this is happening?
Working standalone example:
import pandas as pd
import sqlite3
import numpy as np
pd.set_option('display.width', 1000)
# Create the database
db = sqlite3.connect("Sample Database.sqlite")
cursor = db.cursor()
# Create a table
df = pd.DataFrame({"Stupid column with space":[1,2,3,4],
"MrNoSpace":[1,2,3,4]})
# Push the tables to the database
df.to_sql(name="StupidTable", con=db, flavor='sqlite', if_exists='replace', index=False)
# Just in case you're running this more than once, drop the view if it exists
try: cursor.execute("DROP VIEW StupidView;")
except: pass
# Execute the sql that creates the view
cursor.execute("""
CREATE VIEW StupidView AS
SELECT StupidTable.*
FROM StupidTable""")
db.commit()
# Execute the SQL and print the results
Test1_df = pd.read_sql('SELECT "Stupid column with space", "MrNoSpace" FROM StupidView',con=db) # read data from the view
Test2_df = pd.read_sql('SELECT "Stupid column with space", "MrNoSpace" FROM Table1',con=db) # same sql but on the table
Test3_df = pd.read_sql('SELECT `Stupid column with space`, `MrNoSpace` FROM StupidView',con=db) # using ` and not "
Test4_df = pd.read_sql('SELECT [Stupid column with space], [MrNoSpace] FROM StupidView',con=db) # using []
print Test1_df
print Test2_df
print Test3_df
print Test4_df
Output:
Test1_df - Query on the view: columns are wrapped in double quotes
"Stupid column with space" "MrNoSpace"
0 1 1
1 2 2
2 3 3
3 4 4
Test2_df - Same query but on the table: now with no quotes in column names
Stupid column with space MrNoSpace
0 1 1
1 2 2
2 3 3
3 4 4
Test3_df - Keeps the column names wrapped in ` (works fine if the query on a table but not a view)
`Stupid column with space` `MrNoSpace`
0 1 1
1 2 2
2 3 3
3 4 4
Test4_df - Drops the column names altogether (works fine if used on table not view)
0 1 1
1 2 2
2 3 3
3 4 4
