Forums

Hello,

I have a small personal website on PythonAnywhere (great!!), based on web2py.

It includes a small sqlite database. I have a (rather complex) query which perfectly worked until some days ago that doesn't work anymore.

The query uses the SQL Random() function as indicated by web2py docs:

rows = db(...).select(..., orderby='<random>')

It doesn't return me a random set of rows but rather always the same rows, starting from the beginning of the possible matching rows. I tried to address the problem without success. I also tried not using the web2py dialect and replacing it with

import random
rows=db(...).select().sort(lambda row: random.random())

as explained in the web2py doc. No success either.

I checked on my pc that the query is OK (I downloaded the database and tried the same query which perfectly works).

Could anybody tell me if PythonAnywhere changed anything in the config so that the Random() function could not be executed anymore (I know that random doesn't work on Google NoSQL) ? Or give me some hints ...

Thanks in advance Dom

Our last upgrade was at the end of October, so if this only changed "a few days ago" then that's probably not it. Still, you can check with a:

sqlite3 --version

Both on PythonAnywhere and locally?

Hi Harry,

Thanks for replying. I said a few days ago but I don't really know. My son told me about it (he said" some weeks ago"). I'll ask him exactly. To me, it is clearly the update.

Locally, on my pc, with my version of web2py, it works fine. It's on the website thats it doesn't work.

Is it possible to go back to the previous version. Or what should I do to have it work ? Thanks Dom

Hi again,

I talked to my son. He told me that the last time he visited the website was at least one month ago.

So, I am pretty sure that the upgrade is responsible for the problem. My local version (on my personal pc at home) of sqlite, which is included in Python, is 3.6.21. This one works fine.

The version on the PythonAnywhere website is 3.8.2.

Dom

It looks like sqlite is behaving as it should. This little test-script is returning randomly sorted letters as expected:

import sqlite3
import string

def populate_db():
    conn = sqlite3.connect("random.db")
    c = conn.cursor()
    try:

        c.execute("""
        drop table if exists letters;
        """)
        c.execute("""
        create table letters(c);
        """)
        c.executemany("insert into letters(c) values (?)", string.ascii_letters)
        conn.commit()
    finally:
        c.close()
        conn.close()


def query_db():
    try:
        conn = sqlite3.connect("random.db")
        c = conn.cursor()
        c.execute("select * from letters order by random()")
        print c.fetchall()
    finally:
        c.close()
        conn.close()

populate_db()
query_db()

Check the SQL that web2py is generating for your queries to make sure that it's what you would expect and drop the results into the test program to see what happens.

Hello Glenn,

Thanks for your reply.

I tried your example. It works fine on the website.

With a view to understanding the problem, I made some other tests using my site.

As far as I understand, the problem appears when there is a many to many relationship and when the query asks for a random order.

Unfortunately, I only owns one site on PythonAnywhere, so I cannot test the following test app (using web2py) which has a M2M relation.

db.define_table('person',
                Field('name'))
db.define_table('thing',
                Field('name'))

db.define_table('ownership',
                Field('person', 'reference person'),
                Field('thing', 'reference thing'))

def populate_db():
db.person.insert(name='alex')
db.person.insert(name='bob')
db.person.insert(name='curt')
db.thing.insert(name='boat')
db.thing.insert(name='chair')
db.thing.insert(name='shoes')
db.ownership.insert(person=1, thing=1) # Alex owns Boat
db.ownership.insert(person=1, thing=2) # Alex owns Chair
db.ownership.insert(person=2, thing=3) # Bob owns Shoes
db.ownership.insert(person=3, thing=1) # Curt owns Boat too
db.ownership.insert(person=2, thing=1) # Bob owns Boat too
return

def m2m_query():
persons_and_things = db((db.person.id==db.ownership.person)& (db.thing.id==db.ownership.thing))
all_rows = persons_and_things.select(db.person.name, db.thing.name, orderby='<random>')
alex_rows = persons_and_things(db.person.name=='alex').select(db.person.name, db.thing.name)
boat_rows = persons_and_things(db.thing.name=='boat').select(db.person.name, db.thing.name, orderby='<random>')
return dict(all_rows = all_rows, alex_rows=alex_rows, boat_rows=boat_rows, lastSQL=db._lastsql)

It uses an example from web2py doc. It works fine on my local pc, ordering rows at random, as it should. The SQL returned by web2py seems ok to me.

Could anybody at PythonAnywhere test this ?

Thanks in advance

Dom

You don't need a new web app to try that out. You can just use the web2py DAL standalone.

OK. I made a test. It works fine. Results are given at random with this little test.

Do you have any idea to explain why my query does not work as it should only on the website ? Thanks for any hint.

Dom

Did you use the database and the code that you're using on the site?

Hi Glenn,

The answer to your question is yes.

To make my last test, I was unable to do :

from gluon import DAL, Field

from either a python console or a bash console (on PythonAnywhere) since it returned an importError.

So I put the above test code in a function (with a new test database) in a new menu of my website and ran it.

The results were returned as they should in random order.

Anthony, from web2py users'group told me it could be a cache problem, but I already de-activated the cache in my web2py site.

So I just don't understand.

I'll try in a moment to upload the same database but I doubt it can change something.

Thanks