+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: 1062 mysql ( sql alchemy) duplication error customisation (/thread-11195.html)
1062 mysql ( sql alchemy) duplication error customisation - KirkmanJ - Jun-27-2018
With sql alchemy the error message:
IntegrityError: (_mysql_exceptions.IntegrityError) (1062, "Duplicate entry...)
will occur when you try to enter data with identical primary keys as another data row already in the database.
And to my understanding depending on whether you are in Debug 0 or 1 you would receive either a 500 error or a internal server error respectively.
I am looking to input large csv files which could contain duplication, what i would like to do is instead of just throwing up an error.
> Upload all csv rows not within the database.
> Display the rows that were not uploaded.
There i will need to create my own '1062 error' that gets thrown up instead of the normal errors.Is this possible? can i interrupt the usual sql error system and implement my own? if anyone knows the location of my answers I would be grateful.
RE: 1062 mysql ( sql alchemy) duplication error customisation - buran - Jun-27-2018
well, before you go this way:
1. if record already exists (i.e. primary key), would you just ignore, and not update - construct proper SQL statement
2. if records already exists in the DB (before you start to import from the csv file), while not run query in advance and get the existing keys, then ignore these when import from the file? It can be easier instead to run query for keys that ae in the file...
3. if duplication is just in the file - why not dedup, as you explore in other threads
Of course you can cache the exception with try/except, but it presume you insert one record at a time instead of multiple records
I don't think you should create your own exception in any case
RE: 1062 mysql ( sql alchemy) duplication error customisation - KirkmanJ - Jun-27-2018
The csv contains multiple columns (14) , one of which being date / time. If even a figure changes in any column then the row will need to be entered.
If the row is absolutely identical to one already in the table then it should be rejected (ignored), no updating should take place.
Duplication 'should' not occur within the file so there shouldnt be need to dedup that ( however could of necessary :) )
And from what you are saying I should use the database to filter the csv, and such leave just the files NOT in the database. Then upload those files back into the database. If my understanding is correct.
Inserting one at a time is definitely not an option as the csv will contain between 100-500 rows and the database, well 1 csv entered a day, so will grow rapidly
RE: 1062 mysql ( sql alchemy) duplication error customisation - buran - Jun-27-2018
Well if primary key exists you need to update, if both records identical nothing will change in the DB, even if you "update" the record...
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
Oh, I didn't realise you use sqlalchemy....
RE: 1062 mysql ( sql alchemy) duplication error customisation - KirkmanJ - Jun-28-2018
What do you mean by 'Oh, I didn't realise you use sqlalchemy....'
Does Sqlalchemy not support Insert Ignore into ... /
INSERT INTO ... VALUES ... ON DUPLICATE KEY ... ??
RE: 1062 mysql ( sql alchemy) duplication error customisation - buran - Jun-28-2018
(Jun-28-2018, 08:21 AM)KirkmanJ Wrote: What do you mean by 'Oh, I didn't realise you use sqlalchemy....'No, just that I didn't realise... No problem with SQLalchemy
RE: 1062 mysql ( sql alchemy) duplication error customisation - KirkmanJ - Jun-28-2018
So looking into it and my head is starting to spin :/ .
If i had the following query:
query = test_result.(date_time = date_time_store, place_of_test = test_location_store, part_number= part_number_store, serial_number=product_serial_number_store, test_details=test_detail_store, result=test_result_store)and i wanted to implement it with the mysql startment: INSERT INTO ... VALUES ... ON DUPLICATE KEY. With the unqiue keys: Serial number,date time.
What alterations do i need to make to my query line? or do i need to add something to my models file?
I tried using the Ignore statement with 'prefix_with(Ignore)' but could not get round a attributions error
RE: 1062 mysql ( sql alchemy) duplication error customisation - buran - Jun-28-2018
I'm not familiar with sqlalchemy, so I don't know if there is other way, but definitely you can use textual sql
http://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-textual-sql