I cannot SELECT a recent INSERT to my database unless I stop and restart my program

Question

When I run my code, I am trying to INSERT some info into my database and then recall that info with SELECT. I can see the new entry when I manually check the database. The entry is being inserted but my python code can't see it until I exit and re-run it.

These are being called through an async bot (not shown below). But the same thing happens in the self contained code below. You'll notice some over-use of commits because I'd read a few places people had success committing before a select, but its not working.

I've found a couple questions on here pertaining to this issue but none of their solutions seem to be working for me.

EDIT: If it's not clear from the code itself, these are adding dice rolls to a database and then returning them base on either epoch dates and number of entries.

import sqlite3, time
from datetime import datetime

def add_roll(user=None, nick=None, argument=None, equation=None, result=None, stat=None, success=None, comment=None):
    conn = sqlite3.connect('dicebot.db')
    c = conn.cursor()

    sql = "INSERT INTO rolls (messagetime, user, nick, argument, equation, result, stat, success, comment) VALUES (?,?,?,?,?,?,?,?,?)"
    values = (time.time(), user, nick, argument, equation, result, stat, success, comment)
    c.execute(sql,values)
    conn.commit()    
    conn.close()

def get_entry(date_in_epoch=0, date_out_epoch=time.time(), number_of_entries=1):
    conn = sqlite3.connect('dicebot.db')
    c = conn.cursor()
    conn.commit()

    select_stmt = '''SELECT * FROM rolls ORDER BY messagetime DESC LIMIT (%s)''' % (number_of_entries,)

    if number_of_entries == -1:
        select_stmt = '''SELECT * from rolls WHERE messagetime BETWEEN (%s) and (%s) ORDER BY messagetime''' % (date_in_epoch, date_out_epoch)
    elif number_of_entries >= 0:
        select_stmt = '''SELECT * from rolls WHERE messagetime BETWEEN (%s) and (%s) ORDER BY messagetime DESC LIMIT (%s)''' % (date_in_epoch, date_out_epoch, number_of_entries)

    x = c.execute(select_stmt)
    records = x.fetchall()

    output = []
    for record in records:
        nick = record[3]
        equation = record[5]
        result = record[6]
        stat = record[7]
        success = record[8]
        comment = "" if record[9] is None else record[9]

        if success is not None:
            output.append("**{}:** {} {} {} (Stat={})".format(nick, result, success, comment, stat))
        else:
            output.append("**{}:** {} {}".format(nick, result, comment))

    conn.close()
    return output


if __name__ == '__main__':
    add_roll("MyName", "MyHandle", "1d6+2", "(3)+2", 5, 45, None)
    for record in get_entry(number_of_entries=10):
        print (record)

What is the type of `messagetime` in the table? – John Gordon Oct 06 '20 at 00:29 — John Gordon, Oct 06 '20 at 00:29

score 2 · Answer 1 · answered Oct 06 '20 at 00:29

The problem is your default value for date_out_epoch.

def get_entry(date_in_epoch=0, date_out_epoch=time.time(), number_of_entries=1):

Default values are evaluated when the function is defined, not every time the function is called. Since you're not specifying a value for this parameter when you call the function, you only see the rows that were created before get_entry() was defined, which is when the script is loaded.

If you change your later code to:

    for record in get_entry(number_of_entries=10, date_out_epoch = time.time()):
        print (record)

you'll see all the new rows.

To make get_entry() implement the default you want, you can define it like this:

def get_entry(date_in_epoch=0, date_out_epoch=None, number_of_entries=1):
    if date_out_epoch is None:
        date_out_epoch = time.time()
    ...

you just answered multiple questions for me haha. I was having what i thought was a different problem with a different set of code, but it's definitely the same problem. thank you! — russellaugust, Oct 06 '20 at 00:33
There are lots of problems that can be caused by inappropriate uses of default values. See https://stackoverflow.com/questions/1132941/least-astonishment-and-the-mutable-default-argument for the more common one — Barmar, Oct 06 '20 at 01:08

I cannot SELECT a recent INSERT to my database unless I stop and restart my program

1 Answers1