psycopg2.IntegrityError: insert or update on table "play" violates foreign key constraint "play_pos_team_fkey"

[bradybray]

I encountered the following when running nfldb-update

STARTING NFLDB UPDATE AT 2017-11-18 18:17:37.189874 Connecting to nfldb... done. Setting timezone to UTC... done. Locking write access to tables... done. Updating season phase, year and week... done. Adding schedule data for 16 games... done. Bulk inserting data for 257 games... Sending batch of data to database. Sending batch of data to database. Sending batch of data to database. Sending batch of data to database. Sending batch of data to database. Sending batch of data to database. Sending batch of data to database. Traceback (most recent call last): File "/usr/local/bin/nfldb-update", line 39, in nfldb.update.run(**vars(args)) File "/usr/local/lib/python2.7/dist-packages/nfldb/update.py", line 535, in run doit() File "/usr/local/lib/python2.7/dist-packages/nfldb/update.py", line 525, in doit update_games(db, batch_size=batch_size) File "/usr/local/lib/python2.7/dist-packages/nfldb/update.py", line 397, in update_games bulk_insert_game_data(cursor, scheduled, batch_size=batch_size) File "/usr/local/lib/python2.7/dist-packages/nfldb/update.py", line 195, in bulk_insert_game_data do() File "/usr/local/lib/python2.7/dist-packages/nfldb/update.py", line 188, in do nfldb.db._big_insert(cursor, table, bulk[table]) File "/usr/local/lib/python2.7/dist-packages/nfldb/db.py", line 356, in _big_insert % (table, insert_fields, values)) File "/usr/local/lib/python2.7/dist-packages/psycopg2/extras.py", line 249, in execute return super(RealDictCursor, self).execute(query, vars) psycopg2.IntegrityError: insert or update on table "play" violates foreign key constraint "play_pos_team_fkey" DETAIL: Key (pos_team)=(JAX) is not present in table "team".

[RGAlexander216]

Same problem

[adilq93]

same issue

[903124]

tried https://github.com/BurntSushi/nfldb/issues/194 ?

[RGAlexander216]

That does fix this, but I would suggest adding a (or multiple) alias fields to the team table for these teams with various abbreviations: ARZ/ARI BLT/BAL CLV/CLE HST/HOU JAX/JAC LAC/LACH/SD LAR/LARM/STL WSH/WAS

[ochawkeye]

That does fix this, but I would suggest adding a (or multiple) alias fields to the team table for these teams with various abbreviations:

Someone might use those as abbreviations for those teams, but not NFL.com. The issue here is that the NFL shifted gears and used JAC and JAX in their play-by-play. Adding this list of aliases isn't going to have any impact on evaluating play-by-play if there aren't any play-by-plays with them in it.

nfldb already offers an ability to alias the abbreviations with nfldb.team.standard_team()

[RGAlexander216]

Touché. My suggestion came from a perspective of being able to associate the correct team with respect to the relocations that have/will occur as well as essentially serve as an optional, secondary ID for each team_id. This extra field on the table wouldn't impact the play-by- play, just help the end-user with statistical analysis over time.

[RGAlexander216]

Just seeing your last sentence, that works too. Thank you.

[ariannedee]

You should add LAC too:

insert into team values('JAX','Jacksonville', 'Jaguars');
insert into team values ('LAC', 'Los Angeles', 'Chargers');