get last two queries with mysql in python

Question

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 ?

Can't you iterate the cursor with "for program in auto_cur: " ? Also, does your sql query actually return two rows? — ceteras
– ceteras, Commented Feb 20, 2017 at 11:55

Bakuutin · Accepted Answer · 2017-02-20 12:10:56Z

2

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

answered Feb 20, 2017 at 12:10

Bakuutin

2911 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

franklinsijo · Accepted Answer · 2017-02-20 12:04:39Z

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 = {} 
   ....

Collectives™ on Stack Overflow

get last two queries with mysql in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related