nfldb: Key (pos_team)=(JAX) is not present in table "team".

I hope to have this fixed before the next game.

I think the best thing to do is to settle on JAC/JAX, and do the translation inside nflgame when the JSON is parsed. On the next update, I’ll include a DB migration that syncs the entire database with the correct team name (something similar to what @mlloyd5 did manually).

Ok so here’s my workaround. I reset my install, but I didn’t like the idea of keeping that inserted row in the teams table so I changed all occurrences of ‘JAX’ in the database to ‘JAC’ and then deleted the row from the teams table. Queries below: Run on the database: insert into team values ('JAX','Jacksonville','Jaguars') Run nfldb-update

If you’d like, you can verify that only the play table needs to be updated:

SELECT COUNT(*) from play where pos_team = 'JAX';
SELECT COUNT(*) from drive where pos_team = 'JAX';
SELECT COUNT(*) from game where home_team = 'JAX';
SELECT COUNT(*) from game where away_team = 'JAX';
SELECT COUNT(*) from player where team = 'JAX';
SELECT COUNT(*) from play_player where team = 'JAX';

Update play table: UPDATE play SET pos_team = 'JAC' WHERE pos_team = 'JAX'

Delete ‘JAX’ value from team: DELETE FROM team WHERE team_id = 'JAX'

@vhaar1 I did the following as a work-around.

insert into team values('JAX','Jacksonville', 'Jaguars');

Here’s the script I’m using. Hopefully @BurntSushi finds the time for a permanent fix soon!

psql -U nfldb nfldb -c "INSERT into team values('JAX','Jacksonville', 'Jaguars');"
nfldb-update
psql -U nfldb nfldb -c "UPDATE play SET pos_team = 'JAC' WHERE pos_team = 'JAX';"
psql -U nfldb nfldb -c "DELETE FROM team WHERE team_id = 'JAX';"

I’m running ubuntu and saved it as ~/.local/bin/nfldb-update-script.sh Make it executuable with chmod +x nfldb-update-script.sh and then instead of running nfldb-update run nfldb-update-script.sh from anywhere.

Looking forward to next season… Will the JAX/JAC issue be resolved? Will the Rams issue be resolved? Will the Raiders move to Las Vegas create a new issue? (This isn’t a dig at BurntSushi but rather questions about GameCenter) For one segment of my program that reports score changes, penalties etc. of games in progress I’d like to run nfldb-update at a 30 second interval: C:\Python27\scripts python nfldb-update --Interval 30 But would I need to also run the INSERT into team values('JAX','Jacksonville', 'Jaguars'); and UPDATE play SET pos_team = 'JAC' WHERE pos_team = 'JAX'; DELETE FROM team WHERE team_id = 'JAX' with each update? And can I incorporate these lines into the nfldb-update script if so?

KennySushi and mlloyd5, thank you very much for your help. My postgres and python programming abilities are lacking. I used them to retrieve the info from the nfldb database and import the scores/stats into an excel vba program that I spent about 6 months developing and was working flawlessly until the JAC/JAZ episode. My excel program does an nfldb upgrade at the beginning of the season, at the transition from pre-season to regular and to post-season. It also does an upgrade once per month during the regular season so I had to include your Insert, PLAY, UPDATE and JAX DELETE each time the upgrade is performed. So far, my program is working well due to your help.

What I did was create 3 different scripts and save them in the Python27 folder:

Create a script named Insert_JAX.sql with the following code: INSERT into team values('JAX','Jacksonville', 'Jaguars');

Then create a script named NewDB.pywith the following code: import os os.chdir("c:\\Python27\\Scripts") execfile("nfldb-update")

Then create another script named Delete_JAX.sql: UPDATE play SET pos_team = 'JAC' WHERE pos_team = 'JAX'; DELETE FROM team WHERE team_id = 'JAX'

My program code runs as thus: psql -U nfldb -f C:\Python27\Insert_JAX.sql Python C:\Python27\NewDB.py psql -U nfldb -f C:\Python27\Delete_JAX.sql

This seems to be the best fix for now and I suspect it wont hurt anything to keep the code if/when a fix is implemented. One note is that this script will need to be re-run any time you upgrade nfldb

the JAX insert (insert into team values ('JAX','Jacksonville','Jaguars')) does the trick. A better solution would be for the update script to check if team’s (or other necessary ref data) don’t exist and add them prior to inserting transactional data.

It might be up to the end user whether to update older records to match or to query by name (e.g. name = 'Jaguars') to get both sets of data. All depends on your use case I suppose.

I used the work around below and it’s been working fine since then. I assume at some point I’ll have to roll back. IIRC you must navigate to the folder where “psql” is located (usually the PostgreSQL folder) via cmd.exe and then run the insert statement via:

I executed the insert statement after logging-in on the server via the psql command:

psql -U nfldb nfldb

I seem to get this error with LA as well. Just notifying.

psycopg2.IntegrityError: insert or update on table "play" violates foreign key constraint "play_pos_team_fkey"
DETAIL:  Key (pos_team)=(LA) is not present in table "team".

@kennysushi I executed the insert statement after logging-in on the server via the psql command:

psql -U nfldb nfldb

That worked great. Thanks.