0

As in title, my script works on my Windows machine but doesn't work on my Ubuntu server. It can't process my SQL command:

import sqlite3
conn = sqlite3.connect('data.db')
c = conn.cursor()

c.execute("""
        select distinct user_id
        from
            ( select *,
                    count(1) over (partition by user_log) as occurs
                from rank2_log
            ) AS id
        where occurs > 2; """)

Error:

 File "/root/Log/log.py", line 91, in check_all
14|log |     where occurs > 2;""")
14|log | sqlite3.OperationalError: near "(": syntax error

I checked SQLite module versions and they're the same ( 2. 6 .0 ). Also tried to change it to normal comment from multi line for some reason but that doesn't work either.

Dano
  • 55
  • 6
  • 1
    Why don't use normal group by with having? – Selvin Apr 07 '21 at 22:24
  • @Selvin That's not my command i don't know SQL well enough to do advanced ones like these. – Dano Apr 07 '21 at 22:25
  • 3
    The error is not from that code. The "where" is lowercase in the error. – Tim Roberts Apr 07 '21 at 22:25
  • I changed it in here so it's easier to read.. @TimRoberts – Dano Apr 07 '21 at 22:26
  • 1
    That's not a good plan. It's too easy for you to "tweak" one little thing that is actually causing the error. – Tim Roberts Apr 07 '21 at 22:27
  • Sorry... Changed it back to the original one @TimRoberts – Dano Apr 07 '21 at 22:28
  • 2
    Well, there's no syntax error here, unless you have some odd character in your file. Does your source file have Unix line endings or WIndows? – Tim Roberts Apr 07 '21 at 22:35
  • No it doesn't,, i even tried to run it in separate file without any other stuff. May it be because my python version is different? 3.8.6 on Windows and 3.6.9 on Linux – Dano Apr 07 '21 at 22:38
  • That error message is indicative of a version of sqlite3 that's too old to have window function support. `sqlite3.sqlite_version` is the important bit, not the version of the Python module. – Shawn Apr 07 '21 at 23:41
  • @Shawn Got it... my linux version is 3.22.0 which doesn't have those window functions. Will try to figure out how to update it if it's even possible. Thank you! – Dano Apr 07 '21 at 23:57

1 Answers1

1

Do you only have the sqlite3 module installed, or do you have the cli tool too? I would first run sqlite3 from the command line, and try to run that query. If that works, then the sqlite3 version you are using is validated.

Just to double check, what is the output of pip list | grep sqlite3 and pip list | grep sqlite3 ? on windows and ubuntu?

if the modules are exactly the same, and the input files are exactly the same, then the problem, i would think, is the python version.

Additionally, i agree with @TimRoberts mentioned. Check the python file for line endings. You can follow the below link for stackexchange answer on how to do it:

How to find out line-endings in a text file?

Ricardo
  • 472
  • 3
  • 6
  • I used `sqlite3.version` instead of `sqlite3.sqlite_version` when checking for the version... Turns out my windows version is 3.32.3, linux is 3.22.0 . I will try to figure out how to update it to a newer version, thank you! And kind of out of topic, how should i check the version using pip again? pip list returns list of user installed modules i think and sqlite is pre installed. – Dano Apr 08 '21 at 00:10
  • If you do pip install [module], and the module is already installed, you should get message stating that the requirement is already satisfied, and it should state the version too. – Ricardo Apr 08 '21 at 12:27