Python Forum
1062 mysql ( sql alchemy) duplication error customisation
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
1062 mysql ( sql alchemy) duplication error customisation
#1
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.
Reply
#2
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
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
Reply
#4
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/...icate.html
Oh, I didn't realise you use sqlalchemy....
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#5
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 ... ??
Reply
#6
(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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#7
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
Reply
#8
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/cor...extual-sql
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Mysql and mysql.connector error lostintime 2 612 Oct-03-2023, 10:25 PM
Last Post: lostintime
  SQL Alchemy help to extract sql data into csv files mg24 1 1,679 Sep-30-2022, 04:43 PM
Last Post: Larz60+
  Mysql Syntax error in pymysql ilknurg 4 2,291 May-18-2022, 06:50 AM
Last Post: ibreeden
Question I am struggling with basic json library + sql alchemy w/ mariadb-connector json ->sql BrandonKastning 0 1,483 Mar-04-2022, 09:26 PM
Last Post: BrandonKastning
  Mysql error message: Lost connection to MySQL server during query tomtom 6 15,684 Feb-09-2022, 09:55 AM
Last Post: ibreeden
  mySQL Database error not resolving. cybertooth 2 3,119 Aug-30-2021, 05:45 PM
Last Post: ibreeden
  MYSQL.CONNECTOR ERROR DB1 8 3,776 Jul-23-2021, 03:31 AM
Last Post: DB1
  pool mysql error - not catch by try\except? korenron 1 2,101 Jul-05-2021, 11:26 AM
Last Post: ibreeden
  UnicodeDecodeError: 'utf-8' codec can't decode byte 0x92 error from Mysql call AkaAndrew123 1 3,386 Apr-28-2021, 08:16 AM
Last Post: AkaAndrew123
  Error in Mysql Client when upgrading Django Abi 0 1,851 Sep-21-2020, 06:11 AM
Last Post: Abi

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020