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 #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');