To edit commandline Anki flashcards in MySQL

I need to make a small change in 77777 Anki flashcards, preferentially programmatically, not by using their replace tool which is not sufficient. I imported data originally there as CSV files where data is easy to modify. I got an answer from their support: they are using MySQL as a database:

Well, for starters, I assume one would need an understanding of reading and writing to a MySQL database. If this isn't something you know, I would just stop there.

I would like to know their database schema, for instance. Example pseudocommand which I would like to run on one Deck

sed s/;/1)/ [anki-deck's-cards]

where matching only first entries of the newlines. Actually, the requirement is to do some simple arithmetics. I do not know the connector details of their MySQL. I would like to get an overview of their MySQL details.

How can you edit cards in Deck in Anki Flashcards?


The apkg files contain an embedded sqlite database file. For instance the deck Capital Cities of South America is downloaded as Capital_Cities_of_South_America.apkg which actually is a zip file. Unzipping this will extract several files

$ unzip ../Capital_Cities_of_South_America.apkg
Archive:  ../Capital_Cities_of_South_America.apkg
  inflating: collection.anki2        
 extracting: 0                       
 extracting: 1                       
 extracting: 2                       
 extracting: 3                       
 extracting: 4                       
 extracting: 5                       
 extracting: 6                       
 extracting: 7                       
 extracting: 8                       
 extracting: 9                       
 extracting: 10                      
 extracting: 11                      
  inflating: media                   
$

but the interesting one is collection.anki2 which is a sqlite file.

$ sqlite3 collection.anki2   
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> .tables
cards   col     graves  notes   revlog
sqlite> .dump notes
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE notes (
    id              integer primary key,   /* 0 */
    guid            text not null,         /* 1 */
    mid             integer not null,      /* 2 */
    mod             integer not null,      /* 3 */
    usn             integer not null,      /* 4 */
    tags            text not null,         /* 5 */
    flds            text not null,         /* 6 */
    sfld            integer not null,      /* 7 */
    csum            integer not null,      /* 8 */
    flags           integer not null,      /* 9 */
    data            text not null          /* 10 */
);
INSERT INTO "notes" VALUES(1475868715241,'P1Dh$4r;;P',1475850444584,1476205558,13,' South_America capitals ','Capital of French Guiana?Cayenne<div><br /></div><div><img src="053_Hood_135.jpg" /></div>','Capital of French Guiana?',511387741,0,'');
INSERT INTO "notes" VALUES(1476020723949,'D3h>i:~1(}',1475850444584,1476205484,13,' South_America capitals ','Capital of Brazil?Brasilia<div><br /></div><div><img src="brasilia-006.jpg" /></div>','Capital of Brazil?',139249191,0,'');
INSERT INTO "notes" VALUES(1476020740304,'I=w5}{qq&z',1475850444584,1476021426,4,' South_America capitals ','Capital of Argentina?Buenos Aires','Capital of Argentina?',4279953785,0,'');
INSERT INTO "notes" VALUES(1476020761836,'PpW<QQ2n;<',1475850444584,1476205269,13,' South_America capitals ','Capital of Chile?Santiago<div><br /></div><div><img src="santiago.jpg" /><br /><div><br /></div><div><br /></div></div>','Capital of Chile?',857145718,0,'');
INSERT INTO "notes" VALUES(1476020893034,'AQILm-om{w',1475850444584,1476205286,13,' South_America capitals ','Capital of Suriname?Paramaribo<div><br /></div><div><img src="paste-68513318306560.jpg" /><br /><div><br /></div><div><br /></div></div>','Capital of Suriname?',3475624840,0,'');
INSERT INTO "notes" VALUES(1476021133011,'ssX|ur}vz;',1475850444584,1476205460,13,' South_America capitals ','Capital of Guyana?Georgetown<div><br /></div><div><img src="georgetown-guyana.jpg" /></div>','Capital of Guyana?',1383243232,0,'');
INSERT INTO "notes" VALUES(1476021159549,'d_}(&UOcN0',1475850444584,1476205474,13,' South_America capitals ','Capital of Venezuela?Caracas<div><br /></div><div><img src="CaracasAvila.jpg" /></div>','Capital of Venezuela?',3460219780,0,'');
INSERT INTO "notes" VALUES(1476021305082,'hF6^dUFOU_',1475850444584,1476205202,13,' South_America capitals ','Capital of Colombia?Bagota<div><br /></div><div><!--anki--><img src="asuncion-paraguay.jpg" /></div>','Capital of Colombia?',3745298320,0,'');
INSERT INTO "notes" VALUES(1476021329578,'BF<CKCw&@X',1475850444584,1476021353,4,' South_America capitals ','Capital of Ecuador?Quito','Capital of Ecuador?',93805795,0,'');
INSERT INTO "notes" VALUES(1476021353212,'s-x)P3;UJ+',1475850444584,1476205337,13,' South_America capitals ','Capital of Peru?Lima<div><br /></div><div><img src="lima-miraflores-rw.jpg" /></div>','Capital of Peru?',1899195448,0,'');
INSERT INTO "notes" VALUES(1476021369814,'p~,SBD&um#',1475850444584,1476205352,13,' South_America capitals ','Capital of Bolivia?La Paz<div><br /></div><div><img src="La_Paz_Skyline.jpg" /></div>','Capital of Bolivia?',2475357406,0,'');
INSERT INTO "notes" VALUES(1476021495601,'h(+l3fJs48',1475850444584,1476205311,13,' South_America capitals ','Capital of Uruguay?Montevideo<div><br /></div><div><img src="montevideo-uruguay.jpg" /></div><div><br /></div><div>[sound:rec1476205311.mp3]</div>','Capital of Uruguay?',3423412928,0,'');
INSERT INTO "notes" VALUES(1476021520017,'g[QWvAO[:]',1475850444584,1476205181,13,' South_America capitals ','Capital of Paraguay?Asuncion<div><br /></div><div><img src="asuncion-paraguay.jpg" /></div><div><br /></div><div>[sound:rec1476205181.mp3]</div>','Capital of Paraguay?',3088135100,0,'');
CREATE INDEX ix_notes_usn on notes (usn);
CREATE INDEX ix_notes_csum on notes (csum);
COMMIT;
sqlite> 

At this point you are free to update the entries with standard sql commands.