SQLAlchemy is Throwing an IntegrityError due to a DBSession.add()

Question

On the second time line 121 is called in this script http://paste.pocoo.org/show/520040/, I receive this error message:

*** IntegrityError: (IntegrityError) duplicate key value violates unique constraint "heroes_pkey"
DETAIL:  Key (id)=(14) already exists.
 'INSERT INTO heroes (id, name, description, image_name, default_filename, faction, stat, roles, strength, agility, intelligence, strength_gain, agility_gain, intelligence_gain, min_hp, max_hp, min_mana, max_mana, min_damage, max_damage, armor, movespeed, attack_range, min_attack_animation, max_attack_animation, min_cast_animation, max_cast_animation, base_attack_time, missile_speed, day_site_range, night_site_range, resource_name, "order") VALUES (%(id)s, %(name)s, %(description)s, %(image_name)s, %(default_filename)s, %(faction)s, %(stat)s, %(roles)s, %(strength)s, %(agility)s, %(intelligence)s, %(strength_gain)s, %(agility_gain)s, %(intelligence_gain)s, %(min_hp)s, %(max_hp)s, %(min_mana)s, %(max_mana)s, %(min_damage)s, %(max_damage)s, %(armor)s, %(movespeed)s, %(attack_range)s, %(min_attack_animation)s, %(max_attack_animation)s, %(min_cast_animation)s, %(max_cast_animation)s, %(base_attack_time)s, %(missile_speed)s, %(day_site_range)s, %(night_site_range)s, %(resource_name)s, %(order)s)' {'day_site_range': 1800, 'min_damage': 47, 'intelligence': 18.0, 'agility': 14.0, 'night_site_range': 800, 'min_attack_animation': 0.4, 'id': 66666, 'attack_range': 128, 'default_filename': None, 'strength_gain': 2.7, 'strength': 21.0, 'min_hp': 549, 'armor': 1.96, 'intelligence_gain': 1.5, 'movespeed': 300, 'max_hp': 1765, 'max_cast_animation': 0.51, 'stat': 'Strength', 'resource_name': None, 'description': None, 'faction': 'Radiant', 'missile_speed': 0, 'image_name': None, 'max_damage': None, 'min_cast_animation': 0.4, 'max_mana': 702, 'name': None, 'roles': 'Carry / Pusher', 'base_attack_time': 1.7, 'agility_gain': 1.3, 'min_mana': 234, 'max_attack_animation': 0.3, 'order': 4}

I understand that the reason I am most likely getting this error, is b/c the previous session.add() is trying to add a record to the database with a primary key that already exists. The part I don't understand, is usually this happens automatically while using Postgresql, so why is this situation unique that the auto-increment isn't happening automatically on the add(). It is also important to the note, that the number in this part of the error message "(id)=(14)" increments by one with each successful call of the script.

Answer 1

The problem ended up being with Postgres. I had created the database by importing a sql file, and the sequence that kept track of the heroes primary key ended up not being accurate. This explained why the id was being incremented by one on each subsequent run, b/c Postgres was attempting to find an unused primary key. I solved it by issuing the following query on my database.

SELECT setval('heroes_id_seq', MAX(id)) FROM heroes;