1

I am having an error while trying to run a query with the magic function %sql in jupyter notebook

My query:

%sql SELECT "Elementary, Middle, or High School" FROM chicago_public_schools_data LIMIT 1;

Error:

  • sqlite:///socioeconomic.db (sqlite3.OperationalError) near "or": syntax error [SQL: SELECT Elementary, Middle, or High School FROM chicago_public_schools_data LIMIT 1;] (Background on this error at: https://sqlalche.me/e/20/e3q8)

I'm sure that column name is matching, controlled by selecting all columns, or printing with 'df.columns'.

Index(['School_ID', 'Elementary, Middle, or High School','Street_Address'......

Don't know how can I solve this.

I tried to use (") instead of ('), or use %%sql instead of %sql

Here is an example screenshot from my data: enter image description here

Here is a screenshot from the command line with my query, and example query. I'm getting output with my query in the command line. So my syntax works well on the command line but not on jupyter notebook. enter image description here

QHarr
  • 83,427
  • 12
  • 54
  • 101
Berchister
  • 23
  • 4
  • Edit the question to provide a sample of the data in your `.db` file. Elaborate on what is your expected output. A screenshot of sample data and text of expected output. – Bibhav Apr 19 '23 at 11:46
  • The syntax seems to be correct. Have you tried it with SQLite directly? – Bibhav Apr 19 '23 at 12:06
  • @Bibhav No, I did not. I was doing homework with 10 questions, I had a problem only with that query. I don't even know how to try it with sqlite directly – Berchister Apr 19 '23 at 12:08
  • Open cmd your working directory (*location of your file*) run `sqlite3 filename.db`. Then run your query there.(*Obviously don't use `%sql` there*) – Bibhav Apr 19 '23 at 12:11
  • @Bibhav Added a screenshot. – Berchister Apr 19 '23 at 12:31

1 Answers1

0

Using the cell command:
Parsing for a single line doesn't seem to be good in the jupyter notebook.
I recommend you make use of the cell command every time you execute the SQL command using the magic function in jupyter notebook.

%%sql
SELECT "Elementary, Middle, or High School" FROM chicago_public_schools_data LIMIT 5;

This works and %sql doesn't because using %sql parses the line as:

SELECT Elementary, Middle, or High School FROM chicago_public_schools_data LIMIT 5;

This will raise errors.

Bibhav
  • 1,579
  • 1
  • 5
  • 18