OperationalError when using datetime

63 views

Skip to first unread message

Maurice Waka

unread,

Jul 18, 2018, 1:08:24 AM7/18/18

to web2py-users

I have two problems;

When I successfully submit a form and try to retrieve the text for processing in the controller, I tend to get the previously submitted text and not the latest. I tried to get the latest database insert based on the current time using this code but get the operational error. I want to check the current time in order to retrieve the latest inserted text.

controller code:

def retrieve():
    dt=request.now
    row = db(db.post.author== auth.user.id).select(db.post.ALL, orderby=~db.post.created_on == dt, limitby=(0,1)).first()
    message = row.message if row else None
    return dict(message=message, form=form)

Error message

    row = db(db.post.author== auth.user.id).select(db.post.ALL, orderby=~db.post.created_on == dt, limitby=(0,1)).first() File "/usr/local/lib/python2.7/dist-packages/pydal/objects.py", line 2250, in select
 return adapter.select(self.query, fields, attributes)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/sqlite.py", line 82, in select
 return super(SQLite, self).select(query, fields, attributes)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 762, in select
 return self._select_aux(sql, fields, attributes, colnames)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 718, in _select_aux
 rows = self._select_aux_execute(sql)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 712, in _select_aux_execute
 self.execute(sql)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/__init__.py", line 67, in wrap
 return f(*args, **kwargs)
 File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 412, in execute
 rv = self.cursor.execute(command, *args[1:], **kwargs)
OperationalError: near "DESC": syntax error

Problem/query:

Is there a way to have a trigger in the db, that when there is an insert, the db updates to the controller the current text? something like:

CREATE TRIGGER your_table_trig AFTER UPDATE ON your_table BEGIN update your_table SET updated_on = datetime('now') WHERE user_id = NEW.user_id; END;

黄祥

unread,

Jul 18, 2018, 1:31:56 AM7/18/18

to web2py-users

orderby should be table's field not a conditional comparison

ref:

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#orderby--groupby--limitby--distinct--having-orderby_on_limitby-left-cache

for 2nd question: web2py auth.signature will cover to update value of field modified_on when user update the table, for any specific table's field perhaps you can use before or after callback

ref:

http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#callbacks-on-record-insert--delete-and-update

best regards,

stifan

Anthony

unread,

Jul 18, 2018, 10:30:06 PM7/18/18

to web2py-users

On Wednesday, July 18, 2018 at 1:08:24 AM UTC-4, Maurice Waka wrote:

I have two problems;
When I successfully submit a form and try to retrieve the text for processing in the controller, I tend to get the previously submitted text and not the latest.

What do you mean by this? What is the exact workflow, and what does your code look like? When a form is submitted, the controller action that handles the submission has access to the current submission (not the previous one).

def retrieve(): dt=request.now row = db(db.post.author== auth.user.id).select(db.post.ALL, orderby=~db.post.created_on == dt, limitby=(0,1)).first()

The above is not a valid "orderby" -- it must just be a field, not a query. There is no need for matching anything on request.now, and any inserts made during the current request would not match request.now exactly, as request.now is calculated before any of your app code runs (and therefore before any inserts can happen). Just order by created_on (descending) and take the first record. Anyway, this may not be what you really want -- if you're trying to get a given record right after it was inserted, this approach could lead to a race condition, as another record could be inserted before you do the read.

Anthony

Maurice Waka

unread,

Jul 19, 2018, 3:56:37 AM7/19/18

to web...@googlegroups.com

Team, Anthony, thanks so much for the help.

Anthony, I believe, I've mentioned this before, in previous questions about the other apps. You've helped me a lot and I appreciate very much. The current issue is that, I am developing a system of q&a with the aim of storing the questions for future machine learning purposes. That once a user inputs a query, the question is stored in post (in storage.sqlite), but at the same time, that question will be processed by other functions and a reply send back to the user I want to retrieve that question to the controller or modules for processing.

This is the controller code:

@mobilize

@auth.requires_login()

def view_searches():

if db(db.post).isempty():

"""If the db is completely deleted then we get an error as the iteration is over an empty table."""

db.post.insert(message="Well_hest")# well_hest is an internal keyword/trigger word

dt=request.now

row = db(db.post.author== auth.user.id).select(db.post.ALL, orderby=~db.post.created_on, limitby=(0,1)).first()

reports = row.message if row else None

return dict(reports=reports)

@mobilize

@auth.requires_login()

def search():

form = SQLFORM(db.post).process()

if form.accepts(request, formname=None):

return DIV("Message posted")

elif form.errors:

return TABLE(*[TR(k, v) for k, v in form.errors.items()])

return dict()

This is the html view:

<!DOCTYPE html>

<head>

<title>convForm - example</title>

</head>

<body>

</select>

</form>

</div>

</section>

var rollbackTo = false;

var originalState = false;

function storeState(stateWrapper) {

rollbackTo = stateWrapper.current;

console.log("storeState called: ",rollbackTo);

}

function rollback(stateWrapper) {

console.log("rollback called: ", rollbackTo, originalState);

console.log("answers at the time of user input: ", stateWrapper.answers);

if(rollbackTo!=false) {

if(originalState==false) {

originalState = stateWrapper.current.next;

console.log('stored original state');

}

stateWrapper.current.next = rollbackTo;

console.log('changed current.next to rollbackTo');

}

function restore(stateWrapper) {

if(originalState != false) {

stateWrapper.current.next = originalState;

console.log('changed current.next to originalState');

}

jQuery('#myform').submit(function() {

ajax('{{=URL('search')}}', '#myform', 'target');

return false;

});

</script>

</body>

</html>

With the code above, see the images attached.

You'll note that if I post a query like: 'testing 123' it will be posted to the post table as shown in the image. But retrieving it back to the controller, just for the purpose of retrieving what is posted, I get 'maurice' which is a previous post instead.

This is my biggest headache. That is why i tried getting a way to refresh the Db, or using request.now (thinking that it will get the response with current time)

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/nvgzq49Z9kA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

screen_result.png

Sqlite_image.png

Maurice Waka

unread,

Jul 19, 2018, 4:40:14 AM7/19/18

to web...@googlegroups.com

Some more info, this is the module code:

Post = db.define_table('post',

Field('author', 'reference auth_user', default=auth.user_id, writable=False, readable=False),

Field('message', 'text', requires=IS_NOT_EMPTY(),

notnull=False, ),

auth.signature

)

On Thu, Jul 19, 2018 at 5:30 AM Anthony <abas...@gmail.com> wrote:

--

Dave S

unread,

Jul 19, 2018, 7:04:10 AM7/19/18

to web2py-users

On Thursday, July 19, 2018 at 12:56:37 AM UTC-7, Maurice Waka wrote:

Team, Anthony, thanks so much for the help.
Anthony, I believe, I've mentioned this before, in previous questions about the other apps. You've helped me a lot and I appreciate very much. The current issue is that, I am developing a system of q&a with the aim of storing the questions for future machine learning purposes. That once a user inputs a query, the question is stored in post (in storage.sqlite), but at the same time, that question will be processed by other functions and a reply send back to the user I want to retrieve that question to the controller or modules for processing.
This is the controller code:

The controller code isn't too complicated, aside from the order-by problems, but I get lost in the html file.

SQLFORM will tell you the id of the record inserted

<URL:http://web2py.com/books/default/chapter/29/07/forms-and-validators#SQLFORM-and-insert-update-delete>

and that id can be stored in a session variable your view_searches()function can use. But I would consider making the SQLFORM the primary page content in a conventional web2py view, and using the LOAD() helper function to update the view area.

<URL:http://web2py.com/books/default/chapter/29/12/components-and-plugins#LOAD>

/dps

Anthony

unread,

Jul 19, 2018, 10:03:03 AM7/19/18

to web2py-users

row = db(db.post.author== auth.user.id).select(db.post.ALL, orderby=~db.post.created_on, limitby=(0,1)).first()
reports = row.message if row else None

If you are only returning the "message" field, just select the db.post.message field, no need to use db.post.ALL.

def search():
form = SQLFORM(db.post).process()
if form.accepts(request, formname=None):

You cannot call form.accepts after having called form.process, as the latter already calls the former for you.

return DIV("Message posted")
elif form.errors:
return TABLE(*[TR(k, v) for k, v in form.errors.items()])
return dict()

In all cases, you code will return an empty dict. If there is no associated view, you will get an error unless generic views have been enabled, in which case, you will get generic.html.