Support opening apkg files out of the box

[Blocked]

• Your operating system: iPython/Python3/Termux/Android
• A minimal working example (MWE) of code that can be used to reproduce your problem (where applicable)

from ankipandas import Collection as c
a = c("/data/data/com.termux/files/home/.venv/test.apkg")
a.cards

• what you expected: See Cards

• what happened instead: Thrown a error on a.cards

• Last log messages of AnkiPandas

/data/data/com.termux/files/usr/lib/python3.9/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1735
   1736             ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}")
-> 1737             raise ex from exc
   1738
   1739     @staticmethod

DatabaseError: Execution failed on sql 'SELECT * FROM cards': file is not a database

• sqlite3.sqlite_version returns 3.36.0 in iPython
• Can confirm file is present and is a valid apkg file
• SO says this may be due to incompatibility between the sqlite used to create the db and the sqlite used to access the database

[klieret]

Hi @Blocked. Thanks for opening the issue 😊.

I don't really have an answer for your ight away, but this error seems to be relatively unrelated to most of the code from AnkiPandas and should already occur with the following very simple snippet:

import sqlite3
import pandas as pd

connection = sqlite3.connect("/data/data/com.termux/files/home/.venv/test.apkg")
pd.read_sql_query("SELECT * FROM cards", connection)

[klieret]

Perhaps as an even simpler test to see if this is also unrelated to pandas:

import sqlite3

con = sqlite3.connect("/data/data/com.termux/files/home/.venv/test.apkg")
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())
cursor.close()
con.close()

this should print all tables in the file.

[klieret]

You could also directly try to use the command line interface of sqlite3 to enter SELECT name FROM sqlite_master WHERE type='table'; or SELECT * FROM cards and see if that works.

[Blocked]

@klieret Thanks 👍 Only sqlite3 from commandline seems to work. But, that too, there's no cards in apkg, but zip:

sqlite3
sqlite> .open test.apkg
sqlite> .tables
zip
sqlite> select * from cards;
Error: no such table: cards

Select * from zip does provide scrambled data, but those actually contain the card data. And I still get the database error:file is not a database on pd.read_sql_query("SELECT * FROM cards", connection) in pandas.

Any direction?

[klieret]

Hmm, very interesting. Where is the anki database from? Is it from AnkiDroid? Then it looks like AnkiDroid is using a different format for its database than Anki desktop.

I've honestly never considered that someone might want to use this with AnkiDroid, so I've never tested this. I'm also curious about your use case ;)

[klieret]

@allcontributors please add @Blocked for bug

[Blocked]

I don't think there's any db structure difference. The apkg files are from https://ankiweb.net. eg: https://ankiweb.net/shared/info/965641886 Also, I can't find any difference in database structures in ankidroid here: https://github.com/ankidroid/Anki-Android/wiki/Database-Structure

My intention is to simply to manipulate cards on droid itself.

[allcontributors]

@klieret

I've put up a pull request to add @Blocked! 🎉

[klieret]

Okay, I see this too now (using this tiny deck).

So your use case then is to manipulate the cards before (!) you import them to your profile?
Because what this tells me right now is that the format of shared decks seems to be different from that of the profile of a person (which makes sense, right?)

[klieret]

OK, but it seems like this is actually just a zip file. So if you unzip the file, you will get pictures, as well as a human readable file media and a database collection.anki2 which seems to be something that AnkiPandas should be able to read.

[klieret]

I've actually just tested with the deck and it works all fine :)

c = Collection("collection.anki2")
c.notes
c.cards
c.revs

[Blocked]

I don't think there's a difference between shared deck and exported deck. I checked a exported deck just now and I get the same zip table.

[klieret]

Interesting. So usually AnkiPandas works on the collection directly in the config directory of Anki (so not exported). I'll add a note about this to the documentation.

[Blocked]

@klieret Thank you!! It works after extracting collection.anki2

[Blocked]

If you think it'll be useful, consider adding direct support for shared deck apkg files as well(Testing, unzipping, extracting - modifying- and rezipping).

Thanks again👍

[klieret]

I will definitely add example code in the next few days. zipfile from the standard library provides good support for opening files from a zip archive and writing them back, so maybe there is a relatively elegant way to handle this.

I am still not sure if I can add the read and especially write function to .apkg files directly to AnkiPandas without complicating things too much, but let's see :)

[rpryzant]

Love this thread as I was experiencing the same issue! Was this ever added to the docs?

e.g. I can't find it here https://ankipandas.readthedocs.io/en/latest/troubleshooting.html

Would be super useful to include native "exported deck" support

[klieret]

Thanks for the ping. Yes, indeed this wasn't added yet. I hope I can find some time this week!

[klieret]

Hi @rpryzant @Blocked I've implemented a first version of this in #139 (actually adding both read and write support). Do you want to test it? I still have to add some unit tests, but if you want you can already check out the branch and install from there for beta testing.

[rpryzant]

Thanks so much @klieret !

Hmm this isn't working for me, my Anki deck (attached here:https://www.dropbox.com/s/l4hqbuckxn86le7/anki-raw-3-23-23.apkg?dl=0) only worked when I extracted it first...

Code:

col = Collection('anki-raw-3-23-23/collection.anki21', user="User 1")
print(col.cards)

col = Collection('anki-raw-3-23-23.apkg', user="User 1")
print(col.cards)
quit()

Output:

[rpryzant]

The collection from the extracted deck has cards, but the collection from the raw deck only has one broken card. I hope that's helpful!

[klieret]

Thanks for the feedback, I hope I get to test the code against the example you submitted next week

[klieret]

Hi @rpryzant. Sorry for the late reply. I looked at your file and for me it simply seems to be corrupted.

Because I see the following output:

nid
1679532946726  M:Jo^S<Q/$  1679532946    -1    []  [This file requires a newer version of Anki., ]  Basic

I do not see any issue when I use apkgs that I find on the internet.