2

I am trying to query the pandas data frame with SQL syntax. I imported "import pandasql as ps", and loaded a dataframe. Then i entered this simple syntax:

ps.sqldf(
    """
    SELECT department, first_name, AVG(salary) OVER (PARTITION BY department) AS average
    FROM employee
    GROUP BY department, first_name, salary
    """
    ).head()

it returned this error:

PandaSQLException                         Traceback (most recent call last)
<ipython-input-6-2ecbea651075> in <module>
----> 1 ps.sqldf(
      2     """
      3     SELECT department, first_name, AVG(salary) OVER (PARTITION BY department) AS average
      4     FROM employee
      5     GROUP BY department, first_name, salary

~/.pyenv/versions/3.9.0/lib/python3.9/site-packages/pandasql/sqldf.py in sqldf(query, env, db_uri)
    154     >>> sqldf("select avg(x) from df;", locals())
    155     """
--> 156     return PandaSQL(db_uri)(query, env)

~/.pyenv/versions/3.9.0/lib/python3.9/site-packages/pandasql/sqldf.py in __call__(self, query, env)
     61                 result = read_sql(query, conn)
     62             except DatabaseError as ex:
---> 63                 raise PandaSQLException(ex)
     64             except ResourceClosedError:
     65                 # query returns nothing

PandaSQLException: (sqlite3.OperationalError) near "(": syntax error
[SQL: 
    SELECT department, first_name, AVG(salary) OVER (PARTITION BY department) AS average
    FROM employee
    GROUP BY department, first_name, salary
    ]
(Background on this error at: http://sqlalche.me/e/14/e3q8)

I tried rewriting the entire query, copy and paste again from notebook, but still can't fix the "(" problem. Would you guys please tell me how to fix ti?

yts61
  • 1,142
  • 2
  • 20
  • 33
  • 1
    Window functions are supported in SQLite since version 3.25.0 Check your version. – forpas May 16 '21 at 11:31
  • you are right, my sqlite was outdated, it was a 3.24. I then updated it to 3.35.5 through my Terminal. But when i go back to my VSCode, and run "import sqlite3 sqlite3.sqlite_version" in ipynb, it says it is still 3.24. How can i fix it? i am using pyen 3.9.0. , – yts61 May 16 '21 at 15:39
  • i run "brew upgrade sqlite3" again, it says "Warning: sqlite3 3.35.5 already installed", but the error "PandaSQLException: (sqlite3.OperationalError) near "(": syntax error" persists. – yts61 May 16 '21 at 15:45
  • i followed the instruction here till step 3. 1) Download SQLite tarball from https://www.sqlite.org/download.html 2) Extract the contents and cd into the folder. 3) Run the following commands: ./configure sudo make install – yts61 May 16 '21 at 17:17
  • https://stackoverflow.com/questions/55508830/how-to-upgrade-sqlite-3-8-2-to-3-8-3 – yts61 May 16 '21 at 17:18
  • i am not using virtual env, so i don't how to set up the 4th step, "export LD_LIBRARY_PATH="/usr/local/lib" " – yts61 May 16 '21 at 17:19
  • I followed this, and it works. https://stackoverflow.com/questions/55508830/how-to-upgrade-sqlite-3-8-2-to-3-8-3 – yts61 May 16 '21 at 17:30

0 Answers0