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

[pla1]

Getting the following from nfldb-update. TIA, PLA

/usr/local/bin/nfldb-update

-------------------------------------------------------------------------------

STARTING NFLDB UPDATE AT 2016-09-18 17:56:02.147214

Connecting to nfldb... done.

Setting timezone to UTC... done.

Locking write access to tables... done.

Updating season phase, year and week... done.

Bulk inserting data for 2 games...

Sending batch of data to database.

Traceback (most recent call last):

File "/usr/local/bin/nfldb-update", line 39, in <module>
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 221, 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 223, 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".

[adamvonderhaar]

Having the same issue.

[pla1]

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

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

[adamvonderhaar]

That worked great. Thanks.

[leighlondon]

Are you up to date with nfldb? There's a ton of recent issues with manually inserted data (like that) causing issues down the line (e.g. https://github.com/BurntSushi/nfldb/issues/186).

[pla1]

@leighlondon I am up-to-date. I tried an upgrade prior to posting this issue.

sudo pip install nfldb --upgrade

The JAX insert into the team table was just a work-around. There was data coming into the play table. You can see it with this query:

select * from play where pos_team = 'JAX';

[mlloyd5]

Hi all,

I am receiving the same error. This is on a new install of nfldb

$ pip freeze beautifulsoup4==4.5.1 enum34==1.1.6 httplib2==0.9.2 nfldb==0.2.17 nflgame==1.2.20 psycopg2==2.6.2 pytz==2016.6.1 Then I tried the workaround @hpadmanabhan mentions in nflgame issue 201

I performed the update: update team set team_id = 'JAX' where team_id = 'JAC';

I changed the lines in nflgame\__init__.py and nfldb\team.py From: ['JAC', 'Jacksonville', 'Jaguars', 'Jacksonville Jaguars', 'JAX'], To: ['JAX', 'Jacksonville', 'Jaguars', 'Jacksonville Jaguars', 'JAC'],

Then I ran nfldb-update --player-interval 0

Which returns a different error, this time JAC: psycopg2.IntegrityError: insert or update on table "play" violates foreign key constraint "play_pos_team_fkey" DETAIL: Key (pos_team)=(JAC) is not present in table "team".

[joshutt]

I had the same problem and did the manual insert to resolve it. It looks like the real problem is that this week the game table is using JAC but the play table is using JAX. But in previous weeks it's JAC in both places. That is why just doing an update doesn't fix it, but just switches where it finds the problem.

In my case I added JAX to the team table, so that I have both JAC and JAX. Then on my side of the application wrote some code to convert JAX to JAC.

But all of this is a workaround and it appears that there will have to be a master adjustment to account for both JAC or JAX coming from GameCenter. (I suspect that ARI or ARZ could be a potential problem in the future as well)

[kennysushi]

Hi @pla1 , sorry if this is obvious, but where did you place this work around? In one of the .py files?

I did the following as a work-around: insert into team values('JAX','Jacksonville', 'Jaguars');

[pla1]

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

psql -U nfldb nfldb

[mlloyd5]

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'

[ghost]

@pla1 did you get this resolved? thanks @mlloyd5 your manual workaround worked for me

[BurntSushi]

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).

[mrg1999]

OK, I'm lost. Where do I insert the insert into team values ('JAX','Jacksonville','Jaguars') I added it to the nfldb -update script and received an error. Added it to the config.ini script and received an error. I'm using the Windows install. Or should I just wait for the upgrade?? Thanks.

[challgren]

You would need to do that query in postgres the database, if you are not a database administrator, it would be wise to wait for an update.

On Sep 25, 2016 6:17 PM, "mrg1999" notifications@github.com wrote:

OK, I'm lost. Where do I insert the insert into team values ('JAX','Jacksonville','Jaguars') I added it to the nfldb -update script and received an error. Added it to the config.ini script and received an error. I'm using the Windows install. Or should I just wait for the upgrade?? Thanks.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/BurntSushi/nfldb/issues/194#issuecomment-249452979, or mute the thread https://github.com/notifications/unsubscribe-auth/AAFbTQ7_V_Mo9dGr1CcMESl14ROBK_hIks5qtwD0gaJpZM4KAB6h .

[chppppp]

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".

[Deom23]

When is this going to be fixed?

[kennysushi]

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

[clesiemo3]

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.

[mrg1999]

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.

[slash-zero]

Hi! I just did a fresh install, new DB and all, and got the error reported in this issue. I opted for the simple insert statement as a work-around for now. Has nflgame been updated to do the auto translation form JAC to JAX? Is there a plan to get this fixed in a way that won't break existing installations?

Best regards.

[mrg1999]

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

[ghost]

@mrg1999 This is probably obvious, but for those unaware, you can throw those into a .bat file as well and then run the single file. Adding a pause at the end will make sure there aren't any errors. If anyone is using Anaconda the paths will be "C:\Anaconda2" and "C;\Anaconda2\Scripts" respectively.

[andr3w321]

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.

[mrg1999]

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?

[ghost]

Hi - Would this still be an issue on a new install? Seeding the database with the file at the bottom of the readme and then running nfldb-update yields this error. Am I doing something wrong or is that expected?

Thanks!

$ pip freeze                                                                                                                                                                                            
beautifulsoup4==4.6.0                                                                           
dnspython==1.14.0
docopt==0.6.2
enum34==1.1.6
fuse-python==0.2.1
fusepy==2.0.4
httplib2==0.10.3
mercurial==3.9.1
nfldb==0.2.17
nflgame==1.2.20
psycopg2==2.7.3
pycrypto==2.6.1
pytz==2017.2

After somehow missing mrg1999's comment, that's what I ended up doing. Subsequent run of nflupdate, ran fine.

[kevin2107]

@verchirl you need to do what @mylloyd5 said and update your database with the entry JAX in the team table. then run the nfldb-update script. Have you used postgres before? If not I can give you a more detailed walk through.

[ghost]

@kevin2107 - Thanks Kevin. I ended up doing the table insert/update/delete. I was just worried I had installed improperly and was supposed to receive the fix by other (python package?) means.