CODEWITHSUNDEEP
pythonmysqlpython-2.7
pylearner
pylearner

Reputation: 537

get last two queries with mysql in python

last_two_programs_dict = {} 

auto_cur.execute(
    "SELECT service_key, event_id, start_date_time, duration, prog_name "
    "FROM autodb.ssr_events "
    "WHERE (ca_details='FTA' OR ca_details='SUB') "  
    "AND recordable_event='1' "
    "AND start_date_time > CURRENT_TIMESTAMP() + 180 "
    "AND start_date_time <=> start_date_time "
    "AND duration > '00:04:00' "
    "AND service_key != '4061' "
    "ORDER BY start_date_time ASC "
    "LIMIT 2 "
)

last_two_programs = auto_cur.fetchall()

for program in last_two_programs:

    last_two_programs_dict['service_key'] = event[0]
    last_two_programs_dict['event_id'] = event[1]
    last_two_programs_dict['start_date_time'] = event[2].strftime("%Y%m%dT%H%M%SZ")
    last_two_programs_dict['duration'] = '0'+str(event[3])
    last_two_programs_dict['program_name'] = event[4]


print "Last Two programs dict", last_two_programs_dict

I would like to get the last two programs. When I run this query, it gives me only one. How can I get the last two records ?

Upvotes: 0

Views: 58

Answers (2)

Bakuutin
Bakuutin

Reputation: 291

You should try this:

last_two_programs = [
    {
        'service_key': event[0]
        'program_id': event[1]
        'start_date_time': event[2].strftime("%Y%m%dT%H%M%SZ")
        'duration': '0' + str(event[3])
        'program_name': event[4]
    } for event in auto_cur.fetchall()
]

It's called list comprehension

Upvotes: 2

franklinsijo
franklinsijo

Reputation: 18270

You are overwriting the dict in your loop, create a list and append the dicts to it on every iteration.

last_two_programs_list = []
for program in last_two_programs:
    # create a new dict every iteration
    last_two_programs_dict = {} 

    last_two_programs_dict['service_key'] = event[0]
    last_two_programs_dict['event_id'] = event[1]
    last_two_programs_dict['start_date_time'] = event[2].strftime("%Y%m%dT%H%M%SZ")
    last_two_programs_dict['duration'] = '0'+str(event[3])
    last_two_programs_dict['program_name'] = event[4]

    # append the dict to the list
    last_two_programs_list.append(last_two_programs_dict)

Also, you iterate the cursor directly rather than storing the resultset in a list.

last_two_programs_list = []
for program in auto_cur:
   # create a new dict every iteration
   last_two_programs_dict = {} 
   ....

Upvotes: 0

Related Questions