SQlite3 syntax error UPDATE statement on flask - python

Question

i've developed a APIful webapp using sqlite3 and flask framework with python, i have an external client that sends POST request, and it works correctly.

On my Windows environment the update query works correctly, when i try to execute all on my CentOS environment my query it return a syntax error, the following code is:

    @app.route('/apis/rcvInfo', methods=['POST'])
    def form_to_json():
        if request.method == "POST":
            table=request.form['table']
            status=str(request.form['status'])

            ####query operations
            nEntries = (sendQuery("SELECT COUNT (*) AS RowCnt  FROM "+table))[0]
            if (nEntries > 0 ):
                sendQuery("UPDATE "+table+" SET status = "+status+", lastcheck=CURRENT_TIMESTAMP ORDER BY lastcheck DESC LIMIT 1")
            else:
                sendQuery("INSERT INTO "+table+" (status) VALUES ("+status+")")
                
            db.get_db().commit()

            return json.dumps({'success':True}), 200, {'ContentType':'application/json'} ``` 

def sendQuery(query):
    cursor = db.get_db().cursor()
    print (query)
    cursor.execute(query)
    records = cursor.fetchone()

    return records

On my Windows environment the UPDATE statement work correctly, updating the first row.

But on my CentOS env i have the following error message:

sqlite3.OperationalError: near "ORDER": syntax error

The params (table, status) are correct.

Can't see how this error is OS-specific. Generally in SQL, ORDER BY is not used in UPDATE actions. — Parfait
– Parfait, Commented Sep 30, 2020 at 12:46
From: sqlite.org/lang_update.html#optional_limit_and_order_by_clauses: If SQLite is built with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax of the UPDATE statement is extended with optional ORDER BY and LIMIT So no, ORDER BY is not supported in UPDATE in common releases of SQLite. — forpas
– forpas, Commented Sep 30, 2020 at 12:47

forpas · Accepted Answer · 2020-09-30 13:19:26Z

As mentioned in 2.3. Optional LIMIT and ORDER BY Clauses:

If SQLite is built with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax of the UPDATE statement is extended with optional ORDER BY and LIMIT clauses...

So in a common release of SQLite you can't use ORDER BY and LIMIT with an UPDATE statement.

In your case as a workaround, if the column lastcheck contains unique values you can do this:

UPDATE tablename
SET status = ?,
    lastcheck = CURRENT_TIMESTAMP
WHERE lastcheck = (SELECT MAX(lastcheck) FROM tablename)

or:

UPDATE tablename
SET status = ?,
    lastcheck = CURRENT_TIMESTAMP
WHERE lastcheck = (SELECT lastcheck FROM tablename ORDER BY lastcheck DESC LIMIT 1)

If the column lastcheck does not contain unique values you could use the column rowid in the ORDER BY clause of the subquery to break any ties:

UPDATE tablename
SET status = ?,
    lastcheck = CURRENT_TIMESTAMP
WHERE lastcheck = (SELECT lastcheck FROM tablename ORDER BY lastcheck DESC, rowid DESC LIMIT 1)

Collectives™ on Stack Overflow

SQlite3 syntax error UPDATE statement on flask - python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related