python 3.x - Selecting a random row from postgresql database is returning "None"

Question

I am using Python 3, psycopg2 and trying to retrieve a single random row from my database if the scanned_status = 'false' but it is returning 'None'. My connection to the database is fine. Could somebody please help me with the sql. I have 3 columns: network_number, scanned_status, number_of_scans.

Here is my sql statement

def random_network_number():
    """ Retrieve a random network number from the database """
    sql_statement = "SELECT * FROM ip_store WHERE (scanned_status = false) 
    OFFSET floor(random() * (SELECT COUNT(*) FROM ip_store)) LIMIT 1;"
    network_number = execute_sql_statement(sql_statement)
    output_information(network_number)
    return network_number

Output

[ * ] None

Process finished with exit code 0

question from:https://stackoverflow.com/questions/66067107/selecting-a-random-row-from-postgresql-database-is-returning-none

Answer 1

A simpler version of your SQL would be:

SELECT * FROM ip_store WHERE (scanned_status = false) ORDER BY random() LIMIT 1;

I have a similar use case that has alway returned a record using the ORDER BY random() method.

I believe the problem with your original SQL is that you are using the count of ALL records to calculate your OFFSET and then applying it to a filtered query. I bet your OFFSET is greater than the number of filtered rows. The ORDER BY random() eliminates that problem.