chrki chrki - 9 days ago 9
MySQL Question

New rows not showing up after SQL INSERT & "commit" with Python and SQL

I made a loop in Python that calls itself to repeatedly check for new entries in a database. On first execution, all affected rows are shown fine. Meanwhile, I add more rows into the database. On the next query in my loop, the new rows are not shown.

This is my query-loop:

def loop():
global mysqlconfig # username, passwd...
tbd=[] # this is where I save the result
conn = MySQLdb.connect(**mysqlconfig)
conn.autocommit(True)
c = conn.cursor()
c.execute("SELECT id, message FROM tasks WHERE date <= '%s' AND done = 0;" % now.isoformat(' '))
conn.commit()
tbd = c.fetchall()
print tbd
c.close()
conn.close()

time.sleep(5)
loop()
loop()


This is the SQL part of my Python insertion-script:

conn = MySQLdb.connect(**mysqlconfig)
conn.autocommit(1)
c = conn.cursor()
c.execute("INSERT INTO tasks (date, message) VALUES ('{0}', '{1}');".format("2012-10-28 23:50", "test"))
conn.commit()
id = c.lastrowid
c.close()
conn.close()


I tried SQLite, I tried Oracle MySQL's connector, I tried MySQLdb on a Windows and Linux system and all had the same problem. I looked through many, many threads on Stackoverflow that recommended to turn on autocommit or use commit() after an SQL statement (ex. one, two, three), which I tried and failed.

When I added data with HeidiSQL to my database it showed up in the loop query, but I don't really know why this is. Rows inserted with mysql-client on Linux and my Python insertion script never show up until I restart my loop script.

I don't know if it's the fact that I open 2 connections, each in their own script, but I close every connection and every cursor when I'm done with them.

Answer

The problem could be with your variable now. I don't see anywhere in the loop that it is being reset.

I'd probably use the mysql NOW() function:

c.execute("SELECT id, message FROM tasks WHERE date <= NOW() AND done = 0;")

It looks like the time you are inserting into the database is a time in the future. I don't think your issue is with your database connection, I think it's something to do with the queries you are doing.

Comments