CODEWITHSUNDEEP
pythonpostgresql
parker
parker

Reputation: 97

Postgres: autogenerate primary key in postgres using python

cursor.execute('UPDATE emp SET name = %(name)s',{"name": name} where ?)

I don't understand how to get primary key of a particular record. I have some N number of records present in DB. I want to access those record & manipulate. Through SELECT query i got all records but i want to update all those records accordingly Can someone lend a helping hand? Thanks in Advance!

Table structure:

ID  CustomerName    ContactName 
1   Alfreds         Futterkiste     
2   Ana             Trujillo

Here ID is auto genearted by system in postgres. I am accessing CustomerName of two record & updating. So here when i am updating those record the last updated is overwrtited in first record also.

Here i want to set some condition so that When executing update query according to my record.

After Table structure:

ID  CustomerName    ContactName 
1   xyz         Futterkiste     
2   xyz             Trujillo

Here I want to set first record as 'abc' 2nd record as 'xyz'

Note: It ll done using PK. But i dont know how to get that PK

Upvotes: 1

Views: 589

Answers (2)

nagylzs
nagylzs

Reputation: 4178

Maybe you won't like this, but you should not use autogenerated keys in general. The only exception is when you want to insert some rows and do not do anything else with them. The proper solution is this:

  • Create a sequencefor your table. http://www.postgresql.org/docs/9.4/static/sql-createsequence.html
  • Whenever you need to insert a new row, get the next value from the generator (using select nextval('generator_name')). This way you will know the ID before you create the row.
  • Then insert your row by specifying the id value explicitly.

For the updates:

  • You can create unique constraints (or unique indexes) on sets of coulmns that are known to be unique
  • But you should identify the rows with the identifiers internally.
  • When referring records in other tables, use the identifiers, and create foreign key constraints. (Not always, but usually this is good practice.)

Now, when you need to updatea row (for example: a customer) then you should already know which customer needs to be modified. Because all records are identified by the primary key id, you should already know the id for that row. If you don't know it, but you have an unique index on a set of fields, then you can try to get the id. For example:

select id from emp where CustomerName='abc'  -- but only if you have a unique constraing on CustomerName!

In general, if you want to update a single row, then you should NEVER update this way:

update emp set CustomerName='newname' where CustomerName='abc'

even if you have an unique constraint on CustomerName. The explanation is not easy, and won't fit here. But think about this: you may be sending changes in a transaction block, and there can be many opened transactions at the same time...

Of course, it is fine to update rows, if you intention is to update all rows that satisfy your condition.

Upvotes: 1

Nhor
Nhor

Reputation: 3940

You mean you want to use UPDATE SQL command with WHERE statement:

cursor.execute("UPDATE emp SET CustomerName='abc' WHERE ID=1")
cursor.execute("UPDATE emp SET CustomerName='xyz' WHERE ID=2")

This way you will UPDATE rows with specific IDs.

Upvotes: 1

Related Questions