Create Import Script

[christinechengubc]

Create an import script to import data using something like a csv file (Excel file), which will be easy for a Wargamers Exec to fill in with the necessary data.

We want to try using the BGG XML API to reduce the manual input labour required.

Here is the suggested workflow:

1. Wargamers provides us with board game title, their copies, condition and expansions. We will need to parse this csv file to get the title of each game (used for step 2 and 4). Also parse the number of copies, condition and expansions (used for step 4).
2. For each title, do a search using the BGG API. Parse the XML response to find the ID of the game (used for step 3).
3. Given the ID, use the BGG API to get the game information (publisher, category, min_players, max_players, min_playtime, max_playtime, year_published, description, image, rating, users_rated, complexity, bgg_id)
4. Populate the database with the information from the csv file (from step 1) and the BGG XML response (from step 3). You can do this by directly inserting each row using the INSERT SQL statement, or whatever is easiest.

Please see #42 for the Games table schema.

Should discuss the issues we might face with BGG's search algorithm and also possible mispellings in the title. Should also keep the ID we found on BGG for each game in our database for future use.

[bryceamiranda]

does this include exporting from database to csv file as well?

[christinechengubc]

Hmm, no... exporting is not important for now. We can add that into the 'Cool Stuff' column that we could consider implementing in the future. I can see how what you mentioned could be very useful. I've updated the description to what I think the wargamers will give us.

[bryceamiranda]

@christinechengubc Would I do a games.post request with 4 parameters (from workflow #1) and then using BGG to extract all the other info needed to populate the db? Then from each row of the csv file, keep calling the post method? From BGG alone, there's no info on the complexity of a game, do we remove complexity from schema or do we let users vote for difficulty, and so need another column similar to 'users_rated'?

Also how will we add multiple 'copies' of a game under a single bgg_id? Will we just duplicate a row for every single copy of a game they have?

Sorry lots of questions, I'm seeing terb tonight so if he knows the answers to these, I can just ask him later :P

[christinechengubc]

Would I do a games.post request with 4 parameters (from workflow #1) and then using BGG to extract all the other info needed to populate the db? Then from each row of the csv file, keep calling the post method?

No need to post. This is just for initial import. You can directly create the INSERT SQL script. I will update the workflow above to have more details. Hopefully it'll be clearer.

From BGG alone, there's no info on the complexity of a game, do we remove complexity from schema or do we let users vote for difficulty, and so need another column similar to 'users_rated'?

Complexity of a game is under the tag 'averageweight' under 'ratings' under the 'statistics' tag. Rating and users_rated will also come from BGG. The only information not from BGG will be title, condition, total copies and expansions. (available copies during initial import will be set to the same number as total copies)

Also how will we add multiple 'copies' of a game under a single bgg_id? Will we just duplicate a row for every single copy of a game they have?

One row per game title in the games table. There is a 'copies' column in the games table to indicate how many copies they own. For example:

title: monopoly, copies: 2, bgg_id: 2963

[bryceamiranda]

just to clarify, so is this going to be some sort of python script where given a csv file, I will use the BGG API to gather all extra info and then populate the postgres database?

[christinechengubc]

Doesn't have to be python, but yes, it uses BGG API to gather info and then populate the database with it.

[christinechengubc]

Finished. Just needs some finishing touches when we actually receive the .csv file.