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.
-
1Why don't use normal group by with having?Selvin– Selvin2021年04月07日 22:24:00 +00:00Commented Apr 7, 2021 at 22:24
-
@Selvin That's not my command i don't know SQL well enough to do advanced ones like these.Dano– Dano2021年04月07日 22:25:28 +00:00Commented Apr 7, 2021 at 22:25
-
3The error is not from that code. The "where" is lowercase in the error.Tim Roberts– Tim Roberts2021年04月07日 22:25:40 +00:00Commented Apr 7, 2021 at 22:25
-
1That's not a good plan. It's too easy for you to "tweak" one little thing that is actually causing the error.Tim Roberts– Tim Roberts2021年04月07日 22:27:24 +00:00Commented Apr 7, 2021 at 22:27
-
2Well, 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– Tim Roberts2021年04月07日 22:35:12 +00:00Commented Apr 7, 2021 at 22:35
1 Answer 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:
2 Comments
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.