JAX vs JAC: nfldb-update fails

[troutct]

I have nfldb installed w/ POSTGRES db..My job involves Oracle DATABASE development so I'm able to figure out POSTGRES well enough ..I've also done lots of program development but I am BRAND NEW to Python and JSON. I've had NFLDB installed since last season and last year had no problems running nfldb-udpate weekly but this year I'm seeing failures on both the STL-LA and JAC-JAX issue. I followed advice in issue #201 to update entry in TEAM table from JAC to JAX and updated the following entries in nfldb/team.py and nflgame/init.py:
['JAC', 'Jacksonville', 'Jaguars', 'Jacksonville Jaguars', 'JAX'],
to
['JAX', 'Jacksonville', 'Jaguars', 'Jacksonville Jaguars', 'JAC'],

Now though when I run nfldb-udate, I get error message :
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".

I'm GUESSING that the update scrapes data and assigns team codes based on the entries in the nfldb/team.py and nflgame/init.py files and that now the Jacksonville players should come in as JAX and not JAC...is that right ? If so, why would I be getting this error-that leads me to believe data or Jacksonville players is coming in as JAC in spite of my updates to those files. I've seen other suggestions to run this :

python nfldb-update --player-interval 0

I've done that thinking that forces an update of the player data which is trying to be inserted (maybe I had old temp data w/ JAC instead of JAX that it is trying to load to db ?)

I've also forced a schedule update with:

python update_sched.py --year 2016

That works but no data is inserted to db..does this just pull down temp data that is then inserted by nfldb-update ?

I've scoured numerous issues and searched on JAX, JAC, etc. but can't find any solution that works (nor, curiously, anyone else with my exact issue).

What am I missing !?

Thanks in advance !

[troutct]

Sorry..failed to mention the above install is on a Windows PC..not sure if that matters or not..

[ochawkeye]

The proposed solutions being batted around in the issue you referenced were not how @BurntSushi ultimately ended up handling the change from JAC to JAX. I suspect what you are finding is that there are a number of games out there from 2015 that aren't yet in your database that still reference JAC and since you have removed that team from your .teams to try to resolve the roster issue they no longer have a home.

What version of nfldb do you have?

This might all be resolved by pip install --upgrade nfldb to upgrade to the newest that @BurntSushi released yesterday.

[troutct]

How do I find out what version I have installed ?
Also, if I run pip install --upgrade nfldb would it wipe out any custom tables/fields I've created in my nfldb schema ? (for instance, I created a team_pts table that stores how many points each team's D gives up to various positions for fantasy-football purposes and then have custom SQL to update those values after each week's games and I've added flags to the player table to tag my fantasy players)

[ochawkeye]

How do I find out what version I have installed ?

pip freeze

Also, if I run pip install --upgrade nfldb would it wipe out any custom tables/fields I've created in my nfldb schema ?

I don't think it would unless you manually download the .sql file that was also released and imported that into your PostgreSQL database. I'm not sure I'd trust me entirely on this though...

[troutct]

I've also tried updating TEAM table back to JAC but then I get an error saying JAX not found when I run nfldb-update.

[troutct]

version info:
C:\Python27\Scripts>pip freeze
SQLAlchemy==1.0.14
beautifulsoup4==4.4.1
enum34==1.1.1
httplib2==0.9.2
lxml==3.5.0
mlbgame==1.0.3
nfldb==0.2.13
nflgame==1.2.18
psycopg2==2.6.1
pytz==2015.7

[ochawkeye]

If you're absolutely against upgrading nfldb to the latest version 0.2.17 then there is probably one more thing you might be able to do...

Change your team table back to JAC and then change http.follow_redirects to True.

I think your best bet is probably to upgrade to the latest and greatest though.

[troutct]

Ran the update..seemed to go ok and my custom stuff is still there:
C:\Python27>pip install --upgrade nfldb
Downloading/unpacking nfldb from https://pypi.python.org/packages/76/cc/e0194496fd7e598c83252ae16e2cd69df5b0363f3fd74b812b9551e83507/nfldb-0.2.17.tar.gz#md5=fe298540aec37b9be40720afc8b0f304
Running setup.py (path:c:\users\trout\appdata\local\temp\pip_build_chris\nfldb\setup.py) egg_info for package nfldb

Downloading/unpacking nflgame>=1.2.20 (from nfldb)
Running setup.py (path:c:\users\trout\appdata\local\temp\pip_build_chris\nflgame\setup.py) egg_info for package nflgame

Downloading/unpacking psycopg2 from https://pypi.python.org/packages/a4/0e/29d29dceca6e465804ae612bc711a4741599ac849cf0a99acdbf53838581/psycopg2-2.6.2-cp27-none-win_amd64.whl#md5=08d618f1c5a4db211481e59102cc16f8 (from nfldb)
Downloading/unpacking enum34 from https://pypi.python.org/packages/c5/db/e56e6b4bbac7c4a06de1c50de6fe1ef3810018ae11732a50f15f62c7d050/enum34-1.1.6-py2-none-any.whl#md5=68f6982cc07dde78f4b500db829860bd (from nfldb)
Downloading enum34-1.1.6-py2-none-any.whl
Downloading/unpacking pytz from https://pypi.python.org/packages/ba/c7/3d54cad4fb6cf7bf375d39771e67680ec779a541c68459210fcfdc3ba952/pytz-2016.6.1-py2.py3-none-any.whl#md5=735126cd1c1ddc824b7c94e091d7173e (from nfldb)
Requirement already up-to-date: httplib2 in c:\python27\lib\site-packages (from nflgame>=1.2.20->nfldb)
Downloading/unpacking beautifulsoup4 from https://pypi.python.org/packages/dc/27/8919b7a505b00f410c1c113c6c93b3253784375797976272e4198f4e455a/beautifulsoup4-4.5.1-py2-none-any.whl#md5=0000d92f11b0578c111ae5bf3f86933e (from nflgame>=1.2.20->nfldb)
Installing collected packages: nfldb, nflgame, psycopg2, enum34, pytz, beautifulsoup4
Found existing installation: nfldb 0.2.13
Uninstalling nfldb:
Successfully uninstalled nfldb
Running setup.py install for nfldb

Found existing installation: nflgame 1.2.18
Uninstalling nflgame:
Successfully uninstalled nflgame
Running setup.py install for nflgame

Found existing installation: psycopg2 2.6.1
Uninstalling psycopg2:
Successfully uninstalled psycopg2
Found existing installation: enum34 1.1.1
Uninstalling enum34:
Successfully uninstalled enum34
Found existing installation: pytz 2015.7
Uninstalling pytz:
Successfully uninstalled pytz
Found existing installation: beautifulsoup4 4.4.1
Uninstalling beautifulsoup4:
Successfully uninstalled beautifulsoup4
Successfully installed nfldb nflgame psycopg2 enum34 pytz beautifulsoup4
Cleaning up...

should I leave the TEAM entry as JAX or change it back to JAC..I'm assuming change back to JAC ? Anything else I need to do before retrying nfldb-update ?

[troutct]

Tried this:

1. set team back to JAC
2. ran python update_sched.py --year 2015 ( to get missing postseason games)
3. ran python update_sched.py --year 2016 (to get this year's missing games)
4. ran python nfldb-update

output:

C:\Python27\Scripts>python nfldb-update

STARTING NFLDB UPDATE AT 2016-09-09 15:50:32.191000
Connecting to nfldb... Traceback (most recent call last):
File "nfldb-update", line 39, in
nfldb.update.run(**vars(args))
File "c:\Python27\lib\site-packages\nfldb\update.py", line 535, in run
doit()
File "c:\Python27\lib\site-packages\nfldb\update.py", line 502, in doit
db = nfldb.connect()
File "c:\Python27\lib\site-packages\nfldb\db.py", line 137, in connect
_migrate(conn, api_version)
File "c:\Python27\lib\site-packages\nfldb\db.py", line 447, in _migrate
globsfname
File "c:\Python27\lib\site-packages\nfldb\db.py", line 911, in _migrate_8
''' % (', '.join(_mogrify(c, team[0:3]) for team in nfldb.team.teams2)))
File "c:\Python27\lib\site-packages\psycopg2\extras.py", line 223, in execute
return super(RealDictCursor, self).execute(query, vars)
psycopg2.IntegrityError: duplicate key value violates unique constraint "team_pkey"
DETAIL: Key (team_id)=(LA) already exists.

After I ran pip install --upgrade nfldb earlier, my team table had 34 entries....all normal entries plus one for UNK and the new LA entry. I then updated JAX to JAC..ran the updates mentioned in this comment and get the error that LA already exists...

should I delete the LA entry in teams and try again ? There are no GAMES with home/away team = 'LA' but there ARE players with team='LA'..looks like I would need to delete those first before deleting the LA team record.

[BurntSushi]

You should never ever ever be inserting rows into the team table manually. This is what database migrations inside of nfldb do. When you run nfldb, it sees that your database is old, so it tries to upgrade. Part of that upgrade is adding the new LA team. If that team is already in the table, then insert rightfully fails. Don't touch the team table and let nfldb handle it for you.

[troutct]

I agree but I wasn't aware of the automated pip install - upgrade nfldb process until now.

Now that it's there should I delete all of the player data for team=LA (or should I update them to team='STL' ?) and then delete the team record for LA and then run nfldb-update ?

[BurntSushi]

You could do that. Or just start fresh with the DB linked at the bottom of the README.

[troutct]

Ran the following:
update player set team='STL' where team='LA'
delete from team where teamid='LA'

and then ran nfldb-update

worked like a charm...I'm up and running

Thanks to both ochawkeye and BurntSushi !