MTGJSON Version: 4.6.1

Description of Bug:

A card in the cards.csv file, which appears to be Air Elemental, is missing data in its columns. It appears the flavor text is not properly escaped or quoted.

As discussed in https://discord.com/channels/224178957103136779/1127350404292812821 on the discord, adding uuid indexes after the create statements for tables will speed up queries on uuids and joins using them significantly.

A statement like the below added after a table creation (referencing the appropriate table) will add an index for the UUID column for that table. The index name can be any appropriate name, but using [tablename]uuid would help with consistency and prevent collisions. Formatting appears to be the same between mysql and sqlite for basic indexes.

CREATE INDEX cardIdentifiersuuid ON cardIdentifiers(UUID);
CREATE INDEX cardsuuid ON cards(UUID);

Further detail on mysql indexes: https://dev.mysql.com/doc/refman/8.0/en/create-index.html

The main function should have some optional parameters and if the are present they should be use instead of sys.argv.

Hello thanks a lot for the time you've taken to do this script. I need to get a database with tix, usd and eur prices, so I can use this sql and add the prices from scryfall or convert the scryfall json directly without using the mtgjson file. For both I would need to know how the script works to modify it. The problem is I've understood everything except the generate_sql_schema function, because I can't open the json, it makes my computer freeze because it's too big, and I can't follow the logic of the function. Could you write down in a docstring for that function or maybe here or in the readme what's the "json data" look like and what's the sql result? (I don't know how to call the "json data", what I mean is the keys without the actual data, what would be the relations or table names in the sql database")

And if you could split that function and add more comments to it so it gets easier to follow it's logic, it would be really nice.

I've also refactored some of the code to better understand it, you probably may want to add some of the changes: https://pastebin.com/35Wqvdri
I haven't tested it that's why I didn't make a pull request.

60 records in cards table prefix their name, facename, and number with "A-" (the letter "A" and a hyphen). You can see all 60 using the following query.

SELECT * FROM cards WHERE [name] LIKE 'A-%' OR faceName LIKE 'A-%' OR number LIKE 'A-%';

For example, one record has name "A-Cloister Gargoyle" instead of "Cloister Gargoyle".

A nice feature request would be to have the data in CSV format. This would let people import not only into MySQL or Postgres but also spreadsheet applications.

Hello,
I'd be amazed to have tables for existing preconstructed decks, and their content.

Possibly point out that unless developers want to clone down the now removed AllSets-x.db, they can do a git clone --depth 1 https://github.com/mtgjson/mtgsqlive.git to go from a 40+ MB clone to 156KB

Or possibly just prune out the database entirely.

Steps to reproduce

Create a sqlite database (in my case with Pycharm)

What is expected?

import of the AllPrintings.sqlite creates tables and populates the DB with data

What is actually happening?

Error while importing:

end of turn.Instant10E,2ED,30A,3ED,4BB,4ED,5ED,9ED,CED,CEI,DDL,ELD,FBB,J22,LEA,LEB,M10,SUM{'cardKingdom': 'https://mtgjson.com/links/2a40307b3b5b449a', 'cardKingdomFoil': 'https://mtgjson.com/links/27f686be1b473504', 'cardmarket': 'https://...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "of": syntax error)
end of each day, bringing on the cold blanket of night.2003Englishnormal{6}{U}{U1624414400498cd5a-75e9-5163-90e8-f8d15a97af592710327102135250Denizen of the Deep80When Denizen of the Deep comes into play, return each other creature you con...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "of": syntax error)
end of turn. Play this ability no more than twice each turn.Creature - Bat010E,4BB,4ED,5ED,DDD,GVL,ITP,LEG,REN,RQS{'cardKingdom': 'https://mtgjson.com/links/e6388368a705aa47', 'cardmarket': 'https://mtgjson.com/links/61ff08dbdc04292a', 'tcg...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "of": syntax error)
end of turn. This ability can only be used during opponent's turn, before the attack. May not be used on creatures summoned this turn.Summon - Imp12ED,30A,3ED,CED,CEI,FBB,LEA,LEB,SUM{'cardKingdom': 'https://mtgjson.com/links/9aaa6f32cbc0212...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "of": syntax error)
end of turn.Enchantment — AuraEnchantment7e82ffc2-b036-568b-94b3-a7e3773af228�K�Ue�!������������� ������� �����U�������������?��}���UUU����������9���U��Mark Tedinpaperdraftwhite2608939138RR �?����Q�nonfoil1993E...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "of": syntax error)
end of combat.4Creature — CockatriceCreature7053701c-7ca4-5bf5-9a50-32544c628be4�,�{e�)���������������������������U��������{�������}���UUU����������Y���U��Richard Thomaspaperdraftwhite2603139080GG�nonfoil1993En...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "of": syntax error)
end of combat, if Clockwork Beast attacked or blocked this combat, remove a +1/+0 counter from it.
{X}, {T}: Put up to X +1/+0 counters on Clockwork Beast. This ability can't cause the total number of +1/+0 counters on Clockwork Beast to be...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "of": syntax error)
end step, return target creature card with mana value X or less from your graveyard to the battlefield, where X is the amount of life you gained this turn.4Legendary Creature — Human WarriorCreaturedf89c644-171e-5c29-bcb0-f148a13a270781a909...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "step": syntax error)
end step, return target creature card with mana value X or less from your graveyard to the battlefield, where X is the amount of life you gained this turn.Legendary Creature — Human Warrior340K{'cardKingdom': 'https://mtgjson.com/links/af00...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "step": syntax error)
end step, return target creature card with mana value X or less from your graveyard to the battlefield, where X is the amount of life you gained this turn.4Legendary Creature — Human WarriorCreature81a9093e-b573-5d77-8e09-d80ceeecbfb5df89c6...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "step": syntax error)
end of turn.InstantInstant6e1f4007-7c36-56aa-8888-3f5976e979025ecf0168-1293-59e1-9ad0-a709f3e13f00�A�ze�)����������������������������U���C����w������}���UUU����������w���UU�Games Workshoppaperblack26613893667WW...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "of": syntax error)
end of turn.Instant40K{'cardKingdom': 'https://mtgjson.com/links/9f8c3ea52a255fbf', 'tcgplayer': 'https://mtgjson.com/links/41becc97b35feac7'}uncommon84d1bcef-907c-4c3a-8b52-6e633fa19667275bb019-a883-4303-988e-09b212a5a1e50ea6d165-2c30-41a4...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "of": syntax error)
end step, and if it would leave the battlefield, it is still exiled instead.a346e73e-f771-5eab-992a-94abb5c50b62�S����!�KU2022-10-07At the beginning of the end step, a permanent returned to the battlefield with unearth is exiled. This is a ...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "step": syntax error)
end step, a permanent returned to the battlefield with unearth is exiled. This is a delayed triggered ability, and it can be countered by effects that counter triggered abilities. If the ability is countered, the permanent will stay on the ...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "step": syntax error)
end of turn.4Creature — Human SoldierCreaturec7d8e4b4-845b-56b7-9a9d-76897a195f6f2fdcd5af-d981-58e6-a92a-bf9e9c1a6efa�R�Jd�A������������I������������U���/����/A�����}���UUU�����'����1�A�UU�Ørjan Ruttenborg Sv...
[2023-03-07 08:24:20] [1] [SQLITE_ERROR] SQL error or missing database (near "of": syntax error)

Additional context

The db driver is the latest provided by Pycharm:
DBMS: SQLite (ver. 3.40.1) Case sensitivity: plain=mixed, delimited=mixed Driver: SQLite JDBC (ver. 3.40.1.0, JDBC4.2)

Booster is an awkward structure, lets see if we can simplify it in alt formats!

For people that use "AUTOCOMMIT=0" in mysql import its crucial that the file contains a "commit;" at the end.
It would be great if the new version of the files would contain this again.

I believe the previous versions contained a commit at the end. My automation is only failing since the new version dropped, because the commit is missing, my check for thresholds isn't passed anymore.

I don't know why there are linking tables. Instead of saving a number for each rarity just save the rarity for the card for example. I was going along with the database created by this script but I've reached the point where I think I'll better rewrite the script to avoid using linking tables when I've tried to query for the cards legal in legacy. I couldn't even find the proper way of doing it:

Maybe something like this which I think is just crazy difficult when it would be very simple if instead of a number the cardLegalFormat actually saved the name of the format and the kind of legality.

cardList = cursor.execute("SELECT DISTINCT name
                           FROM cards
                           JOIN lnkCardLegFormat
                             ON card.cId=lnkCardLegFormat.llfId
                           JOIN lnkLegalityFormat
                             ON lnkCardLegFormat.llfId=lnkLegalityFormat.llfId
                           JOIN luLegalities
                             ON luLegalities.legId=lnkLegalityFormat.legId
                           JOIN luFormats
                             ON luFormats.fId=lnkLegalityFormat.fId
                           WHERE luFormats.formatName='Legacy' AND luLegalities.legalityName='Legal'")

Version(s) affected

• MTGJSON 4
• MTGJSON 5

Version(s) tested against

MTGJSON 5.0.1+20200824

Description of Bug

In AllPrintings.sqlite.zip the foreign_data table does not have the faceName field

When trying in a path, if it has an extra space at the end, the app complains.
If you supply a path to save a .sql file without specifying the name of the file (let's say, a directory to save it to) it complains.

The sqlite downloads listed on the mtgjson.com site are empty databases. They have schema, but no data.

Newlines in card text aren’t properly escaped, resulting in an issue where the cards don’t import successfully to SQL as it thinks each of these is the start of a new database entry.

The solution is to escape \n as \\n.

Documentation link in readme.txt leads to a webpage with no info

The following refers to the mysql output, not sure if its different anywhere else :

My assumption is that the "finishes" column will eventually supercede the "hasfoil" and "hasnonfoil" attributes, and they will be removed - however, there is a particular step backwards in that you can't now search for "foil" in the finishes column, as it brings back "nonfoil" as well - there is no distinct word to find just foils - can the content be changed to "F", "N", and "E"?

Alternatively, If this isn't the case, and the hasFoil and hasNonFoil attributes are here to stay, then can we get a hasEtched as well for consistency?

Download links from compiled versions not working. When I try to download SQLite files from the web site it shows me this page.

Using Cockatrice, when importing the file at https://www.mtgjson.com/files/AllPrintings.json.xz , the import is successful, but any set whose set code begins with "R" or any letter after "R" do not import with any cards. The "Manage sets" section shows that their set codes are there, but no names or release dates are associated with them and their cards are missing.

MTGJSON Version: 4.6.1

Description of Bug:

sets.csv has sets with 0 baseSetSize.

according to card model it should be int https://mtgjson.com/file-models/card-atomic/#edhrecrank

Using 5.1.0+20210911

I just opend a Urabrask the Hidden (THE LIST - IMA) ... and i can't find it in database.

You could use a list, loop the list for each value instead of reapiting the same code. The list would be: ["AllPrices.json", "AllDeckFiles", "Keywords.json", "Card Types"]

Hello,
I'd be glad to see tables for card eestimated prices in eur/usd according to their uuid!

Hello,

I don't have any experience with python.
I took the following steps:

1. Installed python 3.6
2. Used json_to_sql.py file.
3. Used the latest AllSets-x.json from mtgjson website.
4. Used the AllSets-x.db from Initial Release
5. Started the cmd, typed the command and the results are below:

c:\1>python json_to_sql.py 1 c:\1\x.db C:\1\AllSets-x.json
Starting JSON to SQLite Conversion...
Removing old saved database...
Moving old database to 1.old...
Traceback (most recent call last):
File "json_to_sql.py", line 244, in
main()
File "json_to_sql.py", line 217, in main
las_json_data = json.load(open(as_json_path, 'r'))
File "C:\Users*\AppData\Local\Programs\Python\Python36-32\lib\json_init_.py", line 296, in load
return loads(fp.read(),
File "C:\Users*\AppData\Local\Programs\Python\Python36-32\lib\encodings\cp1252.py", line 23, in decode
return codecs.charmap_decode(input,self.errors,decoding_table)[0]
UnicodeDecodeError: 'charmap' codec can't decode byte 0x81 in position 30: character maps to

If i'm using the files from Initial Release ( AllSets_Output.json and json_to_sql.py file from Source code (zip) all is running smoothly.

The fields colorIdentity, colorIndicator and colors have spaces between the values, which throws off things during import.

For example, Mortify’s colors is 'W', 'B'. If we can make it so it has no spaces like 'W','B' then it would be easier to import with SET and ENUM.

I believe it would be beneficial to include setName to database. Right now there is only setCode which is more meta-data like than it is useful.

In a particular use case example, when using the SQLite database one has to execute on the cards table, find the set code and then execute on the sets table to match and locate the set name - then do more work to store that upwards.

In short, this is helpful data for any developer in my opinion. Any other thoughts?

Right now the legalities table table is a bunch of UUID's with a column for one format at a time and its value, this is a bit of a mess in my opinion to work with - should we update this table to be one UUID per entry where we can have columns for each format with a value and some type of falsey value if the column is not legal. Something like this?

Version(s) affected

• MTGJSON 4
• MTGJSON 5

Version(s) tested against

5.0.1+20201010

Description of Bug

When the SQL database is created, it is storing certain objects in to non JSON valid strings, in particular from what I've seen, purchaseUrls. I believe this happen on the Python level. Here is an example from the SQLite database:

{
  'cardKingdomFoil': 'https://mtgjson.com/links/2ef7cc17b42b1381',
  'tcgplayer': 'https://mtgjson.com/links/33e174446a057ecc'
}

Single quotes are invalid, these should be double quotes so that parsers can read the string to JSON.

I was trying to compile my AsSets.json file to sqlite but I'm receiving this error:
Traceback (most recent call last): File "/usr/lib/python3.7/runpy.py", line 193, in _run_module_as_main "__main__", mod_spec) File "/usr/lib/python3.7/runpy.py", line 85, in _run_code exec(code, run_globals) File "/home/zion/Projetos/mtgsqlive/mtgsqlive/__main__.py", line 9, in <module> main() File "/home/zion/Projetos/mtgsqlive/mtgsqlive/json2sql.py", line 39, in main parse_and_import_cards(input_file, sql_connection) File "/home/zion/Projetos/mtgsqlive/mtgsqlive/json2sql.py", line 209, in parse_and_import_cards sql_dict_insert(set_insert_values, "sets", sql_connection) File "/home/zion/Projetos/mtgsqlive/mtgsqlive/json2sql.py", line 427, in sql_dict_insert cursor.execute(query, data) sqlite3.OperationalError: table sets has no column named keyruneCode

and I'm also not finding the pre-compiled SQLite file on the website

Program is missing a few fields from recent pushes mtgjson/mtgjson@e53479f

Hi,

It seems that the sqlite files served on https://mtgjson.com/downloads/compiled/ contain no data.

If a multiverseId doesn't exist for a card's foreignData, it is still outputted as an empty string, causing a type issue when it's expected to be an integer. It should be optional and not provided if it does not exist.

Hi all! I've been using the sqlite and noticed that there seem to be duplicates in setBoosterSheetCards. For example

SELECT
    *
FROM
    setBoosterSheetCards
WHERE
    cardUuid = "04ef11e8-3918-5332-9f7c-3dc29d972c5e"
    and setCode = "CMM"
    and sheetName = "rareMythicShowcase"

Returns

04ef11e8-3918-5332-9f7c-3dc29d972c5e    1    CMM    rareMythicShowcase
04ef11e8-3918-5332-9f7c-3dc29d972c5e    1    CMM    rareMythicShowcase

I'm curious if this is intentional in that the same card can show multiple times on a sheet or if this is an error where this should have been aggregated to a cardweight of 2.

Comparably, the following query returns 18376 rows where all values are duplicated in the table. These seem to be across multiple sets as well.

SELECT
    count(*)
FROM
    setBoosterSheetCards
GROUP BY
    cardUuid,
    cardWeight,
    setCode,
    sheetName
HAVING
    count(*) > 1

Using a similar query it seems there may be a similar issue in setBoosterSheets with 272 duplicate entries.

I posted this in the discord and was advised to forward the details to a github issue.

Edit:

I think I may have partially figured out the challenge here. sheetName + setCode is not sufficiently unique to differentiate sheets between packs.

For example, in CMM f2d7ee8f-f031-5fda-ae70-2ca615da2989 is present in both default and collector under the same sheetName nonlegendaryRareMythic

The current schema (shown below) is not sufficient to disambiguate sheets between boosters.

                            "sheetName": sheet_name,
                            "cardUuid": card_uuid,
                            "cardWeight": card_weight,

It is likely required that boosterName also be added to this schema OR a foreign key relationship with a primary key is created with setBoosterContents.

Error found: I'm getting foreign key errors in AllPrintings.sql when inserting into token table for nonexistent token sets. For example, the following inserts to the tokens table using set 'TAFR' instead of 'AFR'.

INSERT INTO tokens (setCode, colors, layout, multiverseId, scryfallId, scryfallOracleId, mtgjsonV4Id, name, language, borderColor, colorIdentity, finishes, frameVersion, hasFoil, hasNonFoil, number, availability, text, type, supertypes, types, subtypes, keywords, uuid, reverseRelated) VALUES ('TAFR', NULL, 'normal', '530447', '6f509dbe-6ec7-4438-ab36-e20be46c9922', '30d0398c-bf5a-4b30-936f-afdb5a109b4b', 'd8d7c8b6-e906-58c2-8eed-143357d5ef44', 'Dungeon of the Mad Mage', 'English', 'black', NULL, 'nonfoil,foil', '2015', 1, 1, '20', 'paper', 'Yawning Portal — You gain 1 life. (Leads to: Dungeon Level)
Dungeon Level — Scry 1. (Leads to: Goblin Bazaar, Twisted Caverns)
Goblin Bazaar — Create a Treasure token. (Leads to: Lost Level)
Twisted Caverns — Target creature can''t attack until your next turn. (Leads to: Lost Level)
Lost Level — Scry 2. (Leads to: Runestone Caverns, Muiral''s Graveyard)
Runestone Caverns — Exile the top two cards of your library. You may play them. (Leads to: Deep Mines)
Muiral''s Graveyard — Create two 1/1 black Skeleton creature tokens. (Leads to: Deep Mines)
Deep Mines — Scry 3. (Leads to: Mad Wizard''s Lair)
Mad Wizard''s Lair — Draw three cards and reveal them. You may cast one of them without paying its mana cost.', 'Dungeon', NULL, 'Dungeon', NULL, 'Scry', 'aa281821-2a70-5b23-8a0d-a5874c753e6c', 'Acererak the Archlich,Barrowin of Clan Undurr,Bar the Gate,Clattering Skeletons,Cloister Gargoyle,Delver''s Torch,Displacer Beast,Dungeon Descent,Dungeon Map,Eccentric Apprentice,Ellywick Tumblestrum,Fates'' Reversal,Fifty Feet of Rope,Find the Path,Fly,Gloom Stalker,Intrepid Outlander,Keen-Eared Sentry,Kick in the Door,Lost Mine of Phandelver,Nadaar, Selfless Paladin,Planar Ally,Precipitous Drop,Ranger''s Hawk,Secret Door,Sefris of the Hidden Ways,Shortcut Seeker,Tomb of Annihilation,Triumphant Adventurer,Varis, Silverymoon Ranger,Veteran Dungeoneer,Wandering Troubadour,You Find a Cursed Idol,Yuan-Ti Fang-Blade,Yuan-Ti Malison,Zalto, Fire Giant Duke,Zombie Ogre,Skeleton,Treasure');

Here is the resulting error.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tokens_setCode". The conflict occurred in database "MTG", table "dbo.sets", column 'code'.
The statement has been terminated.

This error occurs for all inserts to the token table that prefix the set with the letter 'T' which includes these: 'TAFR','TMID','TUST','TCLB','TA25','TGK1','TVOW'. None of these token sets are in the sets table. However, when I remove the 'T' prefix from them, they successfully insert to the token table.

My environment: I made no changes to the insert scripts. But I did modify the initial CREATE TABLE scripts to work with T-SQL on SQL Server Express since that's what I'm familiar with (and since I'm a noob to this site and trying this out for the first time). I'm happy to share my table creation scripts if you'd like. I maintained the foreign key relationships and have started creating separate tables for the ENUM columns.

On a personal note: I'm so glad I found this site! I'm very excited to not have to create my own databases and spreadsheets for everything. There's so much I can't get from the sites and services. I'm very good at T-SQL and Excel, but I'm no programmer. I'm just an ex-accountant who has worked for years now with ERP systems as an IT business analyst.

Requesting a sql file for each set in addition to the allprintings file. Ideally I'd like to be able to run an import of a set when a new set releases, rather than running a merge after importing a new allprintings set.

Some cards populate tcgplayerProductId and tcgplayerEtchedProductId when the row doesn't include the finish of that ID, namely:

1. tcgplayerEtchedProductId sometimes incorrectly populated where etched is not one of the finishes.
2. tcgplayerProductId sometimes incorrectly populated where etched is the only value in finishes.

The effect: This causes problems when I try to join to collections exports from the tcgplayer app. The collection's Product ID (which is a single column) ends up joining to rows that don't match its finish. This issue might be limited to some cards in the SNC and/or CLB sets, but I can't recall.

My workaround: I used the two queries below to identify the cards records and update the incorrectly populated Product ID to NULL. My queries also verify that the ID exists on at least one other correct cards record.

/*** Set tcgplayerEtchedProductId = NULL when incorrectly populated on non-etched row. ***/
SELECT c.scryfallOracleId ,c.tcgplayerProductId ,c.tcgplayerEtchedProductId ,c.finishes ,c.*
--UPDATE c SET c.tcgplayerEtchedProductId = NULL
FROM dbo.cards c
WHERE c.finishes NOT LIKE '%etched%'
	AND c.tcgplayerEtchedProductId IS NOT NULL
	AND EXISTS ( /*** verifies that this etched ID is correctly populated on an etched row ***/
			SELECT 1
			FROM dbo.cards x
			WHERE x.finishes LIKE '%etched%'
			AND x.tcgplayerEtchedProductId = c.tcgplayerEtchedProductId
			)
ORDER BY c.scryfallOracleId ,c.tcgplayerProductId ,c.tcgplayerEtchedProductId ,c.finishes
;


/*** Set tcgplayerProductId = NULL when incorrectly populated on an etched-only row. ***/
SELECT c.scryfallOracleId ,c.tcgplayerProductId ,c.tcgplayerEtchedProductId ,c.finishes ,c.*
--UPDATE c SET c.tcgplayerProductId = NULL
FROM dbo.cards c
WHERE c.finishes = 'etched'
	AND c.tcgplayerProductId IS NOT NULL
	AND EXISTS ( /*** verifies that this non-etched ID is correctly populated on a row that is not etched-only. ***/
			SELECT 1
			FROM dbo.cards x
			WHERE x.finishes <> 'etched'
			AND x.tcgplayerProductId = c.tcgplayerProductId
			)
ORDER BY c.scryfallOracleId ,c.tcgplayerProductId ,c.tcgplayerEtchedProductId ,c.finishes
;

MTGSQLive Output File
(2022-07-28 05:18:51)
MTGJSON Version: 5.2.0+20220728

Current version of https://mtgjson.com/api/v5/AllPrintings.sql.gz delivers multiple duplicate primary key errors when being imported into MySQL 8.

ERROR 1062 (23000) at line 113265: Duplicate entry '22d06541-1812-5b49-8d64-98e79286f45c' for key 'cards.uuid'

The text is cut at 255 characters, you should alter the column type to varchar in order do parse all text. For example, card "Domri, Chaos Bringer", from RNA set-

The issue #65 was closed today, but it is not resolved.
The faceName field is still missing from the foreign_data table in the sqlite file.

MTGJSON Version: 4.X.X

AllPrintings.sqlite

Description of Bug:

When you run queries against the SQLite DB file, you don't get creature information for the non-primary portion of the card, for flip cards, this relates to the information when the card is flipped, and for double-sided cards the missing data is from the back side of the card.

Steps to duplicate:
Open the SQLite db with with your preferred browser.
Then run the following search query:
SELECT distinct c.name , c.type , c.subtypes , c.text from cards as c join legalities as l on l.uuid = c.uuid join prices as p on p.uuid = c.uuid outer left join rulings as r on r.uuid = c.uuid where 1=1 and l.format = 'commander' and l.status = 'Legal' and c.type like '%creature%' and c.subtypes like '%werewolf%' order by 1 asc ;

And compare the output with the following Gathering search URL:
Search Link

You'll notice that Gatherer has double the number of results returned.

The difference between the result sets is that the DB isn't returning any results for the non-primary portion of flip or double-sided cards.

A number of the csv files give strange values for the expected types.

for example, booleans like "isNonFoilOnly" in sets.csv appears to be floating point with 1.0 used to denote TRUE

As another example, columns serialized from dictionaries like "relatedCards" from cards.csv appear as a stringified Python dictionary and cannot be easily deserialized back to the desired data

I've had to force the open command to utf-8, it was not reading the file in windows 10. My python setup is anaconda.
i've had to change line 202 to the following: "json_data = json.load(input_file.open("r", encoding='utf-8'))"

I've downloaded the AllSets Sqlite database from the MTGJSON download page. I note that the set_translations table is empty and there is no translations table.

MTGJSON Version: 4.6.1

Description of Bug:

mcmId, mcmMetaId, mtgoFoilId, mtgoId, multiverseId, tcgplayerProductId are incorrectly written as floats in the CSV file cards.csv (and possibly other files too). This is causing problems when importing data into a table where the format is expected (and when the docs list them) to be integers.