user5740843 user5740843 - 1 year ago 107
SQL Question

Python foreach from a MySQLdb

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()

>> 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!

Answer Source

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,
cur = db.cursor()

ago = datetime.datetime.utcnow() - datetime.timedelta(minutes=15)
    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'

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: 


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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download