WhatsApp-Chat-Exporter: No such table: messages (Edit: Support for WhatsApp databases with "message" table)

__main__.py did not include from Whatsapp_Chat_Exporter import extract_new as extract. Replaced the files in \site-packages\Whatsapp_Chat_Exporter manually with the ones from the branch now and ran the script again. Now being faced with this

╰─ wtsexporter -a
Gathering contacts...(97)
Traceback (most recent call last):
  File "C:\Users\******\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\Users\******\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "C:\Users\******\AppData\Local\Programs\Python\Python310\Scripts\wtsexporter.exe\__main__.py", line 7, in <module>
  File "C:\Users\******\AppData\Local\Programs\Python\Python310\lib\site-packages\Whatsapp_Chat_Exporter\__main__.py", line 184, in main
    messages(db, data)
  File "C:\Users\******\AppData\Local\Programs\Python\Python310\lib\site-packages\Whatsapp_Chat_Exporter\extract_new.py", line 206, in messages
    c.execute("""SELECT jid.raw_string as key_remote_jid,
sqlite3.OperationalError: no such column: message_media.media_caption

It should be fixed in the latest commit: https://github.com/KnugiHK/Whatsapp-Chat-Exporter/commit/d3892a4e4f4f0b533107f871a23de22153cc879a.

I’ll try to reinstall WhatsApp and try it again, but it seems like the database scheme is changing. The new messages table has the following schema:

I can produce the same result on 2.22.7.73 and “messages” table no longer exist.

@Takepy As I can reproduce your situation now, I will work on supporting “message” table.

I created a new branch called “message_table” which support message table directly from wtsexporter command. Feel free to give it a try but please expect bugs from the branch. Bug reports are welcome and should be reported in this issue.

Also, big thank for the SQL statement written by @ulno. For the replacement of key_remote_jid, since sender_jid_row_id could be 0, instead of treating it as the key_remote_jid, I joined the chat and jid table together to obtain the accurate key_remote_jid.

I am curious about how WhatsApp decides which table to store messages 🤨. Is “message” a legacy table? Or “messages” is the legacy one?

“messages” is the legacy one, please read https://thebinaryhick.blog/2022/06/09/new-msgstore-who-dis-a-look-at-an-updated-whatsapp-on-android/

Hi, sorry for the delay; I didn’t have the old backup available immediately.

1. Nope, the "This message is not supported" thing does not display in the resulting HTML when converting an old database. The chat is actually complete, so maybe it is the "message" saying: "Messages and calls are end-to-end encrypted..."

2. The 'main' branch doesn't have the '-s' flag available at all.

Metadata is also treated as messages in the database. But since it is not the main focus when it comes to exporting chats, metadata may show as “not supported” unless handled like changes of the group name. Therefore, I will consider it not a bug if the chat is complete.

I will start preparing for the release when more tests are being performed.

Sorry, it took a little bit longer until I had time to try it out.

No problem!

So I’m guessing my backup is just too old for it to work then? 😦

I think so. I would like to add support for older version. However, I don’t have such database, hence, I am not able to do it. If you have time and you want the script to work with your database, you can take a look inside the database and fine tune the script. Anyway, I will keep my eyes on the opportunity for supporting old databases. Thanks for your report!

I switched to dev branch. “Copying media directory…” printed in the terminal. I think the problem is not related to #25 cause I don’t have the media folder so it’s not a problem of moving instead of copying, it’s creating two copies of the same folder from zero. I’m working only with the msgstore.db file.

vCards will be exported to ./WhatsApp/vCards and copied to the output directory by default. If you don’t want two identical folders, try -c (–move-media) flag.

Hello everyone! We are finally moving to the last step before releasing this update.

I merged the logic for processing old and new schema in extract.py in commit fb88124. As it is not a commit specifically for the message table, it will not be merged into the message_table branch, and the branch will not be updated from now on. And changes will be committed to the dev branch as usual.

Feel free to try it, especially for those with databases with both message and messages tables, to see if there is any compatibility issue.

I had to cherry-pick the fix for https://github.com/KnugiHK/Whatsapp-Chat-Exporter/issues/32 and then it worked perfectly. Thank you for making this tool!

➜  Whatsapp-Chat-Exporter git:(message_table)$ git cherry-pick 2ba5571
➜  Whatsapp-Chat-Exporter git:(message_table)$ pip install '.[android_backup]'

I checked out the message_table branch and used it to extract my newer ‘message’ DB successfully. All conversations including group conversations were converted to HTML. Many thanks @KnugiHK !

The new branch worked on my side – thanks a lot ! Although all medias are missing from the htmls (i tried both with and without the -e flag). Thanks a lot for your work, your tool is great.

I played with it a bit yesterday and replaced the main query in extract.py with this:

SELECT message.sender_jid_row_id as key_remote_jid,
                        message._id,
                        message.from_me as key_from_me,
                        message.timestamp,
                        message.text_data as data,
                        message.status,
                        message_future.version as edit_version,
                        message_thumbnail.thumbnail as thumb_image,
                        message_media.file_path as remote_resource,
                        message_media.mime_type as media_wa_type,
                        message_location.latitude,
                        message_location.longitude,
                        message_quoted.key_id as quoted,
                        message.key_id,
                        message_quoted.text_data as quoted_data,
                        message_media.media_caption
                 FROM message
                    LEFT JOIN message_quoted
                        ON message_quoted.message_row_id = message._id
                    LEFT JOIN message_location
                        ON message_location.message_row_id = message._id
                    LEFT JOIN message_media
                        ON message_media.message_row_id = message._id
                    LEFT JOIN message_thumbnail
                        ON message_thumbnail.message_row_id = message._id
                    LEFT JOIN message_future
                        ON message_future.message_row_id = message._id
                    WHERE key_remote_jid <> '-1';

That brings me past the missing messages table, but will clash in extract.py:240 trying to match the remote id to something in the big data dictionary. So, I assume me trying to replace key_remote_jid with sender_jid_row_id might go into the right direction, but might need some more joints to actually turn things into a phone number.

There are also a messages_view and a chats_view now that seem to match some more tables with each other. Did anybody else figure some dependencies based on their connections out?

I will have a look at the clash later.

I played with it a bit yesterday and replaced the main query in extract.py with this:

SELECT message.sender_jid_row_id as key_remote_jid,
                        message._id,
                        message.from_me as key_from_me,
                        message.timestamp,
                        message.text_data as data,
                        message.status,
                        message_future.version as edit_version,
                        message_thumbnail.thumbnail as thumb_image,
                        message_media.file_path as remote_resource,
                        message_media.mime_type as media_wa_type,
                        message_location.latitude,
                        message_location.longitude,
                        message_quoted.key_id as quoted,
                        message.key_id,
                        message_quoted.text_data as quoted_data,
                        message_media.media_caption
                 FROM message
                    LEFT JOIN message_quoted
                        ON message_quoted.message_row_id = message._id
                    LEFT JOIN message_location
                        ON message_location.message_row_id = message._id
                    LEFT JOIN message_media
                        ON message_media.message_row_id = message._id
                    LEFT JOIN message_thumbnail
                        ON message_thumbnail.message_row_id = message._id
                    LEFT JOIN message_future
                        ON message_future.message_row_id = message._id
                    WHERE key_remote_jid <> '-1';

That brings me past the missing messages table, but will clash in extract.py:240 trying to match the remote id to something in the big data dictionary. So, I assume me trying to replace key_remote_jid with sender_jid_row_id might go into the right direction, but might need some more joints to actually turn things into a phone number.

There are also a messages_view and a chats_view now that seem to match some more tables with each other. Did anybody else figure some dependencies based on their connections out?

I am curious about how WhatsApp decides which table to store messages 🤨. Is “message” a legacy table? Or “messages” is the legacy one?

I’m not certain, using the outdated WhatsApp version both tables exist, in the more recent version (downloaded from whatsapp.com yesterday) only the message table existed.

Edit: Just checked, in my current setup (outdated version) messages are only stored in the messages table (which works awesome with your script!) and the message table appears to be empty.

I’ll give it a try later and report back.