This is a script used to create a SQLite database from exported facebook messenger JSON data.
- Request and download a collection of messages in JSON format from Facebook
- Clone the repo
- Set the following environment variables, or alternatively create a file
.envfile in the root directory with the configuration:
CONVERSATION_PATH=/PATH/TO/ROOT/CONVERSATION/FOLDER/messages/inbox/chat/
DATABASE_NAME=database.db
- Run the script:
python main.py
A more in-depth description of the JSON-schema used for the data export can be found here.
These are the tables and corresponding fields generated in the database by the script.
| Field | Comment |
|---|---|
| message_id | Corresponds to a message id in the messages table. |
| uri | URI to audio file resource. |
| creation_timestamp |
| Field | Comment |
|---|---|
| message_id | Corresponds to a message id in the messages table. |
| uri | URI to gif resource. |
| Field | Comment |
|---|---|
| message_id | Unique message id. |
| sender_name | Person who sent the message. |
| timestamp | When the message was sent. |
| content | The actual message sent. |
| type | Can one of 5 values: Generic, Share, Subscribe, Unsubscribe or Call |
| is_unsent | Is true if the message was removed by the sender. |
| photos_count | The number of photos sent. |
| videos_count | The number of videos sent. |
| sticker_count | The number of stickers sent. |
| audioclip_count | The number of audioclips sent. |
| gif_count | The number of gifs sent. |
| files_count | The number of files sent. |
| users_count | The number of users who were added or removed. Only applicable of message types Unsubscribe and Subscribe. |
| call_duration | The duration of a call. Only applicable for message type Call. |
| reaction_count | The number of reactions received for the message. |
NOTE: The JSON files participants object will only contain the participants at the time of the export. This table however will contain all users who've ever been in the conversation.
| Field | Comment |
|---|---|
| participant_id | Unique participant id |
| name | Name of participant |
| Field | Comment |
|---|---|
| message_id | Corresponds to a message id in the messages table. |
| uri | URI to photo. |
| creation_timestamp |
| Field | Comment |
|---|---|
| message_id | Message id which the reaction was made on. |
| actor | Name of person who reacted on the message. |
| reaction | Emoji depicting which reaction was made. |
| Field | Comment |
|---|---|
| message_id | Corresponds to a message id in the messages table. |
| link | Link which was shared. |
| share_text | Used when sharing locations. |
| Field | Comment |
|---|---|
| message_id | Corresponds to a message id in the messages table. |
| subscription_type | Can be either Subscribe or Unsubscribe depending on adding or removing user from conversation. |
| user | The user who was removed or added to the conversation. The message table contains information on who removed/added the user in this table. |
| Field | Comment |
|---|---|
| message_id | Corresponds to a message id in the messages table. |
| uri | URI to video. |
| creation_timestamp | |
| thumbnail_uri | URI to video (same as uri field as of today). |