I am using SQLite3 with Python and Windows10. I wrote the following code.
curs = conn.cursor()
curs.execute('''
DELETE FROM stack ORDER BY created DESC LIMIT 1;
''')
And I execute that code on Bash on Ubuntu (WSL). That is succeeded. But I got a error message "syntax error" on runnning on cmd.exe (not WSL).
>python stack.py
near "ORDER": syntax error
My wrote code is illegal on Windows? Python version is 3.7.0 on cmd.exe, and 3.5.2 on Bash.
As mentioned in the documentation, DELETE only accepts ORDER BY and LIMIT if the SQLITE_ENABLE_UPDATE_DELETE_LIMIT option is set when, not only compiling the sqlite3.c amalgamation file, but also when creating it from the master source. This is not done by default; it has to be specially turned on.
So to use it, you'd have to download sqlite-src-XXXXXX.zip (Near the bottom of the page), use that to create the sqlite3.c file (On unixish environments, you'd run ./configure with the --enable-update-limit option; not sure how to to turn it on using the MSVC build instructions as I've never used them), and run make. That'll produce sqlite3 libraries that have it enabled. Then you have to make Python use them (Also something I don't know how to do; I'm pretty sure there's other questions here on that topic that have answers you can look up).
Basically, unless you're working on something written in C or C++ and can just include sqlite3.c directly in your project instead of using a library version, using any of the optional features in sqlite is a pain in the rear and I get the impression most people don't bother.
ORDER BYandLIMITwithDELETE? It's not enabled by default