I am getting this error message mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry '13' for key 'order_details.PRIMARY' when I try to add new order and its details using python and SQL query. Complete Error message:

Here is my code:

from datetime import datetime
from sql_connection import get_sql_connection

def insert_order(connection, order):
    cursor = connection.cursor()

    order_query = ("INSERT INTO orders "
             "(customer_name, total, datetime)"
             "VALUES (%s, %s, %s)")
    order_data = (order['customer_name'], order['grand_total'], datetime.now())

    cursor.execute(order_query, order_data)
    order_id = cursor.lastrowid

    order_details_query = ("INSERT INTO order_details "
                           "(order_id, product_id, quantity, total_price)"
                           "VALUES (%s, %s, %s, %s)")

    order_details_data = []
    for order_detail_record in order['order_details']:
        order_details_data.append([
            order_id,
            int(order_detail_record['product_id']),
            float(order_detail_record['quantity']),
            float(order_detail_record['total_price'])
        ])
    cursor.executemany(order_details_query, order_details_data)

    connection.commit()

    return order_id

if __name__ == '__main__':
    connection = get_sql_connection()
    print(insert_order(connection, {
        'customer_name': 'dhaval',
        'grand_total': '500',
        'order_details': [
            {
                'product_id': 3,
                'quantity': 2,
                'total_price': 50
            },
            {
                'product_id': 4,
                'quantity': 1,
                'total_price': 30
            }
        ]
    }))

orders

order_details

order_details foreign key

CodePudding user response：

Error is normal as in table order_details, the primary key is only order_id

And you provide twice the same order_id for each row you insert

for order_detail_record in order['order_details']:
    order_details_data.append([
        order_id, # <<  <<  <<  <<  <<  <<  <<  <<  <<  <<  <<  
        int(order_detail_record['product_id']),
        float(order_detail_record['quantity']),
        float(order_detail_record['total_price'])
    ])

Regarding your code and DB, I'd say that table order_details should have both order_id and product_id as primary key

CodePudding user response：

Change the order details To

CREATE TABLE order_Details
(od_id BIGINT AutO_INCREMENT PRIMARY KEY,
order_id int,
product_id int,
qualntitiy double,
INDEX (order_id),
total_price Double,
    FOREIGN KEY (order_id)
        REFERENCES `orders`(order_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
        );

This would allow many orde_ids

But

1. order_id should be bigint int is too small
2. tota_price should be DECIMAL(10,2)