Python foreach from a MySQLdb

Question

I'm trying to fetch a list of timestamps in MySQL by Python. Once I have the list, I check the time now and check which ones are longer than 15min ago. Onces I have those, I would really like a final total number. This seems more challenging to pull off than I had originally thought.

So, I'm using this to fetch the list from MySQL:

db = MySQLdb.connect(host=server, user=mysql_user, passwd=mysql_pwd, db=mysql_db, connect_timeout=10)
cur = db.cursor()
cur.execute("SELECT heartbeat_time FROM machines")
row = cur.fetchone()
print row
while row is not None:
    print ", ".join([str(c) for c in row])
    row = cur.fetchone()
cur.close()
db.close()

>> 2016-06-04 23:41:17
>> 2016-06-05 03:36:02
>> 2016-06-04 19:08:56

And this is the snippet I use to check if they are longer than 15min ago:

fmt = '%Y-%m-%d %H:%M:%S'
d2 = datetime.strptime('2016-06-05 07:51:48', fmt)
d1 = datetime.strptime('2016-06-04 23:41:17', fmt)

d1_ts = time.mktime(d1.timetuple())
d2_ts = time.mktime(d2.timetuple())

result = int(d2_ts-d1_ts) / 60

if str(result) >= 15:
        print "more than 15m ago"

I'm at a loss how I am able to combine these though. Also, now that I put it in writing, there must be a easier/better way to filter these?

Thanks for the suggestions!

i would either do it completely on the MySQL side as @sagdusmir has already said or use pandas module, which allows you easily read data ([read_sql()](http://stackoverflow.com/questions/24408557/pandas-read-sql-with-parameters)), process data and save it back ([to_sql()](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html)) — MaxU - stand with Ukraine, Jun 05 '16 at 10:15
Do this on server, return only the count value. MySql has NOW() function. — Merlin, Jun 05 '16 at 11:34

sagdusmir · Answer 1 · 2016-06-05T11:38:58.623

You could incorporate the 15min check directly into your SQL query. That way there is no need to mess around with timestamps and IMO it's far easier to read the code.

If you need some date from other columns from your table:

select * from machines where now() > heartbeat_time + INTERVAL 15 MINUTE;

If the total count is the only thing you are interested in:

SELECT count(*) FROM machines WHERE NOW() > heartbeat_time + INTERVAL 15 MINUTE;

That way you can do a cur.fetchone() and get either None or a tuple where the first value is the number of rows with a timestamp older than 15 minutes.

For iterating over a resultset it should be sufficient to write

cur.execute('SELECT * FROM machines')
for row in cur:
    print row

because the base cursor already behaves like an iterator using .fetchone().

(all assuming you have timestamps in your DB as you stated in the question)

@user5740843: if str(result) >= 15: will not work as intended. This will always be True because of the str().

liuyruc · Accepted Answer · 2016-06-05T10:45:40.427

I assume heartbeat_time field is a datetime field.

import datetime
import MySQLdb
import MySQLdb.cursors

db = MySQLdb.connect(host=server, user=mysql_user, passwd=mysql_pwd, db=mysql_db, connect_timeout=10,
                     cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()

ago = datetime.datetime.utcnow() - datetime.timedelta(minutes=15)
try:
    cur.execute("SELECT heartbeat_time FROM machines")
    for row in cur:
        if row['heartbeat_time'] <= ago:
            print row['heartbeat_time'], 'more than 15 minutes ago'
finally:
    cur.close()
    db.close()

If data size is not that huge, loading all of them to memory is a good practice, which will release the memory buffer on the MySQL server. And for DictCursor, there is not such a difference between,

rows = cur.fetchall()
for r in rows:

and

for r in cur:

They both load data to the client. MySQLdb.SSCursor and SSDictCursor will try to transfer data as needed, while it requires MySQL server to support it.

Python foreach from a MySQLdb

2 Answers2