Inserting data from a for loop in database

Question

I am fetching data from an APi, extracting part of it. The data comes in nested dictionaries and lists and I used a nested for loop to extract variables. I want to insert it in mysql db, not sure how to do so, as in some of the columns I will receive a different number of values to be stored. For example, cars could be 1,2,3 or 4. All vehicle_id fetched should be inserted into a column all_vehicles, I am not sure how to do this either.

    datetime_received= datetime.now()
    car_dealer_id=11
    int_id = 8
    dealer_name ='XXX'
    for car in cars: 
         
        code=car['Code']
        start_date=car['RDate']
        end_date=car['RDate']
        
        for portion in car['Consists']['Portions']:
            location= portion['Location']
            
            for consist in portion['Consist']:
               ext_id = consist['ExtId']
                
                for vehicle in consist['Vehicles']:
                    vehicle_id= vehicle['Id']
    sql = """
    INSERT INTO table
    (`datetime_received`, `car_dealer_id` , `ind_id`, `dealer_name`,`code`,`start_date`, `start_time`, `end_date`, `location`, `ext_id`, `all_vehilces`)
    
    VALUES ('%s', '%s',  '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')"""
    cursor.executemany(sql, data)
    connection.commit()
    connection.close()

Data:

cars = {
      "Consists": {
        "Portions": [
          {
            "Consist": [
              {
                "ext_id": "755411",
                "Position": "0",
                "Vehicles": [
                  {
                    "Id": "92",
                    "Position": "1"
                  },
                  {
                    "Id": "921",
                    "Position": "2"
                  },
                  {
                    "Id": "932",
                    "Position": "3"
                  },
                  {
                    "Id": "34",
                    "Position": "4"
                  },
                  {
                    "Id": "92",
                    "Position": "5"
                  }
                ]
              }
            ],
            "Location": "ATA"
            
          }
        ],
        "Updated": "2022-07-21T04:25:08.0000000+01:00"
      },
      "Code": "5`enter code here`75",
      "RDate": "2022-07-21T08:25:00.0000000+01:00",
      "RunDate": "2022-07-21T00:00:00.0000000+01:00",
      
    }

EDITED: Thanks to Barmar, I managed to insert the values. I have one final value to insert in the data[]. Based on the ext_id value I get, I have a function returning the corresponding my_system_id. I want to insert the my_system_ids as well, but I am not calling the function from the correct place and it is not being inserted into the db table.

Here is the function:

     def get_my_system_id(ext_id):
           
            cursor=db_conn.cursor()    
            sql=("""SELECT my_system_id FROM table 
                                   WHERE ext_id= %s""")
            data=(ext_id,)
            cursor.execute(sql,data)
            id_row =cursor.fetchone()
            if_row is not None: 
               my_id=id_row[0]
               return(my_id)
            else:
               return null

Please add a small sample of `cars` and the expected table contents. — Barmar, Jul 23 '22 at 15:13
Is `all_vehicles` supposed to be a list of values? It's not a good idea to put lists in SQL columns, you should normalize the tables. See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Barmar, Jul 23 '22 at 15:13
All variables in above and inside of the loop should be inserted as data, I was not sure how to do so. I will add json cars in original post — user19441790, Jul 23 '22 at 15:23
it is a list of vehicle ids, yes. I will read the thread, thank you — user19441790, Jul 23 '22 at 15:25
Isn't `cars` supposed to be a list? You have it just as a single car. — Barmar, Jul 23 '22 at 15:29
Do you really have separate `start_date` and `start_time` columns, but just `end_date` with no `end_time`? — Barmar, Jul 23 '22 at 15:34
Why is `car['RDate']` used for both `start_date` and `end_date`? Maybe one of them should be `car['RunDate']` — Barmar, Jul 23 '22 at 15:35
Cars is a list yes, just on this occasion the api returned only one result, it sends updates on regular intervals. Thank you for pointing out the typo, start and end dates are the same, perhaps, taking the data from ['RDate'] — user19441790, Jul 23 '22 at 20:15
You have a typo: `return(my_system_id)` should be `return my_id` — Barmar, Jul 25 '22 at 13:54
Thank you for pointing that out. I amended it to return (my_id) and I call it like so: my_system_id=get_my_id(ext_id). I tried also my_system_id=get_my_id(ext_id,) but it is still not inserting the value in my_system_id column in db. — user19441790, Jul 25 '22 at 14:10
Why do you need that column anyway? It seems redundant since you can always get the system ID by joining with the other table. — Barmar, Jul 25 '22 at 14:16

Barmar · Accepted Answer · 2022-07-25T14:59:20.537

1

Use ','.join() to combine all the vehicle IDs into a comma-delimited list.

In the prepared statement, %s should not be quoted. You also only had 10 of them, but you're inserting into 11 columns.

With th edit, add a call to get_my_system_id(ext_id) to the loop, and add that value to the data list.

data = []
for car in cars: 
    code=car['Code']
    start_date, end_date = car['RDate'].split('T')
    end_date=car['RDate']

    for portion in car['Consists']['Portions']:
        location= portion['Location']
        for consist in portion['Consist']:
            ext_id = consist['ExtId']
            vehicle_ids = ','.join(v['id'] for v in consist['Vehicles'])
            system_id = get_my_system_id(ext_id)
            if not system_id:
                print(f"No system ID found for ext_id = {ext_id}, skipping")
                continue
            data.append((datetime_received, car_dealer_id, int_id, dealer_name, code, start_date, start_time, end_date, location, ext_id, system_id, vehicle_ids))

    sql = """
    INSERT INTO table
    (`datetime_received`, `car_dealer_id` , `ind_id`, `dealer_name`,`code`,`start_date`, `start_time`, `end_date`, `location`, `ext_id`, my_system_id, `all_vehicles`)
    
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
    cursor.executemany(sql, data)
    connection.commit()
    connection.close()

edited Jul 25 '22 at 14:59

answered Jul 23 '22 at 15:39

Barmar

741,623
53
500
612

@Thank you for helping me with my learning journey. When I run the code, I am unable to insert the data. Each time I run the code I have different errors on this line: cursor.executemany(sql, data). First the encoding was wrong, but when I printed it, it is utf-8, now I have another : not enough parameters in the sql statement. The number of the placeholders and values match, so I am not sure what is causing the problem. Can you please explain what is the reason for these 2 lines, I am sorry but I could not understand. start_date, end_date = car['RDate'].split('T') end_date=car['RDate'] – user19441790 Jul 24 '22 at 06:04
`%s` should not be inside quotes, that makes it literal instead of a placeholder. – Barmar Jul 24 '22 at 14:11
Thank you again. I edited as per your advise and found that I had appended 1 var less in the data list which was causing the error. I have edited my question, I am trying to insert one more value, using a function. – user19441790 Jul 25 '22 at 10:51
I updated the answer to show how to call the function and include the system ID in the `INSERT` – Barmar Jul 25 '22 at 14:16
Thank you so much. I am still unable to insert the data, got the error 1265 (01000): Data truncated for column 'my_system_id' at row 1. I am sure I have some stupid typo or other error, I have been staring at the code for 2 days and still missing it .. – user19441790 Jul 25 '22 at 14:38
You have a bunch of typos in `get_my_system_id()`, like `if_row!=None` should be `if id_row is not None:` or just `if id_row:`. Make sure you're not returning `None` from the function. Also, what should be inserted if it does return `None`? – Barmar Jul 25 '22 at 14:51
Thanks, will amend now, if it does not return, ie if I don't have this id yet in the other table, I will add this to my log file after that. I edited it like so: if id_row is not None: my_id=id_row[0] return(my_id) else: return null – user19441790 Jul 25 '22 at 15:07
I've added code like that to my answer. My code just prints the error, you can change it to log it. – Barmar Jul 25 '22 at 15:09
I can not thank you enough for all your help. I have also came across some of your answers to other members that also helped me with other issues. – user19441790 Jul 25 '22 at 15:28
ohh. another issue : Exception has occurred: InterfaceError Failed executing the operation; Python type function cannot be converted cursor.executemany(sql, data_to_insert) During handling of the above exception, another exception occurred: Now it is not inserting at all – user19441790 Jul 25 '22 at 15:30
Sounds like you forgot the argument list when calling a function. – Barmar Jul 25 '22 at 15:31
the function is called get_and_insert_data and I was able to call it just like so: get_and_insert_data(). I have not changed anything else, I am not inserting any data now. – user19441790 Jul 25 '22 at 15:52
Do you understand the error message? It means that one of the values you're trying to insert is a function, not an ordinary value. E.g. if you did `system_id = get_my_system_id` instead of `system_id = get_my_system_id(ext_id)`. – Barmar Jul 25 '22 at 15:55
Thank you. I have checked all the code, now it runs, no error, prints No system id found and does not insert any data. I want to insert the rest of the data even if no matching id is found, just leave the column empty. – user19441790 Jul 25 '22 at 16:40
Does the column allow `NULL`? – Barmar Jul 25 '22 at 16:43
not null is not ticked – user19441790 Jul 25 '22 at 16:50
Then what empty value do you want to put there? Change the `if` statement to assign that to `system_id` instead of doing `continue`. – Barmar Jul 25 '22 at 16:52
Thank you. I changed it like so: if not system_id: print(f"No system ID found for external_system_id = {external_system_id}") isystem_id=NULL. It woks as desired, thank you. – user19441790 Jul 25 '22 at 17:05

Inserting data from a for loop in database

1 Answers1