Repository: niftycode/imessage_reader

Issue:
Add ability to filter sent/received messages
Awesome app! Thanks for putting it together. I've been able to access both the excel and sqlite output it creates to look at my messages. My only problem (right now) is that I can't figure out a way to tell if I sent or received the message. I can filter by `sender` to isolate conversations to an individual text thread but without any context it's hard to tell if I sent the message or received it.

You might be able to use the `is_from_me` column on the `message` table in the original database to add a flag or some logic on it. I'm going to keep looking into a little myself but thanks again for putting this together!

Discussion:

Patch:
diff --git a/imessage_reader/create_sqlite.py b/imessage_reader/create_sqlite.py
index 2c2aedc..1bf6d24 100644
--- a/imessage_reader/create_sqlite.py
+++ b/imessage_reader/create_sqlite.py
@@ -5,7 +5,7 @@
 Create a SQLite3 database containing iMessage data (user id, text, date, service)
 Python 3.8+
 Date created: April 30th, 2021
-Date modified: August 7th, 2021
+Date modified: August 28th, 2021
 """
 
 import sqlite3
@@ -41,11 +41,12 @@ class CreateDatabase:
         message TEXT,
         date TEXT,
         service TEXT,
-        destination_caller_id TEXT)''')
+        destination_caller_id TEXT, 
+        is_from_me TEXT)''')
 
         for data in self.imessage_data:
-            cur.execute('''INSERT INTO Messages (user_id, message, date, service, destination_caller_id)
-                VALUES(?, ?, ?, ?, ?)''', (data.user_id, data.text, data.date, data.service, data.account))
+            cur.execute('''INSERT INTO Messages (user_id, message, date, service, destination_caller_id, is_from_me)
+                VALUES(?, ?, ?, ?, ?, ?)''', (data.user_id, data.text, data.date, data.service, data.account, data.is_from_me))
 
         conn.commit()
         cur.close()
diff --git a/imessage_reader/fetch_data.py b/imessage_reader/fetch_data.py
index 30501e1..6e6f0f2 100644
--- a/imessage_reader/fetch_data.py
+++ b/imessage_reader/fetch_data.py
@@ -7,7 +7,7 @@ Python 3.8+
 Author: niftycode
 Modified by: thecircleisround
 Date created: October 8th, 2020
-Date modified: August 6th, 2021
+Date modified: August 28th, 2021
 """
 
 import sys
@@ -28,6 +28,7 @@ class MessageData:
     date: str
     service: str
     account: str
+    is_from_me: str
 
     def __str__(self):
         """
@@ -37,7 +38,8 @@ class MessageData:
                f"message: {self.text}\n" \
                f"date: {self.date}\n" \
                f"service: {self.service}\n" \
-               f"destination caller id: {self.account}\n"
+               f"destination caller id: {self.account}\n "\
+               f"is_from_me: {self.is_from_me}\n"
 
 
 # noinspection PyMethodMayBeStatic
@@ -55,7 +57,8 @@ class FetchData:
               "datetime((date / 1000000000) + 978307200, 'unixepoch', 'localtime')," \
               "handle.id, " \
               "handle.service, " \
-              "message.destination_caller_id " \
+              "message.destination_caller_id, " \
+              "message.is_from_me "\
               "FROM message " \
               "JOIN handle on message.handle_id=handle.ROWID"
 
@@ -77,7 +80,7 @@ class FetchData:
 
         data = []
         for row in rval:
-            data.append(MessageData(row[2], row[0], row[1], row[3], row[4]))
+            data.append(MessageData(row[2], row[0], row[1], row[3], row[4], row[5]))
 
         return data
 
@@ -137,6 +140,8 @@ class FetchData:
         dates = []
         service = []
         account = []
+        is_from_me = []
+
 
         for data in fetched_data:
             users.append(data.user_id)
@@ -144,7 +149,8 @@ class FetchData:
             dates.append(data.date)
             service.append(data.service)
             account.append(data.account)
+            is_from_me.append(data.is_from_me)
 
-        data = list(zip(users, messages, dates, service, account))
+        data = list(zip(users, messages, dates, service, account, is_from_me))
 
         return data
diff --git a/imessage_reader/write_excel.py b/imessage_reader/write_excel.py
index 47412f9..3f88a6d 100644
--- a/imessage_reader/write_excel.py
+++ b/imessage_reader/write_excel.py
@@ -6,7 +6,7 @@
 Write Excel file containing iMessage data (user id, text, date, service, account)
 Python 3.8+
 Date created: October 1st, 2020
-Date modified: August 7th, 2021
+Date modified: August 28th, 2021
 """
 
 from datetime import datetime
@@ -40,6 +40,7 @@ class ExelWriter:
         dates = []
         services = []
         accounts = []
+        is_from_me = []
 
         for data in self.imessage_data:
             users.append(data.user_id)
@@ -47,6 +48,7 @@ class ExelWriter:
             dates.append(data.date)
             services.append(data.service)
             accounts.append(data.account)
+            is_from_me.append(data.is_from_me)
 
         # Call openpyxl.Workbook() to create a new blank Excel workbook
         workbook = openpyxl.Workbook()
@@ -75,6 +77,9 @@ class ExelWriter:
         sheet['E1'] = 'Destination Caller ID'
         sheet['E1'].font = bold16font
 
+        sheet['F1'] = 'Is From Me'
+        sheet['F1'].font = bold16font
+
         # Write users to 1st column
         users_row = 2
         for user in users:
@@ -105,6 +110,12 @@ class ExelWriter:
             sheet.cell(row=account_row, column=5).value = account
             account_row += 1
 
+        # Write is_from_me to 6th column
+        is_from_me_row = 2
+        for from_me in is_from_me:
+            sheet.cell(row=is_from_me_row, column=6).value = from_me
+            is_from_me_row += 1
+
         # Save the workbook (excel file)
         try:
             workbook.save(self.file_path + f'iMessage-Data_{datetime.now().strftime("%Y-%m-%d")}.xlsx')


Test patch:
diff --git a/tests/test_create_sqlite.py b/tests/test_create_sqlite.py
index a0a3f35..673a51c 100644
--- a/tests/test_create_sqlite.py
+++ b/tests/test_create_sqlite.py
@@ -18,7 +18,8 @@ def message_data_one_row():
                                      text='Hello Max!',
                                      date='2021-04-11 17:02:34',
                                      service='iMessage',
-                                     account='+01 555 17172')]
+                                     account='+01 555 17172',
+                                     is_from_me = 1)]
     return message_data_list
 
 
diff --git a/tests/test_message_data.py b/tests/test_message_data.py
index fe8acf8..5b99916 100644
--- a/tests/test_message_data.py
+++ b/tests/test_message_data.py
@@ -14,7 +14,8 @@ def message_data_one_row():
                        'Hello!',
                        '2020-10-27 17:19:20',
                        'SMS',
-                       '+01 555 17172')
+                       '+01 555 17172',
+                       1)
 
 
 @pytest.fixture(scope='function')
@@ -31,17 +32,19 @@ def initialize_db(tmpdir):
     text        TEXT UNIQUE,
     date        TEXT UNIQUE,
     service     TEXT UNIQUE,
-    account     TEXT UNIQUE
+    account     TEXT UNIQUE,
+    is_from_me  INTEGER
     );
     ''')
 
-    cur.execute('''INSERT OR IGNORE INTO message(user_id, text, date, service, account)
-        VALUES ( ?, ?, ?, ?, ?)''',
+    cur.execute('''INSERT OR IGNORE INTO message(user_id, text, date, service, account, is_from_me)
+        VALUES ( ?, ?, ?, ?, ?, ?)''',
                 ('max@mustermann.de',
                  'Hello Kendra!',
                  '2020-10-27 17:19:20',
                  'iMessage',
-                 '+01 555 17172'))
+                 '+01 555 17172',
+                 1))
 
     conn.commit()
 
@@ -54,7 +57,7 @@ def test_message_data(message_data_one_row):
 
 
 def test_db_data(initialize_db):
-    sql_command = 'SELECT user_id, text, date, service, account from message'
+    sql_command = 'SELECT user_id, text, date, service, account, is_from_me from message'
     rval = common.fetch_db_data(initialize_db, sql_command)
     assert(isinstance(rval, list))
     assert(isinstance(rval[0][0], str))
@@ -62,3 +65,4 @@ def test_db_data(initialize_db):
     assert (isinstance(rval[0][2], str))
     assert (isinstance(rval[0][3], str))
     assert (isinstance(rval[0][4], str))
+    assert (isinstance(rval[0][5], int))
diff --git a/tests/test_write_excel.py b/tests/test_write_excel.py
index 8ba4092..aaa6816 100644
--- a/tests/test_write_excel.py
+++ b/tests/test_write_excel.py
@@ -19,7 +19,8 @@ def message_data_one_row():
                                      text='Hello!',
                                      date='2020-10-27 17:19:20',
                                      service='SMS',
-                                     account='+01 555 17172')]
+                                     account='+01 555 17172',
+                                     is_from_me=1)]
     return message_data_list