CODEWITHSUNDEEP
pythonpostgresql
Alex
Alex

Reputation: 87

How to avoid "fake" ids creation in db?

I have python code which push new record to db to column info.

   try:            
        db.add_new_record(info)
    except Exception as e:
        log.error(e)            
        db.db.rollback()
        continue

I use standard sql query

INSERT INTO mydb (info, desc) VALUES ('value1', 'value2')

python:

import psycopg2
def add_new_record(self, info):
    info['table'] = self.table
    query = "INSERT INTO {table} (c1, c2)  VALUES ('{val1}', '{val2}')".format(**self.make_dct(info))
    self.make_execute(query)
    self.db_commit()
    return True

Sometimes I see exception

 exception: duplicate key value violates unique constraint "mydb_info_key"

It's normal.

I count rows in db: 25000 records. But my last ids start with 60000+.

Duplicate exception records new id? How to avoid this?

Upvotes: 1

Views: 109

Answers (1)

Joran Beasley
Joran Beasley

Reputation: 114008

there is another table mydb_seq that tells sql what the next index to use is(you can look at what it is with SELECT * FROM mydb_seq;)

somewhere else you are actively setting an ID to a higher value than this pointer, so sometimes this pointer points at an index that already exists in your table

you can identify where using the following logic

first find the pointer for the next_id

SELECT * FROM mydb_seq;

then query your table for anything with a bigger id

SELECT id FROM mydb WHERE id > $next_id;  // replace $next_id... with the value from above

as an aside you should really really really use the cursor.execute variable insertion ... what you are doing now is madness that will get you sql injected

qry = "INSERT INTO {table} (field1,field2,field3) VALUES (%s,%s,%s)".format(table="my_db")
cursor.execute(query,(var1,var2,var3))

Upvotes: 2

Related Questions