CODEWITHSUNDEEP
pythonmysql
LearningSlowly
LearningSlowly

Reputation: 9451

Python MySQL Foreign Key Error

I am attempting to build a MySQL database which will hold shortest route information I get back from a Google API.

The code for creating tables is shown below. As can be seen, the request_no is generated by MySQL using auto-increment. This request_no is the primary key for the master table which holds the overall results from the API request. The leg table, holds the constitutive parts of the API request. The request_no must relate to its respective leg_no parts, as seen in the second part, the leg_data table. 

TABLES={}
TABLES['master'] = (
    "CREATE TABLE `master` ("
    "  `request_no` int AUTO_INCREMENT,"
    "  `date_time` datetime NOT NULL,"
    "  `distance` int NOT NULL,"
    "  `duration` int NOT NULL,"
    "  `duration_in_traffic` int NOT NULL,"
    "  `Orig_lat` double NOT NULL,"
    "  `Orig_lng` double NOT NULL,"
    "  `Orig_address` longtext ,"
    "  `Dest_lat` double NOT NULL,"
    "  `Dest_lng` double NOT NULL,"
    "  `Dest_address` longtext ,"
    "  PRIMARY KEY (`request_no`)"
    ") ENGINE=InnoDB")

TABLES['leg_data'] = (
    "CREATE TABLE `leg_data` ("
    "  `leg_no` int NOT NULL AUTO_INCREMENT,"
    "  `request_no` int NOT NULL,"
    "  `leg_distance` int ,"
    "  `leg_duration` int ,"
    "  `leg_travel_mode` longtext ,"
    "  `leg_Orig_lat` double ,"
    "  `leg_Orig_lng` double ,"
    "  `leg_Dest_lat` double ,"
    "  `leg_Dest_lng` double ,"
    "  `leg_html_inst` longtext ,"
    "  `leg_polyline` longtext ,"
    "  PRIMARY KEY (`leg_no`),"
    "  CONSTRAINT `leg_data_ibfk` FOREIGN KEY (`request_no`)"
    "     REFERENCES `master` (`request_no`)"
    ") ENGINE=InnoDB")

I then insert values which are parsed from the API JSON response.

EDITED, now using request_no: cursor.lastrowid

add_overall_data = ("INSERT INTO master" 
    "(date_time, distance, duration, duration_in_traffic, Orig_lat, Orig_lng, Orig_address, Dest_lat, Dest_lng, Dest_address)"
    "VALUES (%(date_time)s, %(distance)s, %(duration)s, %(duration_in_traffic)s, %(Orig_lat)s, %(Orig_lng)s, %(Orig_address)s, %(Dest_lat)s, %(Dest_lng)s, %(Dest_address)s)")

add_leg_data = ("INSERT INTO leg_data"
    "(request_no, leg_distance, leg_duration, leg_Orig_lat, leg_Orig_lng, leg_Dest_lat, leg_Dest_lng, leg_html_inst, leg_polyline, leg_travel_mode)" 
    "VALUES (%(request_no)s, %(leg_distance)s, %(leg_duration)s, %(leg_Orig_lat)s, %(leg_Orig_lng)s, %(leg_Dest_lat)s, %(leg_Dest_lng)s, %(leg_html_inst)s, %(leg_polyline)s, %(leg_travel_mode)s)")

for result in results:
    if result["status"] == "OK":
        for leg in result['routes'][0]['legs']:
            params = {
            "date_time": leg['_date_time'],
            "distance": leg['distance']['value'],
            "duration": leg['duration']['value'],
            "duration_in_traffic": leg['duration_in_traffic']['value'],
            "Orig_lat": leg['start_location']['lat'],
            "Orig_lng": leg['start_location']['lng'],
            "Orig_address": leg['start_address'],
            "Dest_lat": leg['end_location']['lat'],
            "Dest_lng": leg['end_location']['lng'],
            "Dest_address": leg['end_address']
            }
            cursor.execute(add_overall_data, params)
        for steps in result['routes'][0]['legs'][0]['steps']:
            params = {
            "request_no": cursor.lastrowid,
            "leg_distance": steps['distance']['value'],
            "leg_duration": steps['duration']['value'],
            "leg_Orig_lat": steps['start_location']['lat'],
            "leg_Orig_lng": steps['start_location']['lng'],
            "leg_Dest_lat": steps['end_location']['lat'],
            "leg_Dest_lng": steps['end_location']['lng'],
            "leg_html_inst": steps['html_instructions'],
            "leg_polyline": steps['polyline']['points'],
            "leg_travel_mode": steps['travel_mode']
            } 
            cursor.execute(add_leg_data, params)

I am now able to populate the request_no field in the leg_data table. Albeit with incorrect values. What cursor. argument could I use to associate the individual legs with their respective master id (request_no)?

Upvotes: 1

Views: 751

Answers (1)

dursk
dursk

Reputation: 4445

When writing to the leg_data table, you need to provide a request_no which corresponds to a value under the request_no column in the master table.

UPDATE: Unfortunately 'date_time' is not unique, as there can be up to 10 records received from the API per second. The syntax below does indeed work. The combination of Orig_lat,Orig_lng,Dest_lat and Dest_lng will be unique, despite its clunkiness. How could I change the 'WHERE' statement below to reference all 4 parameters above?

for result in results:
    if result["status"] == "OK":
        for leg in result['routes'][0]['legs']:
            params = {
            "date_time": leg['_date_time'],
            "distance": leg['distance']['value'],
            "duration": leg['duration']['value'],
            "duration_in_traffic": leg['duration_in_traffic']['value'],
            "Orig_lat": leg['start_location']['lat'],
            "Orig_lng": leg['start_location']['lng'],
            "Orig_address": leg['start_address'],
            "Dest_lat": leg['end_location']['lat'],
            "Dest_lng": leg['end_location']['lng'],
            "Dest_address": leg['end_address']
            }
            cursor.execute(add_overall_data, params)
            cursor.execute(
                'SELECT request_no FROM master WHERE date_time = {}'.format(
                    leg['_date_time']
                )
            )
            request_no = cursor.fetchOne()[0]
        for steps in result['routes'][0]['legs'][0]['steps']:
            params = {
            "request_no": request_no,
            "leg_distance": steps['distance']['value'],
            "leg_duration": steps['duration']['value'],
            "leg_Orig_lat": steps['start_location']['lat'],
            "leg_Orig_lng": steps['start_location']['lng'],
            "leg_Dest_lat": steps['end_location']['lat'],
            "leg_Dest_lng": steps['end_location']['lng'],
            "leg_html_inst": steps['html_instructions'],
            "leg_polyline": steps['polyline']['points'],
            "leg_travel_mode": steps['travel_mode']
            } 
            cursor.execute(add_leg_data, params)

UPDATE 2: If date_time isn't sufficient to uniquely identify a record, then you can add as many columns as you'd like to the query:

cursor.execute(
    'SELECT request_no FROM master WHERE col1 = {} AND col2 = {} AND ...'.format(
        val1, val2, ...
    )
)

Upvotes: 2

Related Questions