0

I am using pandasql in python and I am new to SQlLITE


query = '''
select row_number() over (order by cumSum) as SegmentOrder
, min(rowLoc) as SegmentStart
, max(rowLoc) - min(rowLoc) as Height
from df_lineLocations
where line = 0
--and CumSum !=0
group by cumSum
'''

df_SegmentLocations  = ps.sqldf(query, locals())
df_SegmentLocations

I tried removing the parentheses in row_number() but nothing happened

Mohamed Amine
  • 340
  • 1
  • 4
  • 16
  • Can you add the full traceback of the error that gets printed to the console when you run please. – ChrisOram Sep 01 '21 at 16:01
  • PandaSQLException: (sqlite3.OperationalError) near "(": syntax error [SQL: select row_number over (order by cumSum) as SegmentOrder , min(rowLoc) as SegmentStart , max(rowLoc) - min(rowLoc) as Height from df_lineLocations where line = 0 --and CumSum !=0 group by cumSum ] (Background on this error at: https://sqlalche.me/e/14/e3q8) – Mohamed Amine Sep 02 '21 at 13:16
  • @BarbarosÖzhan I changed ''' with """ but nothing happened – Mohamed Amine Sep 04 '21 at 08:33

1 Answers1

0

You just need to update your SQLite to the latest version and this query will work.

If you are working on Colab then the default SQLite version is old. And older versions throws up OperationalError whenever there are compound queries which require special characters like ( , * etc.

I have referred to this answer to update SQLite to the latest version on Colab: https://stackoverflow.com/a/59429952/13248955

Here's the code snippet:

# compiling is the same as before
!curl https://www.sqlite.org/src/tarball/sqlite.tar.gz?r=release | tar xz
%cd sqlite/
!./configure
!make sqlite3.c
%cd /content
!npx degit coleifer/pysqlite3 -f
!cp sqlite/sqlite3.[ch] .
!python setup.py build_static build

will unzip the sqlite latest version

# check where it is now
!ls build

lib.linux-x86_64-3.7 temp.linux-x86_64-3.7

# !ls build/lib.linux-*version which you got as output in previous step*/pysqlite3/

!ls build/lib.linux-x86_64-3.7/pysqlite3/

dbapi2.py init.py _sqlite3.cpython-37m-x86_64-linux-gnu.so

!ls /usr/lib/ | grep python

The output will be something like this:

python2.7 python3 python3.6 python3.7 python3.8

 # From the list, select python3.7
 !cp build/lib.linux-x86_64-3.7/pysqlite3/_sqlite3.cpython-37m-x86_64-linux-gnu.so/usr/lib/python3.7/lib-dynload/

then MENU: Runtime > Restart runtime

 import sqlite3
 sqlite3.sqlite_version

'3.37.2' // will print out the latest SQLite version

Simran Biswas
  • 1
  • 1
  • It would be great if you could post the code snippet from the linked answer. We dont usually trust the links, as they are prone to changing or dying in a way that your answer cannot control~ – Simas Joneliunas Feb 18 '22 at 01:42
  • I just edited the answer with the code snippet. Hope it helps! – Simran Biswas Feb 22 '22 at 13:28