This can be useful when doing analysis on chats with multiple people in them. Similarly, the message table also includes a chat_id that maps each message back to unique chat. # get the handles to apple-id mapping table handles = pd.read_sql_query("select * from handle", conn) # and join to the messages, on handle_id messages.rename(columns=, inplace = True) merge_leve_1 = temp = pd.merge(messages], handles], on ='handle_id', how='left') Adding a chat id In order to map the handle_id back to the Apple-id we can use a table in the database (appropriately) named handle and join on handle_id.
#My sms mac download code
After a little exploration I found that the handle_id is a code for each phone number or Apple-id that you have had a conversation with. It has a text field with the actual sent or received message, a date field (more on that below) and a handle id. We hit bingo! The message table indeed seems to hold all the saved iMessages. # get the 10 entries of the message table using pandas messages = pd.read_sql_query("select * from message limit 10", conn) Getting the message text and phone number I do that by transferring the table into a pandas dataframe, a type of file that is much easier to explore and manipulate for data analyis projects. Let’s explore the message table because that’s the one that sounds most promising to hold our iMessages.
I found that there are a few tables in the database including one called message and others names chat, handle and attachment. connection code import sqlite3 import pandas as pd # substitute username with your username conn = nnect('/Users/username/Library/Messages/chat.db') # connect to the database cur = conn.cursor() # get the names of the tables in the database cur.execute(" select name from sqlite_master where type = 'table' ") for name in cur.fetchall(): print(name)Ībove we connect to the database and explore what tables are in there. In this tutorial, I’m using Python and the amazing pandas module to connect to the database, explore the tables and data it holds and then read that data from the appropriate tables. Much like larger or enterprise-grade databases you can connect and access the data in the database in a variety of ways. What’s a database?Ī very simple way to understand what a database file is is to think of it a a folder that contains a bunch of excel-like tables. Notice how some folders appear with a greyed icon these are the normally hidden folders. Once that works, you can go and find the Messages folder, (which contains the chat.db database) within the Library folder as shown in the image below. If for some reason that doesn’t work, you can also open the Terminal app and simply type the following. You can make the hidden folders appear by simultaneously pressing the Command, the Shift and the dot keys: “Command+Shift+.”. Hidden folders are folders that by default don’t appear to the user, usually holding files that a casual user doesn’t have to interact with, such as system-related files. You can usually find your username folder on the side bar of the finder. The iMessage history that powers your Messages app is stored in a database file in your computer’s hard drive, in a hidden folder named Library which, in turn, is in your username folder. If you just want to see the good stuff you can find the notebook with the minimal code to extract and prepare the data here.
Green bubble messages unfortunately are not captured. Note: this approach only works for Macs (laptops and desktops) and only gets your iMessage history.
This is a somehow technical guide for extracting all the iMessage data in your computer’s hard drive and putting them in an analysis-friendly file. Well you’re in luck! Your Apple computer stores that message history right within your grasp in a hidden folder on your hard drive! When using the Messages app, you can see all your history of messages on that device and maybe (like me) you have wondered where exactly is that data stored? Can you access it in a format that is easy to analyze? Wouldn’t it be great to be able to see how many messages you have sent per day? Or read the first message you have ever sent to your friends? If you use the Messages app on your Apple computer then you probably have connected your Apple-Id to that computer in order to send and receive iMessages across all your Apple devices (iPhone, iPad, computer).
0 kommentar(er)
