-
本工具設計的初衷是用來獲取微信賬號的相關資訊並解析PC版微信的資料庫。
-
程式以 Python 語言開發，可讀取、解密、還原微信資料庫並幫助使用者檢視聊天記錄，還可以將其聊天記錄匯出為csv、html等格式用於AI訓練，自動回覆或備份等等作用。下面我們將深入探討這個工具的各個方面及其工作原理。
-
本專案僅供學習交流使用，嚴禁用於商業用途或非法途徑，任何違反法律法規、侵犯他人合法權益的行為，均與本專案及其開發者無關，後果由行為人自行承擔。
【完整演示工具下載】
https://www.chwm.vip/index.html?aid=23
我們上一篇文章《劫持微信聊天記錄並分析還原 —— 合併解密後的資料庫（三）》已經講解了如何將解密後的微信資料庫合併成一整個.db檔案，那麼本次我們將詳細介紹一下微信資料庫的結構與各個資料庫的作用。
微信PC端各個資料庫簡述
-
說明：針對 .../WeChat Files/wxid_xxxxxxxx/Msg下的各個檔案解密後的內容進行概述。
-
未作特別說明的情況下，“聊天記錄資料”指代的資料結構上都和Multi資料夾中的完整聊天記錄資料相同或類似。
一、微信小程式相關
微信小程式的相關資料，包括但不限於：
-
你使用過的小程式 RecentWxApp
-
星標的小程式 StarWxApp
-
各個小程式的基本資訊 WAContact
用處不大，不過可以看到你使用過的小程式的名稱和圖示，以及小程式的AppID
二、企業微信相關
BizChat
企業微信聯絡人資料，包括但不限於：
-
在微信中可以訪問的企業微信會話ChatInfo
-
一部分會話的資訊ChatSession（未確認與ChatInfo的關係；這其中的Content欄位是最近一條訊息，疑似用於快取展示的內容）
-
包括群聊在內的聊天涉及的所有企業微信使用者身份資訊UsrInfo
-
該微信賬號繫結的企業微信身份MyUsrInfo
-
特別說明：未經詳細查證，這其中的聊天是否包含使用普通微信身份與企業微信使用者發起的聊天，還是隻包含使用繫結到普通微信的企業微信身份與其它企業微信身份發起的聊天。
BizChatMsg
-
企業微信聊天記錄資料，包括所有和企業微信聊天的資料。
-
與BizChat一樣，未確定涉及的範圍究竟是隻有企業微信-企業微信還是同時包含普通微信-企業微信。
-
另外，此處的訊息與Multi資料夾中真正的微信訊息不同的是在於沒有拆分資料庫。
OpenIM 字首
-
這個也是企業微信的資料，包括聯絡人、企業資訊、與企業微信聯絡人的訊息等。
-
這個是普通微信-企業微信的資料，上面biz字首的是企業微信-企業微信
-
這個不常用，而且也沒有全新的資料結構，不再詳細說了。
PublicMsg
- 看起來像是企業微信的通知訊息，可以理解為企業微信的企業應用訊息
三、微信功能相關
Emotion
顧名思義表情包相關，包括但不限於以下內容：
-
CustomEmotion：顧名思義使用者手動上傳的GIF表情，包含下載連結，不過看起來似乎有加密（內有aesKey欄位但我沒測試）
-
EmotionDes1 和 EmotionItem 應該也是類似的內容，沒仔細研究
-
EmotionPackageItem：賬號新增的表情包的集合列表（從商店下載的那種）
ps：用處不大，微信的MSG資料夾有表情包的url連結，可以直接網路獲取聊天記錄中的表情包。
Favorite
-
FavItems：收藏的訊息條目列表
-
FavDataItem：收藏的具體資料。大概可以確定以下兩點
-
即使只是簡單收藏一篇公眾號文章也會在 FavDataItem 中有一個對應的記錄
-
對於收藏的合併轉發型別的訊息，合併轉發中的每一條訊息在 FavDataItem 中都是一個獨立的記錄
-
FavTags：為收藏內容新增的標籤
Misc
- 有BizContactHeadImg和ContactHeadImg1兩張表，應該是二進位制格式的各個頭像
Sns
微信朋友圈的相關資料：
-
FeedsV20：朋友圈的XML資料
-
CommentV20：朋友圈點贊或評論記錄
-
NotificationV7：朋友圈通知
-
SnsConfigV20：一些配置資訊，能讀懂的是其中有你的朋友圈背景圖
-
SnsGroupInfoV5：猜測是舊版微信朋友圈可見範圍的可見或不可見名單
FTS（搜尋）
- 字首為 FTS 的資料庫可能都和全文搜尋（Full-Text Search）相關(就是微信那個搜尋框)
FTSContact
有一堆表
- FTSChatroom15_content 和 FTSContact15_content 分別對應的是微信“聊天”介面會展示的訊息會話（包括公眾號等）和“聯絡人”介面會出現的所有人（有的時候並不是所有聯絡人都會出現在“聊天”中），資訊包含暱稱、備註名和微訊號，也和微信支援搜尋的欄位相匹配。
FTSFavorite
搜尋收藏內容的索引
- 命名方式類似上面一條
ps：對於收藏內容透過文字搜尋，電腦版是把所有東西拼接成一個超長字串來實現的。這對於文字、連結等沒啥問題，但是對於合併轉發訊息，就會出現搜尋[圖片] 這一關鍵詞。
MultiSearchChatMsg
-
這個資料庫字首不一樣，但是看內容和結構應該還是一個搜尋相關，搜尋的是聊天記錄中的檔案
-
儲存了檔名和其所在的聊天
-
不過FTSMsgSearch18_content和SessionAttachInfo兩張表記錄數量有顯著差異，不確定是哪個少了或是怎樣。
HardLink（檔案在磁碟儲存的位置）
- 將檔案/圖片/影片的檔名指向儲存它們的資料夾名稱（例如2023-04），有用但不多。
Media
- ChatCRVoice和MediaInfo 可能為語音資訊
四、MicroMsg （聯絡人核心）
一個資料庫，不應該和分類平級，但是我認為這是分析到目前以來最核心的，因此單獨來說了。
AppInfo（表）
一些軟體的介紹，猜測可能是關於某些直接從手機APP跳轉到微信的轉發會帶有的轉發來源小尾巴的資訊
Biz 字首
與公眾號相關的內容，應該主要是賬號本身相關。
能確定的是 BizSessionNewFeeds 這張表儲存的是訂閱號大分類底下的會話資訊，包括頭像、最近一條推送等。
ChatInfo
儲存“聊天”列表中每個會話最後一次標記已讀的時間
ChatRoom 和 ChatRoomInfo
儲存群聊相關資訊
-
ChatRoom：儲存每個群聊的使用者列表（包括微訊號列表和群暱稱列表）和個人群暱稱等資訊
-
ChatRoomInfo：群聊相關資訊，主要是群公告內容，與成員無關 順便再吐槽一下，微信這個位置有一個命名出現異常的，別的表字首都是ChatRoom，而突然出現一個ChatroomTool
Contact
顧名思義，聯絡人。不過這裡的聯絡人並不是指你的好友，而是所有你可能看見的人，除好友外還有所有群聊中的所有陌生人。
-
Contact：這張表儲存的是使用者基本資訊，包括但不限於微訊號（沒有好友的陌生人也能看！）、暱稱、備註名、設定的標籤等等，甚至還有生成的各種欄位的拼音，可能是用於方便搜尋的吧
-
ContactHeadImgUrl：頭像地址
-
ContactLabel：好友標籤 ID 與名稱對照
-
ExtraBuf: 儲存位置資訊、手機號、郵箱等資訊
PatInfo
存了一部分好友的拍一拍字尾，但是隻有幾個，我記得我電腦上顯示過的拍一拍似乎沒有這麼少？
Session
真正的“聊天”欄目顯示的會話列表，一個不多一個不少，包括“摺疊的群聊”這樣子的特殊會話；資訊包括名稱、未讀訊息數、最近一條訊息等
TicketInfo
這張表在我這裡有百餘條資料，但是我實在沒搞明白它是什麼
五、FTSMSG
FTS 這一字首了——這代表的是搜尋時所需的索引。
其內主要的內容是這樣的兩張表：
-
FTSChatMsg2_content：內有三個欄位
-
docid：從1開始遞增的數字，相當於當前條目的 ID
-
c0content：搜尋關鍵字（在微信搜尋框輸入的關鍵字被這個欄位包含的內容可以被搜尋到）
-
c1entityId：尚不明確用途，可能是校驗相關
-
FTSChatMsg2_MetaData
-
docid：與FTSChatMsg2_content表中的 docid 對應
-
msgId：與MSG資料庫中的內容對應
-
entityId：與FTSChatMsg2_content表中的 c1entityId 對應
-
type：可能是該訊息的型別
-
其餘欄位尚不明確
特別地，表名中的這個數字2，個人猜測可能是當前資料庫格式的版本號。
六、MediaMSG （語音訊息）
這裡儲存了所有的語音訊息。資料庫中有且僅有Media一張表，內含三個有效欄位：
-
Key
-
Reserved0 與MSG資料庫中訊息的MsgSvrID一一對應
-
Buf silk格式的語音資料
七、MSG（聊天記錄核心資料庫）
內部主要的兩個表是 MSG 和 Name2ID
Name2ID
-
Name2ID這張表只有一列，內容格式是微訊號或群聊ID@chatroom -
作用是使MSG中的某些欄位與之對應。雖然表中沒有 ID 這一列，但事實上微信預設了第幾行 ID 就是幾（從1開始編號）。
MSG
-
localId：字面意思訊息在本地的 ID，暫未發現其功用
-
TalkerId：訊息所在房間的 ID（該資訊為猜測，猜測原因見 StrTalker 欄位），與Name2ID對應。
-
MsgSvrID：猜測 Srv 可能是 Server 的縮寫，代指伺服器端儲存的訊息 ID
-
Type：訊息型別，具體對照見表1
-
SubType：訊息型別子分類，暫時未見其實際用途
-
IsSender：是否是自己發出的訊息，也就是標記訊息展示在對話頁左邊還是右邊，取值0或1
-
CreateTime：訊息建立時間的秒級時間戳。此處需要進一步實驗來確認該時間具體標記的是哪個時間節點，個人猜測的規則如下：
-
從這臺電腦上發出的訊息：標記代表的是每個訊息點下傳送按鈕的那一刻
-
從其它裝置上發出的/收到的來自其它使用者的訊息：標記的是本地從伺服器接收到這一訊息的時間
-
Sequence：次序，雖然看起來像一個毫秒級時間戳但其實不是。這是
CreateTime欄位末尾接上三位數字組成的，通常情況下為000，如果在出現兩條CreateTime相同的訊息則最後三位依次遞增。需要進一步確認不重複範圍是在一個會話內還是所有會話。CreateTime相同的訊息則最後三位依次遞增。需要進一步確認不重複範圍是在一個會話內還是所有會話。 -
StatusEx、FlagEx、Status、MsgServerSeq、MsgSequence：這五個欄位個人暫時沒有分析出有效資訊
-
StrTalker：訊息傳送者的微訊號。特別說明，從這裡來看的話，上面的
TalkerId欄位大機率是指的訊息所在的房間ID，而非傳送者ID，當然也可能和TalkerId屬於重複內容，這一點待確認。 -
StrContent：字串格式的資料。特別說明的是，除了文字型別的訊息外，別的大多型別這一欄位都會是一段 XML 資料標記一些相關資訊。透過解析xml可以得到更多的資訊，例如圖片的寬高、語音的時長等等。
-
DisplayContent：對於拍一拍，儲存拍者和被拍者賬號資訊
-
Reserved0~6：這些欄位也還沒有分析出有效資訊，也有的欄位恆為空
-
CompressContent：字面意思是壓縮的資料，實際上也就是微信任性不想存在 StrContent 裡的資料在這裡（例如帶有引用的文字訊息等；採用lz4壓縮演算法壓縮）
-
BytesExtra：額外的二進位制格式資料
-
BytesTrans：目前看這是一個恆為空的欄位
表1：MSG.Type欄位數值與含義對照表（可能可以擴充套件到其它資料庫中同樣標記訊息型別這一資訊的欄位）
| 分類Type | 子分類SubType | 對應型別 |
|---|---|---|
| 1 | 0 | 文字 |
| 3 | 0 | 圖片 |
| 34 | 0 | 語音 |
| 43 | 0 | 影片 |
| 47 | 0 | 動畫表情（第三方開發的表情包） |
| 49 | 1 | 類似文字訊息而不一樣的訊息，目前只見到一個阿里雲盤的邀請註冊是這樣的。估計和57子類的情況一樣 |
| 49 | 5 | 卡片式連結，CompressContent 中有標題、簡介等，BytesExtra 中有本地快取的封面路徑 |
| 49 | 6 | 檔案，CompressContent 中有檔名和下載連結（但不會讀），BytesExtra 中有本地儲存的路徑 |
| 49 | 8 | 使用者上傳的 GIF 表情，CompressContent 中有CDN連結，不過似乎不能直接訪問下載 |
| 49 | 19 | 合併轉發的聊天記錄，CompressContent 中有詳細聊天記錄，BytesExtra 中有圖片影片等的快取 |
| 49 | 33/36 | 分享的小程式，CompressContent 中有卡片資訊，BytesExtra 中有封面快取位置 |
| 49 | 57 | 帶有引用的文字訊息（這種型別下 StrContent 為空，傳送和引用的內容均在 CompressContent 中） |
| 49 | 63 | 影片號直播或直播回放等 |
| 49 | 87 | 群公告 |
| 49 | 88 | 影片號直播或直播回放等 |
| 49 | 2000 | 轉賬訊息（包括髮出、接收、主動退還） |
| 49 | 2003 | 贈送紅包封面 |
| 10000 | 0 | 系統通知（居中出現的那種灰色文字） |
| 10000 | 4 | 拍一拍 |
| 10000 | 8000 | 系統通知（特別包含你邀請別人加入群聊） |
更多內容檢視：
微信PC端各個資料庫檔案結構與功能簡述 - 根目錄_微信電腦端目錄格式
以下是各個資料庫的處理實現程式碼：
# -*- coding: utf-8 -*-#
# -------------------------------------------------------------------------------
# Name: dbbase.py
# Description:
# Author: Rainbow
# Date: 2024/11/08
# -------------------------------------------------------------------------------
import importlib
import os
import sqlite3
import time
from .utils import db_loger
from dbutils.pooled_db import PooledDB
# import logging
#
# db_loger = logging.getLogger("db_prepare")
class DatabaseSingletonBase:
# _singleton_instances = {} # 使用字典儲存不同db_path對應的單例例項
_class_name = "DatabaseSingletonBase"
_db_pool = {} # 使用字典儲存不同db_path對應的連線池
# def __new__(cls, *args, **kwargs):
# if cls._class_name not in cls._singleton_instances:
# cls._singleton_instances[cls._class_name] = super().__new__(cls)
# return cls._singleton_instances[cls._class_name]
@classmethod
def connect(cls, db_config):
"""
連線資料庫，如果增加其他資料庫連線，則重寫該方法
:param db_config: 資料庫配置
:return: 連線池
"""
if not db_config:
raise ValueError("db_config 不能為空")
db_key = db_config.get("key", "chwm.vip")
db_type = db_config.get("type", "sqlite")
if db_key in cls._db_pool and cls._db_pool[db_key] is not None:
return cls._db_pool[db_key]
if db_type == "sqlite":
db_path = db_config.get("path", "")
if not os.path.exists(db_path):
raise FileNotFoundError(f"檔案不存在: {db_path}")
pool = PooledDB(
creator=sqlite3, # 使用 sqlite3 作為連線建立者
maxconnections=0, # 連線池最大連線數
mincached=4, # 初始化時，連結池中至少建立的空閒的連結，0表示不建立
maxusage=1, # 一個連結最多被重複使用的次數，None表示無限制
blocking=True, # 連線池中如果沒有可用連線後，是否阻塞等待。True，等待；False，不等待然後報錯
ping=0, # ping 資料庫判斷是否服務正常
database=db_path
)
elif db_type == "mysql":
mysql_config = {
'user': db_config['user'],
'host': db_config['host'],
'password': db_config['password'],
'database': db_config['database'],
'port': db_config['port']
}
pool = PooledDB(
creator=importlib.import_module('pymysql'), # 使用 mysql 作為連線建立者
ping=1, # ping 資料庫判斷是否服務正常
**mysql_config
)
else:
raise ValueError(f"不支援的資料庫型別: {db_type}")
db_loger.info(f"{pool} 連線控制代碼建立 {db_config}")
cls._db_pool[db_key] = pool
return pool
class DatabaseBase(DatabaseSingletonBase):
_class_name = "DatabaseBase"
existed_tables = []
def __init__(self, db_config):
"""
db_config = {
"key": "test1",
"type": "sqlite",
"path": r"C:\***\wxdump_work\merge_all.db"
}
"""
self.config = db_config
self.pool = self.connect(self.config)
self.__get_existed_tables()
def __get_existed_tables(self):
sql = "SELECT tbl_name FROM sqlite_master WHERE type = 'table' and tbl_name!='sqlite_sequence';"
existing_tables = self.execute(sql)
if existing_tables:
self.existed_tables = [row[0].lower() for row in existing_tables]
return self.existed_tables
else:
return None
def tables_exist(self, required_tables: str or list):
"""
判斷該類所需要的表是否存在
Check if all required tables exist in the database.
Args:
required_tables (list or str): A list of table names or a single table name string.
Returns:
bool: True if all required tables exist, False otherwise.
"""
if isinstance(required_tables, str):
required_tables = [required_tables]
rbool = all(table.lower() in self.existed_tables for table in (required_tables or []))
if not rbool: db_loger.warning(f"{required_tables=}\n{self.existed_tables=}\n{rbool=}")
return rbool
def execute(self, sql, params=None):
"""
執行SQL語句
:param sql: SQL語句 (str)
:param params: 引數 (tuple)
:return: 查詢結果 (list)
"""
connection = self.pool.connection()
try:
# connection.text_factory = bytes
cursor = connection.cursor()
if params:
cursor.execute(sql, params)
else:
cursor.execute(sql)
return cursor.fetchall()
except Exception as e1:
try:
connection.text_factory = bytes
cursor = connection.cursor()
if params:
cursor.execute(sql, params)
else:
cursor.execute(sql)
rdata = cursor.fetchall()
connection.text_factory = str
return rdata
except Exception as e2:
db_loger.error(f"{sql=}\n{params=}\n{e1=}\n{e2=}\n", exc_info=True)
return None
finally:
connection.close()
def close(self):
self.pool.close()
db_loger.info(f"關閉資料庫 - {self.config}")
def __del__(self):
self.close()
# class MsgDb(DatabaseBase):
#
# def p(self, *args, **kwargs):
# sel = "select tbl_name from sqlite_master where type='table'"
# data = self.execute(sel)
# # print([i[0] for i in data])
# return data
#
#
# class MsgDb1(DatabaseBase):
# _class_name = "MsgDb1"
#
# def p(self, *args, **kwargs):
# sel = "select tbl_name from sqlite_master where type='table'"
# data = self.execute(sel)
# # print([i[0] for i in data])
# return data
#
#
# if __name__ == '__main__':
# logging.basicConfig(level=logging.INFO,
# style='{',
# datefmt='%Y-%m-%d %H:%M:%S',
# format='[{levelname[0]}] {asctime} [{name}:{levelno}] {pathname}:{lineno} {message}'
# )
#
# config1 = {
# "key": "test1",
# "type": "sqlite",
# "path": r"D:\e_all.db"
# }
# config2 = {
# "key": "test2",
# "type": "sqlite",
# "path": r"D:\_call.db"
# }
#
# t1 = MsgDb(config1)
# t1.p()
# t2 = MsgDb(config2)
# t2.p()
# t3 = MsgDb1(config1)
# t3.p()
# t4 = MsgDb1(config2)
# t4.p()
#
# print(t4._db_pool)
# # 銷燬t1
# del t1
# # 銷燬t2
# del t2
# del t3
#
# # 銷燬t4
# del t4
# import time
# time.sleep(1)
#
# t1 = MsgDb(config1)
# t1.p()
# t2 = MsgDb(config2)
# t2.p()
#
#
# print(t2._db_pool)
# -*- coding: utf-8 -*-#
# -------------------------------------------------------------------------------
# Name: Favorite.py
# Description: 負責處理wx收藏資料庫
# Author: Rainbow
# Date: 2024/11/08
# -------------------------------------------------------------------------------
from collections import defaultdict
from .dbbase import DatabaseBase
from .utils import timestamp2str, xml2dict
# * FavItems：收藏的訊息條目列表
# * FavDataItem：收藏的具體資料。大概可以確定以下兩點
# * 即使只是簡單收藏一篇公眾號文章也會在 FavDataItem 中有一個對應的記錄
# * 對於收藏的合併轉發型別的訊息，合併轉發中的每一條訊息在 FavDataItem 中都是一個獨立的記錄
# * FavTags：為收藏內容新增的標籤
class FavoriteHandler(DatabaseBase):
_class_name = "Favorite"
Favorite_required_tables = ["FavItems", "FavDataItem", "FavTagDatas", "FavBindTagDatas"]
def get_tags(self, LocalID):
"""
return: {LocalID: TagName}
"""
if not self.tables_exist("FavTagDatas"):
return {}
if LocalID is None:
sql = "select LocalID, TagName from FavTagDatas order by ServerSeq"
else:
sql = "select LocalID, TagName from FavTagDatas where LocalID = '%s' order by ServerSeq " % LocalID
tags = self.execute(sql) # [(1, 797940830, '程式語言類'), (2, 806153863, '賬單')]
# 轉換為字典
tags = {tag[0]: tag[1] for tag in tags}
return tags
def get_FavBindTags(self):
"""
return: [(FavLocalID, TagName)]
"""
sql = ("select DISTINCT A.FavLocalID, B.TagName "
"from FavBindTagDatas A, FavTagDatas B where A.TagLocalID = B.LocalID")
FavBindTags = self.execute(sql)
return FavBindTags
def get_favorite(self):
"""
return: [{FavItemsFields}, {FavItemsFields}]
"""
FavItemsFields = {
"FavLocalID": "本地收藏ID",
"SvrFavId": "伺服器收藏ID",
"SourceId": "源ID",
"Type": "型別",
"SourceType": "源型別",
"LocalStatus": "本地狀態",
"Flag": "標記",
"Status": "狀態",
"FromUser": "源使用者",
"RealChatName": "實際聊天名稱",
"SearchKey": "搜尋關鍵字",
"UpdateTime": "更新時間",
"reseverd0": "預留欄位0",
"XmlBuf": "XML緩衝區"
}
FavDataItemFields = {
"FavLocalID": "本地收藏ID",
"Type": "型別",
"DataId": "資料ID",
"HtmlId": "HTML ID",
"Datasourceid": "資料來源ID",
"Datastatus": "資料狀態",
"Datafmt": "資料格式",
"Datatitle": "資料標題",
"Datadesc": "資料描述",
"Thumbfullmd5": "縮圖全MD5",
"Thumbhead256md5": "縮圖頭256MD5",
"Thumbfullsize": "縮圖全尺寸",
"fullmd5": "全MD5",
"head256md5": "頭256MD5",
"fullsize": "全尺寸",
"cdn_thumburl": "CDN縮圖URL",
"cdn_thumbkey": "CDN縮圖KEY",
"thumb_width": "縮圖寬度",
"thumb_height": "縮圖高度",
"cdn_dataurl": "CDN資料URL",
"cdn_datakey": "CDN資料KEY",
"cdn_encryver": "CDN加密版本",
"duration": "時長",
"stream_weburl": "流媒體WEB URL",
"stream_dataurl": "流媒體資料URL",
"stream_lowbandurl": "流媒體低頻寬URL",
"sourcethumbpath": "源縮圖路徑",
"sourcedatapath": "源資料路徑",
"stream_videoid": "流媒體影片ID",
"Rerserved1": "保留欄位1",
"Rerserved2": "保留欄位2",
"Rerserved3": "保留欄位3",
"Rerserved4": "保留欄位4",
"Rerserved5": "保留欄位5",
"Rerserved6": "保留欄位6",
"Rerserved7": "保留欄位7"
}
if not self.tables_exist(["FavItems", "FavDataItem"]):
return False
sql1 = "select " + ",".join(FavItemsFields.keys()) + " from FavItems order by UpdateTime desc"
sql2 = "select " + ",".join(FavDataItemFields.keys()) + " from FavDataItem B order by B.RecId asc"
FavItemsList = self.execute(sql1)
FavDataItemList = self.execute(sql2)
if FavItemsList is None or len(FavItemsList) == 0:
return False
FavDataDict = {}
if FavDataItemList and len(FavDataItemList) >= 0:
for item in FavDataItemList:
data_dict = {}
for i, key in enumerate(FavDataItemFields.keys()):
data_dict[key] = item[i]
FavDataDict[item[0]] = FavDataDict.get(item[0], []) + [data_dict]
# 獲取標籤
FavTags = self.get_FavBindTags()
FavTagsDict = {}
for FavLocalID, TagName in FavTags:
FavTagsDict[FavLocalID] = FavTagsDict.get(FavLocalID, []) + [TagName]
rdata = []
for item in FavItemsList:
processed_item = {
key: item[i] for i, key in enumerate(FavItemsFields.keys())
}
processed_item['UpdateTime'] = timestamp2str(processed_item['UpdateTime'])
processed_item['XmlBuf'] = xml2dict(processed_item['XmlBuf'])
processed_item['TypeName'] = Favorite_type_converter(processed_item['Type'])
processed_item['FavData'] = FavDataDict.get(processed_item['FavLocalID'], [])
processed_item['Tags'] = FavTagsDict.get(processed_item['FavLocalID'], [])
rdata.append(processed_item)
try:
import pandas as pd
except ImportError:
return False
pf = pd.DataFrame(FavItemsList)
pf.columns = FavItemsFields.keys() # set column names
pf["UpdateTime"] = pf["UpdateTime"].apply(timestamp2str) # 處理時間
pf["XmlBuf"] = pf["XmlBuf"].apply(xml2dict) # 處理xml
pf["TypeName"] = pf["Type"].apply(Favorite_type_converter) # 新增型別名稱列
pf["FavData"] = pf["FavLocalID"].apply(lambda x: FavDataDict.get(x, [])) # 新增資料列
pf["Tags"] = pf["FavLocalID"].apply(lambda x: FavTagsDict.get(x, [])) # 新增標籤列
pf = pf.fillna("") # 去掉Nan
rdata = pf.to_dict(orient="records")
return rdata
def Favorite_type_converter(type_id_or_name: [str, int]):
"""
收藏型別ID與名稱轉換
名稱(str)=>ID(int)
ID(int)=>名稱(str)
:param type_id_or_name: 訊息型別ID或名稱
:return: 訊息型別名稱或ID
"""
type_name_dict = defaultdict(lambda: "未知", {
1: "文字", # 文字 已測試
2: "圖片", # 圖片 已測試
3: "語音", # 語音
4: "影片", # 影片 已測試
5: "連結", # 連結 已測試
6: "位置", # 位置
7: "小程式", # 小程式
8: "檔案", # 檔案 已測試
14: "聊天記錄", # 聊天記錄 已測試
16: "群聊影片", # 群聊中的影片 可能
18: "筆記" # 筆記 已測試
})
if isinstance(type_id_or_name, int):
return type_name_dict[type_id_or_name]
elif isinstance(type_id_or_name, str):
return next((k for k, v in type_name_dict.items() if v == type_id_or_name), (0, 0))
else:
raise ValueError("Invalid input type")
# -*- coding: utf-8 -*-#
# -------------------------------------------------------------------------------
# Name: MediaMSG.py
# Description: 負責處理語音資料庫
# Author: Rainbow
# Date: 2024/11/08
# -------------------------------------------------------------------------------
from .dbbase import DatabaseBase
from .utils import silk2audio
class MediaHandler(DatabaseBase):
_class_name = "MediaMSG"
Media_required_tables = ["Media"]
def Media_add_index(self):
"""
新增索引, 加快查詢速度
"""
if self.tables_exist("Media"):
self.execute("CREATE INDEX IF NOT EXISTS MsgSvrID ON Media(Reserved0)")
def get_audio(self, MsgSvrID, is_play=False, is_wave=False, save_path=None, rate=24000):
if not self.tables_exist("Media"):
return False
sql = "select Buf from Media where Reserved0=? "
DBdata = self.execute(sql, (MsgSvrID,))
if not DBdata:
return False
if len(DBdata) == 0:
return False
data = DBdata[0][0] # [1:] + b'\xFF\xFF'
try:
pcm_data = silk2audio(buf_data=data, is_play=is_play, is_wave=is_wave, save_path=save_path, rate=rate)
return pcm_data
except Exception as e:
return False
# -*- coding: utf-8 -*-#
# -------------------------------------------------------------------------------
# Name: MicroMsg.py
# Description: 負責處理聯絡人資料庫
# Author: Rainbow
# Date: 2024/11/08
# -------------------------------------------------------------------------------
import logging
from .dbbase import DatabaseBase
from .utils import timestamp2str, bytes2str, db_loger, db_error
import blackboxprotobuf
class MicroHandler(DatabaseBase):
_class_name = "MicroMsg"
Micro_required_tables = ["ContactLabel", "Contact", "ContactHeadImgUrl", "Session", "ChatInfo", "ChatRoom",
"ChatRoomInfo"]
def Micro_add_index(self):
"""
新增索引, 加快查詢速度
"""
# 為 Session 表新增索引
if self.tables_exist("Session"):
self.execute("CREATE INDEX IF NOT EXISTS idx_Session_strUsrName_nTime ON Session(strUsrName, nTime);")
self.execute("CREATE INDEX IF NOT EXISTS idx_Session_nOrder ON Session(nOrder);")
self.execute("CREATE INDEX IF NOT EXISTS idx_Session_nTime ON Session(nTime);")
# 為 Contact 表新增索引
if self.tables_exist("Contact"):
self.execute("CREATE INDEX IF NOT EXISTS idx_Contact_UserName ON Contact(UserName);")
# 為 ContactHeadImgUrl 表新增索引
if self.tables_exist('ContactHeadImgUrl'):
self.execute("CREATE INDEX IF NOT EXISTS idx_ContactHeadImgUrl_usrName ON ContactHeadImgUrl(usrName);")
# 為 ChatInfo 表新增索引
if self.tables_exist('ChatInfo'):
self.execute("CREATE INDEX IF NOT EXISTS idx_ChatInfo_Username_LastReadedCreateTime "
"ON ChatInfo(Username, LastReadedCreateTime);")
self.execute(
"CREATE INDEX IF NOT EXISTS idx_ChatInfo_LastReadedCreateTime ON ChatInfo(LastReadedCreateTime);")
# 為 Contact 表新增複合索引
if self.tables_exist('Contact'):
self.execute("CREATE INDEX IF NOT EXISTS idx_Contact_search "
"ON Contact(UserName, NickName, Remark, Alias, QuanPin, PYInitial, RemarkQuanPin, RemarkPYInitial);")
# 為 ChatRoom 和 ChatRoomInfo 表新增索引
if self.tables_exist(['ChatRoomInfo', "ChatRoom"]):
self.execute("CREATE INDEX IF NOT EXISTS idx_ChatRoom_ChatRoomName ON ChatRoom(ChatRoomName);")
self.execute("CREATE INDEX IF NOT EXISTS idx_ChatRoomInfo_ChatRoomName ON ChatRoomInfo(ChatRoomName);")
@db_error
def get_labels(self, id_is_key=True):
"""
讀取標籤列表
:param id_is_key: id_is_key: True: id作為key，False: name作為key
:return:
"""
labels = {}
if not self.tables_exist("ContactLabel"):
return labels
sql = "SELECT LabelId, LabelName FROM ContactLabel ORDER BY LabelName ASC;"
result = self.execute(sql)
if not result:
return labels
if id_is_key:
labels = {row[0]: row[1] for row in result}
else:
labels = {row[1]: row[0] for row in result}
return labels
@db_error
def get_session_list(self):
"""
獲取會話列表
:return: 會話列表
"""
sessions = {}
if not self.tables_exist(["Session", "Contact", "ContactHeadImgUrl"]):
return sessions
sql = (
"SELECT S.strUsrName,S.nOrder,S.nUnReadCount, S.strNickName, S.nStatus, S.nIsSend, S.strContent, "
"S.nMsgLocalID, S.nMsgStatus, S.nTime, S.nMsgType, S.Reserved2 AS nMsgSubType, C.UserName, C.Alias, "
"C.DelFlag, C.Type, C.VerifyFlag, C.Reserved1, C.Reserved2, C.Remark, C.NickName, C.LabelIDList, "
"C.ChatRoomType, C.ChatRoomNotify, C.Reserved5, C.Reserved6 as describe, C.ExtraBuf, H.bigHeadImgUrl "
"FROM (SELECT strUsrName, MAX(nTime) AS MaxnTime FROM Session GROUP BY strUsrName) AS SubQuery "
"JOIN Session S ON S.strUsrName = SubQuery.strUsrName AND S.nTime = SubQuery.MaxnTime "
"left join Contact C ON C.UserName = S.strUsrName "
"LEFT JOIN ContactHeadImgUrl H ON C.UserName = H.usrName "
"WHERE S.strUsrName!='@publicUser' "
"ORDER BY S.nTime DESC;"
)
db_loger.info(f"get_session_list sql: {sql}")
ret = self.execute(sql)
if not ret:
return sessions
id2label = self.get_labels()
for row in ret:
(strUsrName, nOrder, nUnReadCount, strNickName, nStatus, nIsSend, strContent,
nMsgLocalID, nMsgStatus, nTime, nMsgType, nMsgSubType,
UserName, Alias, DelFlag, Type, VerifyFlag, Reserved1, Reserved2, Remark, NickName, LabelIDList,
ChatRoomType, ChatRoomNotify, Reserved5, describe, ExtraBuf, bigHeadImgUrl) = row
ExtraBuf = get_ExtraBuf(ExtraBuf)
LabelIDList = LabelIDList.split(",") if LabelIDList else []
LabelIDList = [id2label.get(int(label_id), label_id) for label_id in LabelIDList if label_id]
nTime = timestamp2str(nTime) if nTime else None
sessions[strUsrName] = {
"wxid": strUsrName, "nOrder": nOrder, "nUnReadCount": nUnReadCount, "strNickName": strNickName,
"nStatus": nStatus, "nIsSend": nIsSend, "strContent": strContent, "nMsgLocalID": nMsgLocalID,
"nMsgStatus": nMsgStatus, "nTime": nTime, "nMsgType": nMsgType, "nMsgSubType": nMsgSubType,
"LastReadedCreateTime": nTime,
"nickname": NickName, "remark": Remark, "account": Alias,
"describe": describe, "headImgUrl": bigHeadImgUrl if bigHeadImgUrl else "",
"ExtraBuf": ExtraBuf, "LabelIDList": tuple(LabelIDList)
}
return sessions
@db_error
def get_recent_chat_wxid(self):
"""
獲取最近聊天的聯絡人
:return: 最近聊天的聯絡人
"""
users = []
if not self.tables_exist(["ChatInfo"]):
return users
sql = (
"SELECT A.Username, LastReadedCreateTime, LastReadedSvrId "
"FROM ( SELECT Username, MAX(LastReadedCreateTime) AS MaxLastReadedCreateTime FROM ChatInfo "
"WHERE LastReadedCreateTime IS NOT NULL AND LastReadedCreateTime > 1007911408000 GROUP BY Username "
") AS SubQuery JOIN ChatInfo A "
"ON A.Username = SubQuery.Username AND LastReadedCreateTime = SubQuery.MaxLastReadedCreateTime "
"ORDER BY A.LastReadedCreateTime DESC;"
)
db_loger.info(f"get_recent_chat_wxid sql: {sql}")
result = self.execute(sql)
if not result:
return []
for row in result:
# 獲取使用者名稱、暱稱、備註和聊天記錄數量
username, LastReadedCreateTime, LastReadedSvrId = row
LastReadedCreateTime = timestamp2str(LastReadedCreateTime) if LastReadedCreateTime else None
users.append(
{"wxid": username, "LastReadedCreateTime": LastReadedCreateTime, "LastReadedSvrId": LastReadedSvrId})
return users
@db_error
def get_user_list(self, word: str = None, wxids: list = None, label_ids: list = None):
"""
獲取聯絡人列表
[ 注意：如果修改這個函式，要同時修改dbOpenIMContact.py中的get_im_user_list函式 ]
:param word: 查詢關鍵字，可以是wxid,使用者名稱、暱稱、備註、描述，允許拼音
:param wxids: wxid列表
:param label_ids: 標籤id
:return: 聯絡人字典
"""
if isinstance(wxids, str):
wxids = [wxids]
if isinstance(label_ids, str):
label_ids = [label_ids]
users = {}
if not self.tables_exist(["Contact", "ContactHeadImgUrl"]):
return users
sql = (
"SELECT A.UserName, A.Alias, A.DelFlag, A.Type, A.VerifyFlag, A.Reserved1, A.Reserved2,"
"A.Remark, A.NickName, A.LabelIDList, A.ChatRoomType, A.ChatRoomNotify, A.Reserved5,"
"A.Reserved6 as describe, A.ExtraBuf, B.bigHeadImgUrl "
"FROM Contact A LEFT JOIN ContactHeadImgUrl B ON A.UserName = B.usrName WHERE 1==1 ;"
)
if word:
sql = sql.replace(";",
f"AND ( A.UserName LIKE '%{word}%' "
f"OR A.NickName LIKE '%{word}%' "
f"OR A.Remark LIKE '%{word}%' "
f"OR A.Alias LIKE '%{word}%' "
f"OR LOWER(A.QuanPin) LIKE LOWER('%{word}%') "
f"OR LOWER(A.PYInitial) LIKE LOWER('%{word}%') "
f"OR LOWER(A.RemarkQuanPin) LIKE LOWER('%{word}%') "
f"OR LOWER(A.RemarkPYInitial) LIKE LOWER('%{word}%') "
f") "
";")
if wxids:
sql = sql.replace(";", f"AND A.UserName IN ('" + "','".join(wxids) + "') ;")
if label_ids:
sql_label = [f"A.LabelIDList LIKE '%{i}%' " for i in label_ids]
sql_label = " OR ".join(sql_label)
sql = sql.replace(";", f"AND ({sql_label}) ;")
db_loger.info(f"get_user_list sql: {sql}")
result = self.execute(sql)
if not result:
return users
id2label = self.get_labels()
for row in result:
# 獲取wxid,暱稱，備註，描述，頭像,標籤
(UserName, Alias, DelFlag, Type, VerifyFlag, Reserved1, Reserved2, Remark, NickName, LabelIDList,
ChatRoomType, ChatRoomNotify, Reserved5, describe, ExtraBuf, bigHeadImgUrl) = row
ExtraBuf = get_ExtraBuf(ExtraBuf)
LabelIDList = LabelIDList.split(",") if LabelIDList else []
LabelIDList = [id2label.get(int(label_id), label_id) for label_id in LabelIDList if label_id]
# print(f"{UserName=}\n{Alias=}\n{DelFlag=}\n{Type=}\n{VerifyFlag=}\n{Reserved1=}\n{Reserved2=}\n"
# f"{Remark=}\n{NickName=}\n{LabelIDList=}\n{ChatRoomType=}\n{ChatRoomNotify=}\n{Reserved5=}\n"
# f"{describe=}\n{ExtraBuf=}\n{bigHeadImgUrl=}")
users[UserName] = {
"wxid": UserName, "nickname": NickName, "remark": Remark, "account": Alias,
"describe": describe, "headImgUrl": bigHeadImgUrl if bigHeadImgUrl else "",
"ExtraBuf": ExtraBuf, "LabelIDList": tuple(LabelIDList),
"extra": None}
extras = self.get_room_list(roomwxids=filter(lambda x: "@" in x, users.keys()))
for UserName in users:
users[UserName]["extra"] = extras.get(UserName, None)
return users
@db_error
def get_room_list(self, word=None, roomwxids: list = None):
"""
獲取群聊列表
:param word: 群聊搜尋詞
:param roomwxids: 群聊wxid列表
:return: 群聊字典
"""
# 連線 MicroMsg.db 資料庫，並執行查詢
if isinstance(roomwxids, str):
roomwxids = [roomwxids]
rooms = {}
if not self.tables_exist(["ChatRoom", "ChatRoomInfo"]):
return rooms
sql = (
"SELECT A.ChatRoomName,A.UserNameList,A.DisplayNameList,A.ChatRoomFlag,A.IsShowName,"
"A.SelfDisplayName,A.Reserved2,A.RoomData, "
"B.Announcement,B.AnnouncementEditor,B.AnnouncementPublishTime "
"FROM ChatRoom A LEFT JOIN ChatRoomInfo B ON A.ChatRoomName==B.ChatRoomName "
"WHERE 1==1 ;")
if word:
sql = sql.replace(";",
f"AND A.ChatRoomName LIKE '%{word}%' ;")
if roomwxids:
sql = sql.replace(";", f"AND A.ChatRoomName IN ('" + "','".join(roomwxids) + "') ;")
db_loger.info(f"get_room_list sql: {sql}")
result = self.execute(sql)
if not result:
return rooms
for row in result:
# 獲取使用者名稱、暱稱、備註和聊天記錄數量
(ChatRoomName, UserNameList, DisplayNameList, ChatRoomFlag, IsShowName, SelfDisplayName,
Reserved2, RoomData,
Announcement, AnnouncementEditor, AnnouncementPublishTime) = row
UserNameList = UserNameList.split("^G")
DisplayNameList = DisplayNameList.split("^G")
RoomData = ChatRoom_RoomData(RoomData)
wxid2roomNickname = {}
if RoomData:
rd = []
for k, v in RoomData.items():
if isinstance(v, list):
rd += v
for i in rd:
try:
if isinstance(i, dict) and isinstance(i.get('1'), str) and i.get('2'):
wxid2roomNickname[i['1']] = i["2"]
except Exception as e:
db_loger.error(f"wxid2remark: ChatRoomName:{ChatRoomName}, {i} error:{e}", exc_info=True)
wxid2userinfo = self.get_user_list(wxids=UserNameList)
for i in wxid2userinfo:
wxid2userinfo[i]["roomNickname"] = wxid2roomNickname.get(i, "")
owner = wxid2userinfo.get(Reserved2, Reserved2)
rooms[ChatRoomName] = {
"wxid": ChatRoomName, "roomWxids": UserNameList, "IsShowName": IsShowName,
"ChatRoomFlag": ChatRoomFlag, "SelfDisplayName": SelfDisplayName,
"owner": owner, "wxid2userinfo": wxid2userinfo,
"Announcement": Announcement, "AnnouncementEditor": AnnouncementEditor,
"AnnouncementPublishTime": AnnouncementPublishTime}
return rooms
@db_error
def ChatRoom_RoomData(RoomData):
# 讀取群聊資料,主要為 wxid，以及對應暱稱
if RoomData is None or not isinstance(RoomData, bytes):
return None
data = get_BytesExtra(RoomData)
bytes2str(data) if data else None
return data
@db_error
def get_BytesExtra(BytesExtra):
if BytesExtra is None or not isinstance(BytesExtra, bytes):
return None
try:
deserialize_data, message_type = blackboxprotobuf.decode_message(BytesExtra)
return deserialize_data
except Exception as e:
db_loger.warning(f"\nget_BytesExtra: {e}\n{BytesExtra}", exc_info=True)
return None
@db_error
def get_ExtraBuf(ExtraBuf: bytes):
"""
讀取ExtraBuf（聯絡人表）
:param ExtraBuf:
:return:
"""
if not ExtraBuf:
return None
buf_dict = {
'74752C06': '性別[1男2女]', '46CF10C4': '個性簽名', 'A4D9024A': '國', 'E2EAA8D1': '省', '1D025BBF': '市',
'F917BCC0': '公司名稱', '759378AD': '手機號', '4EB96D85': '企微屬性', '81AE19B4': '朋友圈背景',
'0E719F13': '備註圖片', '945f3190': '備註圖片2',
'DDF32683': '0', '88E28FCE': '1', '761A1D2D': '2', '0263A0CB': '3', '0451FF12': '4', '228C66A8': '5',
'4D6C4570': '6', '4335DFDD': '7', 'DE4CDAEB': '8', 'A72BC20A': '9', '069FED52': '10', '9B0F4299': '11',
'3D641E22': '12', '1249822C': '13', 'B4F73ACB': '14', '0959EB92': '15', '3CF4A315': '16',
'C9477AC60201E44CD0E8': '17', 'B7ACF0F5': '18', '57A7B5A8': '19', '695F3170': '20', 'FB083DD9': '21',
'0240E37F': '22', '315D02A3': '23', '7DEC0BC3': '24', '16791C90': '25'
}
rdata = {}
for buf_name in buf_dict:
rdata_name = buf_dict[buf_name]
buf_name = bytes.fromhex(buf_name)
offset = ExtraBuf.find(buf_name)
if offset == -1:
rdata[rdata_name] = ""
continue
offset += len(buf_name)
type_id = ExtraBuf[offset: offset + 1]
offset += 1
if type_id == b"\x04":
rdata[rdata_name] = int.from_bytes(ExtraBuf[offset: offset + 4], "little")
elif type_id == b"\x18":
length = int.from_bytes(ExtraBuf[offset: offset + 4], "little")
rdata[rdata_name] = ExtraBuf[offset + 4: offset + 4 + length].decode("utf-16").rstrip("\x00")
elif type_id == b"\x17":
length = int.from_bytes(ExtraBuf[offset: offset + 4], "little")
rdata[rdata_name] = ExtraBuf[offset + 4: offset + 4 + length].decode("utf-8", errors="ignore").rstrip(
"\x00")
elif type_id == b"\x05":
rdata[rdata_name] = f"0x{ExtraBuf[offset: offset + 8].hex()}"
return rdata
# -*- coding: utf-8 -*-#
# -------------------------------------------------------------------------------
# Name: MSG.py
# Description: 負責處理訊息資料庫資料
# Author: Rainbow
# Date: 2024/11/08
# -------------------------------------------------------------------------------
import json
import os
import re
import lz4.block
import blackboxprotobuf
from .dbbase import DatabaseBase
from .utils import db_error, timestamp2str, xml2dict, match_BytesExtra, type_converter
class MsgHandler(DatabaseBase):
_class_name = "MSG"
MSG_required_tables = ["MSG"]
def Msg_add_index(self):
"""
新增索引,加快查詢速度
"""
# 檢查是否存在索引
if not self.tables_exist("MSG"):
return
self.execute("CREATE INDEX IF NOT EXISTS idx_MSG_StrTalker ON MSG(StrTalker);")
self.execute("CREATE INDEX IF NOT EXISTS idx_MSG_CreateTime ON MSG(CreateTime);")
self.execute("CREATE INDEX IF NOT EXISTS idx_MSG_StrTalker_CreateTime ON MSG(StrTalker, CreateTime);")
@db_error
def get_m_msg_count(self, wxids: list = ""):
"""
獲取聊天記錄數量,根據wxid獲取單個聯絡人的聊天記錄數量，不傳wxid則獲取所有聯絡人的聊天記錄數量
:param wxids: wxid list
:return: 聊天記錄數量列表 {wxid: chat_count, total: total_count}
"""
if isinstance(wxids, str) and wxids:
wxids = [wxids]
if wxids:
wxids = "('" + "','".join(wxids) + "')"
sql = f"SELECT StrTalker, COUNT(*) FROM MSG WHERE StrTalker IN {wxids} GROUP BY StrTalker ORDER BY COUNT(*) DESC;"
else:
sql = f"SELECT StrTalker, COUNT(*) FROM MSG GROUP BY StrTalker ORDER BY COUNT(*) DESC;"
sql_total = f"SELECT COUNT(*) FROM MSG;"
if not self.tables_exist("MSG"):
return {}
result = self.execute(sql)
total_ret = self.execute(sql_total)
if not result:
return {}
total = 0
if total_ret and len(total_ret) > 0:
total = total_ret[0][0]
msg_count = {"total": total}
msg_count.update({row[0]: row[1] for row in result})
return msg_count
@db_error
def get_msg_list(self, wxids: list or str = "", start_index=0, page_size=500, msg_type: str = "",
msg_sub_type: str = "", start_createtime=None, end_createtime=None, my_talker="我"):
"""
獲取聊天記錄列表
:param wxids: [wxid]
:param start_index: 起始索引
:param page_size: 頁大小
:param msg_type: 訊息型別
:param msg_sub_type: 訊息子型別
:param start_createtime: 開始時間
:param end_createtime: 結束時間
:param my_talker: 我
:return: 聊天記錄列表 {"id": _id, "MsgSvrID": str(MsgSvrID), "type_name": type_name, "is_sender": IsSender,
"talker": talker, "room_name": StrTalker, "msg": msg, "src": src, "extra": {},
"CreateTime": CreateTime, }
"""
if not self.tables_exist("MSG"):
return [], []
if isinstance(wxids, str) and wxids:
wxids = [wxids]
param = ()
sql_wxid, param = (f"AND StrTalker in ({', '.join('?' for _ in wxids)}) ",
param + tuple(wxids)) if wxids else ("", param)
sql_type, param = ("AND Type=? ", param + (msg_type,)) if msg_type else ("", param)
sql_sub_type, param = ("AND SubType=? ", param + (msg_sub_type,)) if msg_type and msg_sub_type else ("", param)
sql_start_createtime, param = ("AND CreateTime>=? ", param + (start_createtime,)) if start_createtime else (
"", param)
sql_end_createtime, param = ("AND CreateTime<=? ", param + (end_createtime,)) if end_createtime else ("", param)
sql = (
"SELECT localId,TalkerId,MsgSvrID,Type,SubType,CreateTime,IsSender,Sequence,StatusEx,FlagEx,Status,"
"MsgSequence,StrContent,MsgServerSeq,StrTalker,DisplayContent,Reserved0,Reserved1,Reserved3,"
"Reserved4,Reserved5,Reserved6,CompressContent,BytesExtra,BytesTrans,Reserved2,"
"ROW_NUMBER() OVER (ORDER BY CreateTime ASC) AS id "
"FROM MSG WHERE 1=1 "
f"{sql_wxid}"
f"{sql_type}"
f"{sql_sub_type}"
f"{sql_start_createtime}"
f"{sql_end_createtime}"
f"ORDER BY CreateTime ASC LIMIT ?,?"
)
param = param + (start_index, page_size)
result = self.execute(sql, param)
if not result:
return [], []
result_data = (self.get_msg_detail(row, my_talker=my_talker) for row in result)
rdata = list(result_data) # 轉為列表
wxid_list = {d['talker'] for d in rdata} # 建立一個無重複的 wxid 列表
return rdata, list(wxid_list)
@db_error
def get_date_count(self, wxid='', start_time: int = 0, end_time: int = 0, time_format='%Y-%m-%d'):
"""
獲取每日聊天記錄數量，包括髮送者數量、接收者數量和總數。
"""
if not self.tables_exist("MSG"):
return {}
if isinstance(start_time, str) and start_time.isdigit():
start_time = int(start_time)
if isinstance(end_time, str) and end_time.isdigit():
end_time = int(end_time)
# if start_time or end_time is not an integer and not a float, set both to 0
if not (isinstance(start_time, (int, float)) and isinstance(end_time, (int, float))):
start_time = 0
end_time = 0
params = ()
sql_wxid = "AND StrTalker = ? " if wxid else ""
params = params + (wxid,) if wxid else params
sql_time = "AND CreateTime BETWEEN ? AND ? " if start_time and end_time else ""
params = params + (start_time, end_time) if start_time and end_time else params
sql = (f"SELECT strftime('{time_format}', CreateTime, 'unixepoch', 'localtime') AS date, "
" COUNT(*) AS total_count ,"
" SUM(CASE WHEN IsSender = 1 THEN 1 ELSE 0 END) AS sender_count, "
" SUM(CASE WHEN IsSender = 0 THEN 1 ELSE 0 END) AS receiver_count "
"FROM MSG "
"WHERE StrTalker NOT LIKE '%chatroom%' "
f"{sql_wxid} {sql_time} "
f"GROUP BY date ORDER BY date ASC;")
result = self.execute(sql, params)
if not result:
return {}
# 將查詢結果轉換為字典
result_dict = {}
for row in result:
date, total_count, sender_count, receiver_count = row
result_dict[date] = {
"sender_count": sender_count,
"receiver_count": receiver_count,
"total_count": total_count
}
return result_dict
@db_error
def get_top_talker_count(self, top: int = 10, start_time: int = 0, end_time: int = 0):
"""
獲取聊天記錄數量最多的聯絡人,他們聊天記錄數量
"""
if not self.tables_exist("MSG"):
return {}
if isinstance(start_time, str) and start_time.isdigit():
start_time = int(start_time)
if isinstance(end_time, str) and end_time.isdigit():
end_time = int(end_time)
# if start_time or end_time is not an integer and not a float, set both to 0
if not (isinstance(start_time, (int, float)) and isinstance(end_time, (int, float))):
start_time = 0
end_time = 0
sql_time = f"AND CreateTime BETWEEN {start_time} AND {end_time} " if start_time and end_time else ""
sql = (
"SELECT StrTalker, COUNT(*) AS count,"
"SUM(CASE WHEN IsSender = 1 THEN 1 ELSE 0 END) AS sender_count, "
"SUM(CASE WHEN IsSender = 0 THEN 1 ELSE 0 END) AS receiver_count "
"FROM MSG "
"WHERE StrTalker NOT LIKE '%chatroom%' "
f"{sql_time} "
"GROUP BY StrTalker ORDER BY count DESC "
f"LIMIT {top};"
)
result = self.execute(sql)
if not result:
return {}
# 將查詢結果轉換為字典
result_dict = {row[0]: {"total_count": row[1], "sender_count": row[2], "receiver_count": row[3]} for row in
result}
return result_dict
# 單條訊息處理
@db_error
def get_msg_detail(self, row, my_talker="我"):
"""
獲取單條訊息詳情,格式化輸出
"""
(localId, TalkerId, MsgSvrID, Type, SubType, CreateTime, IsSender, Sequence, StatusEx, FlagEx, Status,
MsgSequence, StrContent, MsgServerSeq, StrTalker, DisplayContent, Reserved0, Reserved1, Reserved3,
Reserved4, Reserved5, Reserved6, CompressContent, BytesExtra, BytesTrans, Reserved2, _id) = row
CreateTime = timestamp2str(CreateTime)
type_id = (Type, SubType)
type_name = type_converter(type_id)
msg = StrContent
src = ""
extra = {}
if type_id == (1, 0): # 文字
msg = StrContent
elif type_id == (3, 0): # 圖片
DictExtra = get_BytesExtra(BytesExtra)
DictExtra_str = str(DictExtra)
img_paths = [i for i in re.findall(r"(FileStorage.*?)'", DictExtra_str)]
img_paths = sorted(img_paths, key=lambda p: "Image" in p, reverse=True)
if img_paths:
img_path = img_paths[0].replace("'", "")
img_path = [i for i in img_path.split("\\") if i]
img_path = os.path.join(*img_path)
src = img_path
else:
src = ""
msg = "圖片"
elif type_id == (34, 0): # 語音
tmp_c = xml2dict(StrContent)
voicelength = tmp_c.get("voicemsg", {}).get("voicelength", "")
transtext = tmp_c.get("voicetrans", {}).get("transtext", "")
if voicelength.isdigit():
voicelength = int(voicelength) / 1000
voicelength = f"{voicelength:.2f}"
msg = f"語音時長：{voicelength}秒\n翻譯結果：{transtext}" if transtext else f"語音時長：{voicelength}秒"
src = os.path.join(f"{StrTalker}",
f"{CreateTime.replace(':', '-').replace(' ', '_')}_{IsSender}_{MsgSvrID}.wav")
elif type_id == (43, 0): # 影片
DictExtra = get_BytesExtra(BytesExtra)
DictExtra = str(DictExtra)
DictExtra_str = str(DictExtra)
video_paths = [i for i in re.findall(r"(FileStorage.*?)'", DictExtra_str)]
video_paths = sorted(video_paths, key=lambda p: "mp4" in p, reverse=True)
if video_paths:
video_path = video_paths[0].replace("'", "")
video_path = [i for i in video_path.split("\\") if i]
video_path = os.path.join(*video_path)
src = video_path
else:
src = ""
msg = "影片"
elif type_id == (47, 0): # 動畫表情
content_tmp = xml2dict(StrContent)
cdnurl = content_tmp.get("emoji", {}).get("cdnurl", "")
if not cdnurl:
DictExtra = get_BytesExtra(BytesExtra)
cdnurl = match_BytesExtra(DictExtra)
if cdnurl:
msg, src = "表情", cdnurl
elif type_id == (48, 0): # 地圖資訊
content_tmp = xml2dict(StrContent)
location = content_tmp.get("location", {})
msg = (f"緯度:【{location.pop('x')}】 經度:【{location.pop('y')}】\n"
f"位置：{location.pop('label')} {location.pop('poiname')}\n"
f"其他資訊：{json.dumps(location, ensure_ascii=False, indent=4)}"
)
src = ""
elif type_id == (49, 0): # 檔案
DictExtra = get_BytesExtra(BytesExtra)
url = match_BytesExtra(DictExtra)
src = url
file_name = os.path.basename(url)
msg = file_name
elif type_id == (49, 5): # (分享)卡片式連結
CompressContent = decompress_CompressContent(CompressContent)
CompressContent_tmp = xml2dict(CompressContent)
appmsg = CompressContent_tmp.get("appmsg", {})
title = appmsg.get("title", "")
des = appmsg.get("des", "")
url = appmsg.get("url", "")
msg = f'{title}\n{des}\n\n<a href="{url}" target="_blank">點選檢視詳情</a>'
src = url
extra = appmsg
elif type_id == (49, 19): # 合併轉發的聊天記錄
CompressContent = decompress_CompressContent(CompressContent)
content_tmp = xml2dict(CompressContent)
title = content_tmp.get("appmsg", {}).get("title", "")
des = content_tmp.get("appmsg", {}).get("des", "")
recorditem = content_tmp.get("appmsg", {}).get("recorditem", "")
recorditem = xml2dict(recorditem)
msg = f"{title}\n{des}"
src = recorditem
elif type_id == (49, 57): # 帶有引用的文字訊息
CompressContent = decompress_CompressContent(CompressContent)
content_tmp = xml2dict(CompressContent)
appmsg = content_tmp.get("appmsg", {})
title = appmsg.get("title", "")
refermsg = appmsg.get("refermsg", {})
type_id = appmsg.get("type", "1")
displayname = refermsg.get("displayname", "")
display_content = refermsg.get("content", "")
display_createtime = refermsg.get("createtime", "")
display_createtime = timestamp2str(
int(display_createtime)) if display_createtime.isdigit() else display_createtime
if display_content and display_content.startswith("<?xml"):
display_content = xml2dict(display_content)
if "img" in display_content:
display_content = "圖片"
else:
appmsg1 = display_content.get("appmsg", {})
title1 = appmsg1.get("title", "")
display_content = title1 if title1 else display_content
msg = f"{title}\n\n[引用]({display_createtime}){displayname}:{display_content}"
src = ""
elif type_id == (49, 2000): # 轉賬訊息
CompressContent = decompress_CompressContent(CompressContent)
content_tmp = xml2dict(CompressContent)
wcpayinfo = content_tmp.get("appmsg", {}).get("wcpayinfo", {})
paysubtype = wcpayinfo.get("paysubtype", "") # 轉賬型別
feedesc = wcpayinfo.get("feedesc", "") # 轉賬金額
pay_memo = wcpayinfo.get("pay_memo", "") # 轉賬備註
begintransfertime = wcpayinfo.get("begintransfertime", "") # 轉賬開始時間
msg = (f"{'已收款' if paysubtype == '3' else '轉賬'}：{feedesc}\n"
f"轉賬說明：{pay_memo if pay_memo else ''}\n"
f"轉賬時間：{timestamp2str(begintransfertime)}\n"
)
src = ""
elif type_id[0] == 49 and type_id[1] != 0:
DictExtra = get_BytesExtra(BytesExtra)
url = match_BytesExtra(DictExtra)
src = url
msg = type_name
elif type_id == (50, 0): # 語音通話
msg = "語音/視訊通話[%s]" % DisplayContent
# elif type_id == (10000, 0):
# msg = StrContent
# elif type_id == (10000, 4):
# msg = StrContent
# elif type_id == (10000, 8000):
# msg = StrContent
talker = "未知"
if IsSender == 1:
talker = my_talker
else:
if StrTalker.endswith("@chatroom"):
bytes_extra = get_BytesExtra(BytesExtra)
if bytes_extra:
try:
talker = bytes_extra['3'][0]['2']
if "publisher-id" in talker:
talker = "系統"
except:
pass
else:
talker = StrTalker
row_data = {"id": _id, "MsgSvrID": str(MsgSvrID), "type_name": type_name, "is_sender": IsSender,
"talker": talker, "room_name": StrTalker, "msg": msg, "src": src, "extra": extra,
"CreateTime": CreateTime, }
return row_data
@db_error
def decompress_CompressContent(data):
"""
解壓縮Msg：CompressContent內容
:param data: CompressContent內容 bytes
:return:
"""
if data is None or not isinstance(data, bytes):
return None
try:
dst = lz4.block.decompress(data, uncompressed_size=len(data) << 8)
dst = dst.replace(b'\x00', b'') # 已經解碼完成後，還含有0x00的部分，要刪掉，要不後面ET識別的時候會報錯
uncompressed_data = dst.decode('utf-8', errors='ignore')
return uncompressed_data
except Exception as e:
return data.decode('utf-8', errors='ignore')
@db_error
def get_BytesExtra(BytesExtra):
BytesExtra_message_type = {
"1": {
"type": "message",
"message_typedef": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "int",
"name": ""
}
},
"name": "1"
},
"3": {
"type": "message",
"message_typedef": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "str",
"name": ""
}
},
"name": "3",
"alt_typedefs": {
"1": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "message",
"message_typedef": {},
"name": ""
}
},
"2": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "message",
"message_typedef": {
"13": {
"type": "fixed32",
"name": ""
},
"12": {
"type": "fixed32",
"name": ""
}
},
"name": ""
}
},
"3": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "message",
"message_typedef": {
"15": {
"type": "fixed64",
"name": ""
}
},
"name": ""
}
},
"4": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "message",
"message_typedef": {
"15": {
"type": "int",
"name": ""
},
"14": {
"type": "fixed32",
"name": ""
}
},
"name": ""
}
},
"5": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "message",
"message_typedef": {
"12": {
"type": "fixed32",
"name": ""
},
"7": {
"type": "fixed64",
"name": ""
},
"6": {
"type": "fixed64",
"name": ""
}
},
"name": ""
}
},
"6": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "message",
"message_typedef": {
"7": {
"type": "fixed64",
"name": ""
},
"6": {
"type": "fixed32",
"name": ""
}
},
"name": ""
}
},
"7": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "message",
"message_typedef": {
"12": {
"type": "fixed64",
"name": ""
}
},
"name": ""
}
},
"8": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "message",
"message_typedef": {
"6": {
"type": "fixed64",
"name": ""
},
"12": {
"type": "fixed32",
"name": ""
}
},
"name": ""
}
},
"9": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "message",
"message_typedef": {
"15": {
"type": "int",
"name": ""
},
"12": {
"type": "fixed64",
"name": ""
},
"6": {
"type": "int",
"name": ""
}
},
"name": ""
}
},
"10": {
"1": {
"type": "int",
"name": ""
},
"2": {
"type": "message",
"message_typedef": {
"6": {
"type": "fixed32",
"name": ""
},
"12": {
"type": "fixed64",
"name": ""
}
},
"name": ""
}
},
}
}
}
if BytesExtra is None or not isinstance(BytesExtra, bytes):
return None
try:
deserialize_data, message_type = blackboxprotobuf.decode_message(BytesExtra, BytesExtra_message_type)
return deserialize_data
except Exception as e:
return None
# -*- coding: utf-8 -*-#
# -------------------------------------------------------------------------------
# Name: OpenIMContact.py
# Description:
# Author: Rainbow
# Date: 2024/11/08
# -------------------------------------------------------------------------------
from .dbbase import DatabaseBase
from .utils import db_error
class OpenIMContactHandler(DatabaseBase):
_class_name = "OpenIMContact"
OpenIMContact_required_tables = ["OpenIMContact"]
def get_im_user_list(self, word=None, wxids=None):
"""
獲取聯絡人列表
[ 注意：如果修改這個函式，要同時修改dbMicro.py中的get_user_list函式 ]
:param word: 查詢關鍵字，可以是使用者名稱、暱稱、備註、描述，允許拼音
:param wxids: 微信id列表
:return: 聯絡人字典
"""
if not self.tables_exist("OpenIMContact"):
return []
if not wxids:
wxids = {}
if isinstance(wxids, str):
wxids = [wxids]
sql = ("SELECT UserName,NickName,Type,Remark,BigHeadImgUrl,CustomInfoDetail,CustomInfoDetailVisible,"
"AntiSpamTicket,AppId,Sex,DescWordingId,ExtraBuf "
"FROM OpenIMContact WHERE 1==1 ;")
if word:
sql = sql.replace(";",
f"AND (UserName LIKE '%{word}%' "
f"OR NickName LIKE '%{word}%' "
f"OR Remark LIKE '%{word}%' "
f"OR LOWER(NickNamePYInit) LIKE LOWER('%{word}%') "
f"OR LOWER(NickNameQuanPin) LIKE LOWER('%{word}%') "
f"OR LOWER(RemarkPYInit) LIKE LOWER('%{word}%') "
f"OR LOWER(RemarkQuanPin) LIKE LOWER('%{word}%') "
") ;")
if wxids:
sql = sql.replace(";", f"AND UserName IN ('" + "','".join(wxids) + "') ;")
result = self.execute(sql)
if not result:
return {}
users = {}
for row in result:
# 獲取使用者名稱、暱稱、備註和聊天記錄數量
(UserName, NickName, Type, Remark, BigHeadImgUrl, CustomInfoDetail, CustomInfoDetailVisible,
AntiSpamTicket, AppId, Sex, DescWordingId, ExtraBuf) = row
users[UserName] = {
"wxid": UserName, "nickname": NickName, "remark": Remark, "account": UserName,
"describe": '', "headImgUrl": BigHeadImgUrl if BigHeadImgUrl else "",
"ExtraBuf": None, "LabelIDList": tuple(), "extra": None}
return users
@db_error
def get_ExtraBuf(ExtraBuf: bytes):
"""
讀取ExtraBuf（聯絡人表）
:param ExtraBuf:
:return:
"""
if not ExtraBuf:
return None
buf_dict = {
'74752C06': '性別[1男2女]', '46CF10C4': '個性簽名', 'A4D9024A': '國', 'E2EAA8D1': '省', '1D025BBF': '市',
'F917BCC0': '公司名稱', '759378AD': '手機號', '4EB96D85': '企微屬性', '81AE19B4': '朋友圈背景',
'0E719F13': '備註圖片', '945f3190': '備註圖片2',
'DDF32683': '0', '88E28FCE': '1', '761A1D2D': '2', '0263A0CB': '3', '0451FF12': '4', '228C66A8': '5',
'4D6C4570': '6', '4335DFDD': '7', 'DE4CDAEB': '8', 'A72BC20A': '9', '069FED52': '10', '9B0F4299': '11',
'3D641E22': '12', '1249822C': '13', 'B4F73ACB': '14', '0959EB92': '15', '3CF4A315': '16',
'C9477AC60201E44CD0E8': '17', 'B7ACF0F5': '18', '57A7B5A8': '19', '695F3170': '20', 'FB083DD9': '21',
'0240E37F': '22', '315D02A3': '23', '7DEC0BC3': '24', '16791C90': '25'
}
rdata = {}
for buf_name in buf_dict:
rdata_name = buf_dict[buf_name]
buf_name = bytes.fromhex(buf_name)
offset = ExtraBuf.find(buf_name)
if offset == -1:
rdata[rdata_name] = ""
continue
offset += len(buf_name)
type_id = ExtraBuf[offset: offset + 1]
offset += 1
if type_id == b"\x04":
rdata[rdata_name] = int.from_bytes(ExtraBuf[offset: offset + 4], "little")
elif type_id == b"\x18":
length = int.from_bytes(ExtraBuf[offset: offset + 4], "little")
rdata[rdata_name] = ExtraBuf[offset + 4: offset + 4 + length].decode("utf-16").rstrip("\x00")
elif type_id == b"\x17":
length = int.from_bytes(ExtraBuf[offset: offset + 4], "little")
rdata[rdata_name] = ExtraBuf[offset + 4: offset + 4 + length].decode("utf-8", errors="ignore").rstrip(
"\x00")
elif type_id == b"\x05":
rdata[rdata_name] = f"0x{ExtraBuf[offset: offset + 8].hex()}"
return rdata
# -*- coding: utf-8 -*-#
# -------------------------------------------------------------------------------
# Name: OpenIMMedia.py
# Description: 負責處理語音資料庫
# Author: Rainbow
# Date: 2024/11/08
# -------------------------------------------------------------------------------
from .dbbase import DatabaseBase
from .utils import silk2audio, db_loger
class OpenIMMediaHandler(DatabaseBase):
_class_name = "OpenIMMedia"
OpenIMMedia_required_tables = ["OpenIMMedia"]
def get_im_audio(self, MsgSvrID, is_play=False, is_wave=False, save_path=None, rate=24000):
if not self.tables_exist("OpenIMMedia"):
return False
sql = "select Buf from OpenIMMedia where Reserved0=? "
DBdata = self.execute(sql, (MsgSvrID,))
if not DBdata:
return False
if len(DBdata) == 0:
return False
data = DBdata[0][0] # [1:] + b'\xFF\xFF'
try:
pcm_data = silk2audio(buf_data=data, is_play=is_play, is_wave=is_wave, save_path=save_path, rate=rate)
return pcm_data
except Exception as e:
db_loger.warning(e, exc_info=True)
return False
# -*- coding: utf-8 -*-#
# -------------------------------------------------------------------------------
# Name: PublicMsg.py
# Description: 負責處理公眾號資料庫資訊
# Author: Rainbow
# Date: 2024/11/08
# -------------------------------------------------------------------------------
from .dbMSG import MsgHandler
from .utils import db_error
class PublicMsgHandler(MsgHandler):
_class_name = "PublicMSG"
PublicMSG_required_tables = ["PublicMsg"]
def PublicMsg_add_index(self):
"""
新增索引,加快查詢速度
"""
# 檢查是否存在索引
if not self.tables_exist("PublicMsg"):
return
sql = "CREATE INDEX IF NOT EXISTS idx_PublicMsg_StrTalker ON PublicMsg(StrTalker);"
self.execute(sql)
sql = "CREATE INDEX IF NOT EXISTS idx_PublicMsg_CreateTime ON PublicMsg(CreateTime);"
self.execute(sql)
sql = "CREATE INDEX IF NOT EXISTS idx_PublicMsg_StrTalker_CreateTime ON PublicMsg(StrTalker, CreateTime);"
self.execute(sql)
@db_error
def get_plc_msg_count(self, wxids: list = ""):
"""
獲取聊天記錄數量,根據wxid獲取單個聯絡人的聊天記錄數量，不傳wxid則獲取所有聯絡人的聊天記錄數量
:param wxids: wxid list
:return: 聊天記錄數量列表 {wxid: chat_count}
"""
if not self.tables_exist("PublicMsg"):
return {}
if isinstance(wxids, str) and wxids:
wxids = [wxids]
if wxids:
wxids = "('" + "','".join(wxids) + "')"
sql = f"SELECT StrTalker, COUNT(*) FROM PublicMsg WHERE StrTalker IN {wxids} GROUP BY StrTalker ORDER BY COUNT(*) DESC;"
else:
sql = f"SELECT StrTalker, COUNT(*) FROM PublicMsg GROUP BY StrTalker ORDER BY COUNT(*) DESC;"
sql_total = f"SELECT COUNT(*) FROM MSG;"
result = self.execute(sql)
total_ret = self.execute(sql_total)
if not result:
return {}
total = 0
if total_ret and len(total_ret) > 0:
total = total_ret[0][0]
msg_count = {"total": total}
msg_count.update({row[0]: row[1] for row in result})
return msg_count
@db_error
def get_plc_msg_list(self, wxids: list or str = "", start_index=0, page_size=500, msg_type: str = "",
msg_sub_type: str = "", start_createtime=None, end_createtime=None, my_talker="我"):
"""
獲取聊天記錄列表
:param wxids: [wxid]
:param start_index: 起始索引
:param page_size: 頁大小
:param msg_type: 訊息型別
:param msg_sub_type: 訊息子型別
:param start_createtime: 開始時間
:param end_createtime: 結束時間
:return: 聊天記錄列表 {"id": _id, "MsgSvrID": str(MsgSvrID), "type_name": type_name, "is_sender": IsSender,
"talker": talker, "room_name": StrTalker, "msg": msg, "src": src, "extra": {},
"CreateTime": CreateTime, }
"""
if not self.tables_exist("PublicMsg"):
return [], []
if isinstance(wxids, str) and wxids:
wxids = [wxids]
param = ()
sql_wxid, param = (f"AND StrTalker in ({', '.join('?' for _ in wxids)}) ",
param + tuple(wxids)) if wxids else ("", param)
sql_type, param = ("AND Type=? ", param + (msg_type,)) if msg_type else ("", param)
sql_sub_type, param = ("AND SubType=? ", param + (msg_sub_type,)) if msg_type and msg_sub_type else ("", param)
sql_start_createtime, param = ("AND CreateTime>=? ", param + (start_createtime,)) if start_createtime else (
"", param)
sql_end_createtime, param = ("AND CreateTime<=? ", param + (end_createtime,)) if end_createtime else ("", param)
sql = (
"SELECT localId,TalkerId,MsgSvrID,Type,SubType,CreateTime,IsSender,Sequence,StatusEx,FlagEx,Status,"
"MsgSequence,StrContent,MsgServerSeq,StrTalker,DisplayContent,Reserved0,Reserved1,Reserved3,"
"Reserved4,Reserved5,Reserved6,CompressContent,BytesExtra,BytesTrans,Reserved2,"
"ROW_NUMBER() OVER (ORDER BY CreateTime ASC) AS id "
"FROM PublicMsg WHERE 1=1 "
f"{sql_wxid}"
f"{sql_type}"
f"{sql_sub_type}"
f"{sql_start_createtime}"
f"{sql_end_createtime}"
f"ORDER BY CreateTime ASC LIMIT ?,?"
)
param = param + (start_index, page_size)
result = self.execute(sql, param)
if not result:
return [], []
result_data = (self.get_msg_detail(row, my_talker=my_talker) for row in result)
rdata = list(result_data) # 轉為列表
wxid_list = {d['talker'] for d in rdata} # 建立一個無重複的 wxid 列表
return rdata, list(wxid_list)
# -*- coding: utf-8 -*-#
# -------------------------------------------------------------------------------
# Name: Sns.py
# Description: 負責處理朋友圈相關資料 軟體只能看到在電腦微信瀏覽過的朋友圈記錄
# Author: Rainbow
# Date: 2024/11/08
# -------------------------------------------------------------------------------
import json
from .dbbase import DatabaseBase
from .utils import silk2audio, xml2dict, timestamp2str
# FeedsV20：朋友圈的XML資料
# CommentV20：朋友圈點贊或評論記錄
# NotificationV7：朋友圈通知
# SnsConfigV20：一些配置資訊，能讀懂的是其中有你的朋友圈背景圖
# SnsGroupInfoV5：猜測是舊版微信朋友圈可見範圍的可見或不可見名單
class SnsHandler(DatabaseBase):
_class_name = "Sns"
Media_required_tables = ["AdFeedsV8", "FeedsV20", "CommentV20", "NotificationV7", "SnsConfigV20", "SnsFailureV5",
"SnsGroupInfoV5", "SnsNoNotifyV5"]
def get_sns_feed(self):
"""
獲取朋友圈資料
http://shmmsns.qpic.cn/mmsns/uGxMq1C4wvppcjBbyweK796GtT1hH3LGISYajZ2v7C11XhHk5icyDUXcWNSPk2MooeIa8Es5hXP0/0?idx=1&token=WSEN6qDsKwV8A02w3onOGQYfxnkibdqSOkmHhZGNB4DFumlE9p1vp0e0xjHoXlbbXRzwnQia6X5t3Annc4oqTuDg
"""
sql = (
"SELECT FeedId, CreateTime, FaultId, Type, UserName, Status, ExtFlag, PrivFlag, StringId, Content "
"FROM FeedsV20 "
"ORDER BY CreateTime DESC")
FeedsV20 = self.execute(sql)
for row in FeedsV20[2:]:
(FeedId, CreateTime, FaultId, Type, UserName, Status, ExtFlag, PrivFlag, StringId, Content) = row
Content = xml2dict(Content) if Content and Content.startswith("<") else Content
CreateTime = timestamp2str(CreateTime)
print(
f"{FeedId=}\n"
f"{CreateTime=}\n"
f"{FaultId=}\n"
f"{Type=}\n"
f"{UserName=}\n"
f"{Status=}\n"
f"{ExtFlag=}\n"
f"{PrivFlag=}\n"
f"{StringId=}\n\n"
f"{json.dumps(Content, indent=4, ensure_ascii=False)}\n\n"
)
return FeedId, CreateTime, FaultId, Type, UserName, Status, ExtFlag, PrivFlag, StringId, Content
def get_sns_comment(self):
pass