How to use INSERT statement with unique primary key values (1 Viewer)

sbrown106

Member
Local time
Today, 21:32
Joined
Feb 6, 2021
Messages
77
Hello everybody,

I have a script using Python that uses the INSERT statement to input data into a table, using something like the test statement below

cursor.execute(' INSERT INTO tbl_test (fld_PK, fld_value1, fld_value2) VALUES (?,?,?)'),(ID, name, address))

This wont work if I have a duplicate primary key (fld_PK) and the program crashes.

I did a test and incremented the primary key value by 0.1 and set the integer type to double and ran the program again and it inserted the new record directly after the original one ( I'm guessing that you already knew this! I was just expecting to stick at the end of the table).

So if a duplicate key exists is there I can INSERT at this position but REPLACE? as I will only need to replace a few fields. Ive tried INSERT IGNORE but apparently that's not supported anymore - the INSERT IGNORE statement would have been really useful as it would have provided a warning as well. I don't want to change the PK to accept duplicates in MS Access either - has anybody suggestions as to how I might get this to work please? - there's probably a better way to do this

any help appreciated
 

Saphirah

Active member
Local time
Today, 22:32
Joined
Apr 5, 2020
Messages
163
The simplest way to do this is to first remove the old record if exists:

Python:
cursor.execute('DELETE * FROM tbl_test WHERE fld_PK = ' + ID)

And then run your INSERT INTO Statement

Python:
 cursor.execute(' INSERT INTO tbl_test (fld_PK, fld_value1, fld_value2) VALUES (?,?,?)'),(ID, name, address))
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:32
Joined
Jul 9, 2003
Messages
16,245
I think it would be safer to check to see if the record already existed, then either ignore it and/or tell the user.
 

Saphirah

Active member
Local time
Today, 22:32
Joined
Apr 5, 2020
Messages
163
I think it would be safer to check to see if the record already existed, then either ignore it and/or tell the user.
That's the behaviour he already has :D (At least the ignoring part).

If you want to tell the user that an entry already exist, you might want to execute a select query in python first, and then check if it returned a valid entry.
But i do not know how to return data from a query in python, so i can not help you with that :D
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:32
Joined
Feb 19, 2002
Messages
42,976
I'm confused. If you are attempting to generate a unique PK, how is it possible to match and so update, an existing record?

Is there some problem with using an Identity column as the unique ID?
 

sbrown106

Member
Local time
Today, 21:32
Joined
Feb 6, 2021
Messages
77
The simplest way to do this is to first remove the old record if exists:

Python:
cursor.execute('DELETE * FROM tbl_test WHERE fld_PK = ' + ID)

And then run your INSERT INTO Statement

Python:
 cursor.execute(' INSERT INTO tbl_test (fld_PK, fld_value1, fld_value2) VALUES (?,?,?)'),(ID, name, address))

I'm confused. If you are attempting to generate a unique PK, how is it possible to match and so update, an existing record?

Is there some problem with using an Identity column as the unique ID?
Thanks, Sorry I'm just catching up with these replies. Basically I have 2 tables (table 1 and table 2) that accept data from 2 different sources at different times. So for example table 1 might have patient data and table 2 medical information. The first table uses a unique identifier created by Access for the patient (its primary key) but also contains a field containing a reference (REF) from the same data source. The second table uses a different data source, which can arrive much later, but contains the same reference (REF) and this needs to be used as its PK, as this reference is always unique, and its this reference which is used across other tables, financials etc. So basically when the first table gets populated its primary key is created by Access and one of the fields contains a value REF, this REF value is then inserted into the second table as its PK. So when the second set of data comes in for the second table, it may already contain the same REF value as its PK if its come from the first dataset. Sorry if its confusing, its confusing me - I didnt design this database! So what I would like to do is - if this REF value already exists in the second table, is to skip over it (with a warning ideally), and just input the few fields that come with the second table. I would be grateful for any advice/suggestions - thanks again.
 

sbrown106

Member
Local time
Today, 21:32
Joined
Feb 6, 2021
Messages
77
The simplest way to do this is to first remove the old record if exists:

Python:
cursor.execute('DELETE * FROM tbl_test WHERE fld_PK = ' + ID)

And then run your INSERT INTO Statement

Python:
 cursor.execute(' INSERT INTO tbl_test (fld_PK, fld_value1, fld_value2) VALUES (?,?,?)'),(ID, name, address))
Thanks Saphirah for that reply, the problem that I have is that there are some fields I may need to keep, so if a delete all the record and use replace I will lose all that data. But could I just delete the PK and then use the INSERT statement ? would this still insert in the same record?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:32
Joined
Feb 19, 2002
Messages
42,976
Since the other application is the master, the simplest solution is to delete tbl1 and import the replacement records.

You can't have two sources for data because you would never be able to keep them in sync. So, just accept the situation that your database is not the master and replace tbl2 in its entirety

Does tbl1 just accumulate rows with each import?

What is the point of the Access application? What is it doing that the original application cannot do?

Is there any chance that the Access application can become the master and the users add/change data in it directly rather than using the import/replace method you are currently using..
 

Saphirah

Active member
Local time
Today, 22:32
Joined
Apr 5, 2020
Messages
163
Thanks Saphirah for that reply, the problem that I have is that there are some fields I may need to keep, so if a delete all the record and use replace I will lose all that data. But could I just delete the PK and then use the INSERT statement ? would this still insert in the same record?
The delete statement i wrote does not delete every entry. It just deletes the entry with the same ID. So only one.
A replace is basically the same as "delete the old entry" and then "put in the new one".

If there is no entry with that id, then the delete statement won't actually do anything!

I assumed that you only want to insert one row with your command, because you wrote an insert into statement. Correct me if i am wrong :D

I do not know what you mean by "you want to keep some fields". So you basically only want to update a few fields, and if the id does not exist create a new entry?

Then i would suggest the following:

Code:
try:
     cursor.execute(' INSERT INTO tbl_test (fld_PK, fld_value1, fld_value2) VALUES (?,?,?)'),(ID, name, address))
except Exception as e:
    cursor.execute(' UPDATE tbl_test SET fld_value1 = ?, fld_value2 = ?'),(name, address) WHERE fld_PK = ' + ID)

Would be better if you konw the exact error that occurs and if you catch that one, but for now this should be fine.
 
Last edited:

Saphirah

Active member
Local time
Today, 22:32
Joined
Apr 5, 2020
Messages
163
Are ID, name, adress single values or arrays?
 

sbrown106

Member
Local time
Today, 21:32
Joined
Feb 6, 2021
Messages
77
Since the other application is the master, the simplest solution is to delete tbl1 and import the replacement records.

You can't have two sources for data because you would never be able to keep them in sync. So, just accept the situation that your database is not the master and replace tbl2 in its entirety

Does tbl1 just accumulate rows with each import?

What is the point of the Access application? What is it doing that the original application cannot do?

Is there any chance that the Access application can become the master and the users add/change data in it directly rather than using the import/replace method you are currently using..
Thanks Pat - I think the database needs redesigning, table1 just accumulates rows when the user enter the name address etc for a person and this gets assigned a PK for this person. Once this person has been created with this PK a certain action can occur. This table then looks to see if this REF value it has been given exists in the second table before proceeding. So this entry controls the execution. The second table with its PK is a request for information that table1 uses and takes with it when its PK has been created.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:32
Joined
Feb 19, 2002
Messages
42,976
I don't believe the problem definition is complete.

Where is the data coming from? Where is it going? What happens to it in your app? What triggers the action? Does tbl1 just simply grow in perpetuity? Is it possible for rows in tbl1 to not have any matching parent record in tbl2?

Also, the "parent" table is generally though of as tbl1 and the "child" table (the one containing the FK) is thought of as tbl2 so even your relationship is probably backwards.

Going back to your original question, I don't understand why you are trying to assign a unique ID the way you are. If the input file has no natural PK, then let Access assign an autonumber. If you are trying to generate a PK based on a value in the other table, that is simply not possible unless there already exists some way to link the two tables.
 

Users who are viewing this thread

Top Bottom