Python Forum

Python Forum > Python Coding > General Coding Help > Problem with updata the data into the database as it went into the wrong rows
Full Version: Problem with updata the data into the database as it went into the wrong rows
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

chris0147

Nov-10-2017, 11:27 PM
Hi all,
I have got a problem with update the data into the database. When I ran the code, it will input the data that start with 3061 to 3070 into the database which it should have start with 3001 to 3010.

When I try this:

    for next_day, program_id in zip(nextday_list, program_id_list):
        value = program_id
        
        for i in range(0,10):
            if value == program_id:
               cur.execute("UPDATE programs SET program_id=? WHERE start_date=?",(value, next_day))
It will make the database table to show like this:

Output:
    channel             |   start_date     |  program_id
    101 BBC One South E |  20171111230000  |  3061
    101 BBC One South E |  20171111233000  |  3062
    101 BBC One South E |  20171112000000  |  3063
    101 BBC One South E |  20171112013500  |  3064
    101 BBC One South E |  20171112014000  |  3065
    101 BBC One South E |  20171112060000  |  3066
    101 BBC One South E |  20171112090000  |  3067
    101 BBC One South E |  20171112102000  |  3068
    101 BBC One South E |  20171112123000  |  3069
    101 BBC One South E |  20171112133000  |  3070
    102 BBC Two | 20171111232500  |  3061
    102 BBC Two | 20171112010000  |  
    102 BBC Two | 20171112023500  |  
    102 BBC Two | 20171112060500  |  
    102 BBC Two | 20171112065000  |  
    102 BBC Two | 20171112073500  |  
    102 BBC Two | 20171112083500  |  
    102 BBC Two | 20171112093000  |  
    102 BBC Two | 20171112130000  |  
    102 BBC Two | 20171112140000  |  
It should be:

Output:
    channel             |   start_date     |  program_id
    101 BBC One South E |  20171111230000  |  3001
    101 BBC One South E |  20171111233000  |  3002
    101 BBC One South E |  20171112000000  |  3003
    101 BBC One South E |  20171112013500  |  3004
    101 BBC One South E |  20171112014000  |  3005
    101 BBC One South E |  20171112060000  |  3006
    101 BBC One South E |  20171112090000  |  3007
    101 BBC One South E |  20171112102000  |  3008
    101 BBC One South E |  20171112123000  |  3009
    101 BBC One South E |  20171112133000  |  3010
    102 BBC Two | 20171111232500  |  3011
    102 BBC Two | 20171112010000  |  3012
    102 BBC Two | 20171112023500  |  3013
    102 BBC Two | 20171112060500  |  3014
    102 BBC Two | 20171112065000  |  3015
    102 BBC Two | 20171112073500  |  3016
    102 BBC Two | 20171112083500  |  3017
    102 BBC Two | 20171112093000  |  3018
    102 BBC Two | 20171112130000  |  3019
    102 BBC Two | 20171112140000  |  3020
Here is the program_id data:

Output:
    3001
    3002
    3003
    3004
    3005
    3006
    3007
    3008
    3009
    3010
    3011
    3012
    3013
    3014
    3015
    3016
    3017
    3018
    3019
    3020
    3021
    3022
    3023
    3024
    3025
    3026
    3027
    3028
    3029
    3030
    3031
    3032
    3033
    3034
    3035
    3036
    3037
    3038
    3039
    3040
    3041
    3042
    3043
    3044
    3045
    3046
    3047
    3048
    3049
    3050
    3051
    3052
    3053
    3054
    3055
    3056
    3057
    3058
    3059
    3060
    3061
    3062
    3063
    3064
    3065
    3066
    3067
    3068
    3069
    3070
Here is the program_id_list list:

Output:
    ['3001', '3002', '3003', '3004', '3005', '3006', '3007', '3008', '3009', 
    '3010', '3011', '3012', '3013', '3014', '3015', '3016', '3017', '3018', 
    '3019', '3020', '3021', '3022', '3023', '3024', '3025', '3026', '3027', 
    '3028', '3029', '3030', '3031', '3032', '3033', '3034', '3035', '3036', 
    '3037', '3038', '3039', '3040', '3041', '3042', '3043', '3044', '3045', 
    '3046', '3047', '3048', '3049', '3050', '3051', '3052', '3053', '3054', 
    '3055', '3056', '3057', '3058', '3059', '3060', '3061', '3062', '3063', 
    '3064', '3065', '3066', '3067', '3068', '3069', '3070']
Here is the nextday_list list:

Output:
    ['20171111224000', '20171111230000', '20171111233000', '20171112000000', 
    '20171112013500', '20171112014000', '20171112060000', '20171112090000', 
    '20171112102000', '20171112123000', '20171111224000', '20171111230000', 
    '20171111233000', '20171112000000', '20171112013500', '20171112014000', 
    '20171112060000', '20171112090000', '20171112102000', '20171112123000', 
    '20171111224000', '20171111230000', '20171111233000', '20171112000000',         
    '20171112013500', '20171112014000', '20171112060000', '20171112090000', 
    '20171112102000', '20171112123000', '20171111224000', '20171111230000', 
    '20171111233000', '20171112000000', '20171112013500', '20171112014000', 
    '20171112060000', '20171112090000', '20171112102000', '20171112123000', 
    '20171111224000', '20171111230000', '20171111233000', '20171112000000', 
    '20171112013500', '20171112014000', '20171112060000', '20171112090000', 
    '20171112102000', '20171112123000', '20171111224000', '20171111230000', 
    '20171111233000', '20171112000000', '20171112013500', '20171112014000', 
    '20171112060000', '20171112090000', '20171112102000', '20171112123000', 
    '20171111224000', '20171111230000', '20171111233000', '20171112000000', 
    '20171112013500', '20171112014000', '20171112060000', '20171112090000', 
    '20171112102000', '20171112123000']
What I'm expecting to do is I want to input the 10 program ids for each channel in the same row as the next_day data which 10 program ids goes for bbc one, the other 10 goes for bbc two and so on. 

Here is for example: Find the next_day data 20171111224000, then find them in the database to match it then in the same row I want to input 3001, then in the next row the next_day data show 20171111230000 I want to put 3002, then 3003, 3004, 3005...etc until to 3010 for each row. The same thing for the other channels.

Can you please show me an example how I could input the program id 3001 to 3010 to input them in the same row as the next_day data for bbc one, 3011 to 3020 for bbc two...etc??

Larz60+

Nov-11-2017, 12:50 AM
you are running the inner loop with same data 10 times for each iteration of outer loop, why?

chris0147

Nov-11-2017, 01:00 AM
(Nov-11-2017, 12:50 AM)Larz60+ Wrote: [ -> ]you are running the inner loop with same data 10 times for each iteration of outer loop, why?

well I dont want to run the inner loop with same data 10 times.

When I try this:

     if value == program_id:
        cur.execute("UPDATE programs SET program_id=? WHERE start_date=?",(value, next_day))
It will insert the data into the database like 11 times:

Output:
    channel             |   start_date     |  program_id
    101 BBC One South E |  20171111230000  |  3061
    101 BBC One South E |  20171111233000  |  3062
    101 BBC One South E |  20171112000000  |  3063
    101 BBC One South E |  20171112013500  |  3064
    101 BBC One South E |  20171112014000  |  3065
    101 BBC One South E |  20171112060000  |  3066
    101 BBC One South E |  20171112090000  |  3067
    101 BBC One South E |  20171112102000  |  3068
    101 BBC One South E |  20171112123000  |  3069
    101 BBC One South E |  20171112133000  |  3070
    101 BBC One South E |  20171112165500  |  3060
    102 BBC Two         | 20171111232500   |
    102 BBC Two         | 20171112010000   |  
    102 BBC Two         | 20171112023500   |  
    102 BBC Two         | 20171112060500   |  
    102 BBC Two         | 20171112065000   |  
    102 BBC Two         | 20171112073500   |  
    102 BBC Two         | 20171112083500   |  
    102 BBC Two         | 20171112093000   |  
    102 BBC Two         | 20171112130000   |  
    102 BBC Two         | 20171112140000   | 
Do you know how I can insert the 3001 to 3010 in the same rows as the next_day data??

Larz60+

Nov-11-2017, 02:22 AM
the following statement:
for i in range(0,10):
does what it's told to do, run code that's part of the loop 11 times with no change of data.

chris0147

Nov-11-2017, 04:38 PM
(Nov-11-2017, 02:22 AM)Larz60+ Wrote: [ -> ]the following statement:
for i in range(0,10):
does what it's told to do, run code that's part of the loop 11 times with no change of data.

I have been using the code outside the for loop but it doesn't make any difference.

You should have post the code that will update the data into the database in the same rows as the next_day data for per channel.

Larz60+

Nov-11-2017, 07:03 PM
Quote:You should have post the code that will update the data into the database in the same rows as the
next_day
data for per channel. 
we do sometimes. However the intent of the forum is to point you in the right direction.
If you post code that is complete enough to run the problem area, you are more likely to get
code examples back.
Once you are in an inner loop, unless the code is threaded, (even then special processing is needed)
the rest of the program will not run until the loop finishes. Therefore, since all data outside of the loop
cannot be changed except from within the loop.
Python Forum > Python Coding > General Coding Help > Problem with updata the data into the database as it went into the wrong rows