CODEWITHSUNDEEP
mysqlpython-3.xapi
user19441790
user19441790

Reputation: 11

Inserting data from a for loop in database

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

Upvotes: 0

Views: 278

Answers (1)

Barmar
Barmar

Reputation: 782166

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

Upvotes: 1

Related Questions