{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from auto_everything.base import IO, Terminal\n", "io = IO()\n", "t_ = Terminal()\n", "\n", "from IPython.display import clear_output" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "text = io.read(\"./raw/2019年.md\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "text" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import re" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "s = \"\"\"\n", "# 2012年\n", "\n", "## 12月\n", "\n", "\n", "--------------------\n", "\"\"\"\n", "re.sub(r\"#+\\s\\d+.\", \"yingshaoxo\", s)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "text = re.sub(r\"#+\\s\\d+.\", \"\", text)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "everyday = [t for t in text.split(\"\\n---\\n\") if t.strip() != \"\"]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from pprint import pprint\n", "pprint(everyday)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "source = \"\"\"\n", "> 2012-12-31 20:06:20\n", "\n", "萧静腾那个垃圾，简直垫污了《我的歌声里》这首歌！最后假唱！！！\n", "\n", "> 评论(2)：\n", "\n", "* [范琴[胡勇的妹](https://user.qzone.qq.com/228533964)：你啥子意思嘛！哼\n", "\t* [yingshaoxo](https://user.qzone.qq.com/1576570260)：本来就是“心若倦了…”就开始假唱了…垃圾、勒色、\n", "\t* [范琴[胡勇的妹](https://user.qzone.qq.com/228533964)：哼！\n", "* [胡勇](https://user.qzone.qq.com/1264313808)：咳咳\n", "\"\"\"\n", "date_expression = r\"(>\\s+)(\\d+-\\d+-\\d+\\s+\\d+:\\d+:\\d+)\"\n", "\n", "re.search(date_expression, source).group(2)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "source = \"\"\"\n", "> 2012-12-28 17:48:55【转发】\n", "\n", "对于那群恐龙男，不必说什么\n", "\n", "> 原文:\n", "\n", "[就是我。](https://user.qzone.qq.com/447993638)：对于畜牲我们要表示无语，毕竟它们的智商跟我们[@選擇](https://user.qzone.qq.com/751112688) [@妮](https://user.qzone.qq.com/1093944276) @花少少 不是同一水平的。。。。\n", "> 评论(0)：\n", "\"\"\"\n", "forwarded_message_expression = r\"(>\\s+原文:\\s+)(.+)(>\\s+)\"\n", "re.search(forwarded_message_expression, source, flags=re.DOTALL).group(2)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "re.sub(forwarded_message_expression, r\"\\3\", source, flags=re.DOTALL)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "source = \"\"\"\n", "萧静腾那个垃圾，简直垫污了《我的歌声里》这首歌！最后假唱！！！\n", "\n", "> 评论(2)：\n", "\n", "* [范琴[胡勇的妹](https://user.qzone.qq.com/228533964)：你啥子意思嘛！哼\n", "\t* [yingshaoxo](https://user.qzone.qq.com/1576570260)：本来就是“心若倦了…”就开始假唱了…垃圾、勒色、\n", "\t* [范琴[胡勇的妹](https://user.qzone.qq.com/228533964)：哼！\n", "* [胡勇](https://user.qzone.qq.com/1264313808)：咳咳\n", "\"\"\"\n", "comments_expression = r\"(>\\s+评论\\(\\d+\\)：\\s*)(.*)\"\n", "re.search(comments_expression, source, flags=re.DOTALL).group(2)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "re.sub(comments_expression, \"\", source, flags=re.DOTALL)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "for oneday in everyday:\n", " # get date\n", " try:\n", " date = re.search(date_expression, oneday).group(2).strip()\n", " except Exception as e:\n", " print(oneday)\n", " raise Exception(e)\n", " # delete date\n", " oneday = re.sub(date_expression + r\"(【转发】)*\", \"\", oneday).strip()\n", " \n", " # get forwarded message\n", " forwareded = re.search(forwarded_message_expression, oneday, flags=re.DOTALL)\n", " if forwareded:\n", " forwareded = forwareded.group(2).strip()\n", " # delete forwarded message\n", " oneday = re.sub(forwarded_message_expression, r\"\\3\", oneday, flags=re.DOTALL)\n", " else:\n", " forwareded = \"\"\n", " \n", " # get comments\n", " comments = re.search(comments_expression, oneday, flags=re.DOTALL)\n", " if comments:\n", " comments = comments.group(2).strip()\n", " # delete forwarded message\n", " oneday = re.sub(comments_expression, \"\", oneday, flags=re.DOTALL)\n", " else:\n", " comments = \"\"\n", " \n", " # get message\n", " message = oneday.strip()\n", " \n", " print(\"#\"*20)\n", " print(date)\n", " print(\"#\"*20)\n", " if message:\n", " print(message)\n", " print(\"#\"*20)\n", " if forwareded:\n", " print(forwareded)\n", " print(\"#\"*20)\n", " if comments:\n", " print(comments)\n", " print(\"\\n\"* 3)\n", " print(\"=\" * 60)\n", " print(\"\\n\"*3)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "source = \"\"\"\n", "踢人当然是需要理由的。\n", "\n", "\n", "\n", "
\n", "\n", "\n", "
\n", "\"\"\"\n", "\n", "div_expression = r'
.+
'\n", "div = re.search(div_expression, source, flags=re.DOTALL).group(0)\n", "print(div)\n", "print()\n", "\n", "#image_expression = r'(\")(图片/.+)(\"\\s.+\\s/>)'\n", "image_expression = r'(\")(图片/\\w+\\.\\w+)'\n", "images = re.findall(image_expression, div)\n", "pprint(images)\n", "print()\n", "\n", "images = [image[1] for image in images]\n", "print(images)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "re.sub(div_expression, \"\", source, flags=re.DOTALL)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import os" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "everyday_list = []\n", "\n", "def get_images(oneday):\n", " div = re.search(div_expression, oneday, flags=re.DOTALL)\n", " if div:\n", " div = div.group(0)\n", " images = re.findall(image_expression, div)\n", " images = [image[1] for image in images]\n", " oneday = re.sub(div_expression, \"\", oneday, flags=re.DOTALL)\n", " else:\n", " images = []\n", " return images, oneday\n", "\n", "for i in reversed(os.listdir(\"./raw\")):\n", " if i[-3:] == \".md\":\n", " print(i)\n", " path = \"./raw/\" + i\n", " text = io.read(path)\n", " text = re.sub(r\"#+\\s\\d+.\", \"\", text)\n", " everyday = [t for t in text.split(\"\\n---\\n\") if t.strip() != \"\"]\n", " for oneday in everyday:\n", " # get date\n", " try:\n", " date = re.search(date_expression, oneday).group(2).strip()\n", " except Exception as e:\n", " print(oneday)\n", " raise Exception(e)\n", " # delete date\n", " oneday = re.sub(date_expression + r\"(【转发】)*\", \"\", oneday).strip()\n", "\n", " # get forwarded message\n", " forwareded = re.search(forwarded_message_expression, oneday, flags=re.DOTALL)\n", " if forwareded:\n", " forwareded = forwareded.group(2).strip()\n", " # delete forwarded message\n", " oneday = re.sub(forwarded_message_expression, r\"\\3\", oneday, flags=re.DOTALL)\n", " else:\n", " forwareded = \"\"\n", "\n", " # get comments\n", " comments = re.search(comments_expression, oneday, flags=re.DOTALL)\n", " if comments:\n", " comments = comments.group(2).strip()\n", " # delete forwarded message\n", " oneday = re.sub(comments_expression, \"\", oneday, flags=re.DOTALL)\n", " else:\n", " comments = \"\"\n", "\n", " # get message\n", " message = oneday.strip()\n", "\n", "\n", " print(\"#\"*50)\n", " oneday_dict[\"date\"] = date\n", " print(date)\n", " print(\"*\"*20)\n", " images = []\n", " if message:\n", " new_images, message = get_images(message)\n", " images += new_images\n", " oneday_dict[\"message\"] = message\n", " print(message)\n", " print(\"*\"*20)\n", " if forwareded:\n", " new_images, forwareded = get_images(forwareded)\n", " images += new_images\n", " oneday_dict[\"forwareded\"] = forwareded\n", " print(forwareded)\n", " print(\"*\"*20)\n", " if comments:\n", " new_images, comments = get_images(comments)\n", " images += new_images\n", " oneday_dict[\"comments\"] = comments\n", " print(comments)\n", " if images:\n", " oneday_dict[\"images\"] = images\n", " print(\"*\"*20)\n", " print(images)\n", " print(\"\\n\"*3)\n", " everyday_list.append(oneday_dict)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "everyday_list = []\n", "\n", "for i in reversed(os.listdir(\"./raw\")):\n", " if i[-3:] == \".md\":\n", " print(i)\n", " path = \"./raw/\" + i\n", " text = io.read(path)\n", " text = re.sub(r\"#+\\s\\d+.\", \"\", text)\n", " everyday = [t for t in text.split(\"\\n---\\n\") if t.strip() != \"\"]\n", " for oneday in everyday:\n", " oneday_dict = {}\n", " \n", " # get date\n", " date = re.search(date_expression, oneday).group(2).strip()\n", " # delete date\n", " oneday = re.sub(date_expression + r\"(【转发】)*\", \"\", oneday).strip()\n", "\n", " # get images\n", " div = re.search(div_expression, oneday, flags=re.DOTALL)\n", " if div:\n", " div = div.group(0)\n", " images = re.findall(image_expression, div)\n", " images = [image[1] for image in images]\n", " oneday = re.sub(div_expression, \"\", oneday, flags=re.DOTALL)\n", " else:\n", " images = []\n", " \n", " # get forwarded message\n", " forwareded = re.search(forwarded_message_expression, oneday, flags=re.DOTALL)\n", " if forwareded:\n", " forwareded = forwareded.group(2).strip()\n", " # delete forwarded message\n", " oneday = re.sub(forwarded_message_expression, r\"\\3\", oneday, flags=re.DOTALL)\n", " else:\n", " forwareded = \"\"\n", "\n", " # get comments\n", " comments = re.search(comments_expression, oneday, flags=re.DOTALL)\n", " if comments:\n", " comments = comments.group(2).strip()\n", " # delete forwarded message\n", " oneday = re.sub(comments_expression, \"\", oneday, flags=re.DOTALL)\n", " else:\n", " comments = \"\"\n", " \n", " # get message\n", " message = oneday.strip()\n", "\n", " print(\"_\"*20)\n", " print(date)\n", " oneday_dict[\"date\"] = date\n", " print(\"_\"*20)\n", " if message:\n", " print(message)\n", " oneday_dict[\"message\"] = message\n", " print(\"_\"*20)\n", " if images:\n", " print(images)\n", " oneday_dict[\"images\"] = images\n", " print(\"_\"*20)\n", " if forwareded:\n", " print(forwareded)\n", " oneday_dict[\"forwareded\"] = forwareded\n", " print(\"_\"*20)\n", " if comments:\n", " print(comments)\n", " oneday_dict[\"comments\"] = comments\n", " print(\"\\n\"*3)\n", " everyday_list.append(oneday_dict)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "len(everyday_list)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from datetime import datetime\n", "\n", "time = \"2020-01-16 05:42:12\"\n", "datetime.fromisoformat(time)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "everyday_list = sorted(everyday_list, key = lambda i: datetime.fromisoformat(i['date'])) \n", "everyday_list[-100:]" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import json\n", "\n", "io.write(\"MyQzoneData.json\", json.dumps(everyday_list, indent=4))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import json\n", "import base64\n", "import re\n", "\n", "from auto_everything.base import IO, Terminal\n", "io = IO()\n", "t_ = Terminal()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "text = io.read(\"MyQzoneData.json\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "qzone = json.loads(text)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def image_to_uri(path):\n", " path = \"raw/\" + path\n", " formats = path.split(\".\")\n", " if formats:\n", " formats = formats[-1]\n", " encoded = base64.b64encode(open(path, \"rb\").read())\n", " return f\"data:image/{formats};base64,\" + encoded.decode(\"utf-8\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "days = []\n", "for day in qzone:\n", " date = day[\"date\"]\n", " \n", " if \"images\" in day:\n", " images = day[\"images\"]\n", " images = [image_to_uri(path) for path in images]\n", " else:\n", " images = []\n", " \n", " if \"message\" in day:\n", " message = day[\"message\"]\n", " else:\n", " message = \"\"\n", " \n", " if \"comments\" in day:\n", " comments = day[\"comments\"]\n", " comments = re.sub(r\"\\(http(.*?)\\)\", \"\", comments)\n", " comments = re.sub(r\"\\s*\\!\\[\\]\\(图片\\/(.*?)\\)\", \"\", comments)\n", " #comments = re.sub(r\"\\*\\s*\\[yingshaoxo\\]\\s\", \"\", comments)\n", " #comments = re.sub(r\"\\*\\s*\\[.+\\]\\s\", \"\", comments)\n", " else:\n", " comments = \"\"\n", " \n", " text = message + \"\\n\"*4 + comments\n", " text = text.strip()\n", " dictionary = (date, \"qzone\", text, json.dumps(images))\n", " days.append(dictionary)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "!rm qzone.db" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import os\n", "import sqlite3\n", "\n", "class Self():\n", " _root_path = \".\"\n", " pass\n", "\n", "self = Self()\n", "self.SQL_DATA_FILE = os.path.join(self._root_path, \"qzone.db\")\n", "if not os.path.exists(self.SQL_DATA_FILE):\n", " print(f\"SQL doesn't exist: {self.SQL_DATA_FILE}\")\n", " print(f\"We'll create a new one!\")\n", "self._sql_conn = sqlite3.connect(self.SQL_DATA_FILE, check_same_thread=False)\n", "\n", "def regular_expression(expr, item):\n", " reg = re.compile(expr, flags=re.DOTALL)\n", " return reg.search(item) is not None\n", "self._sql_conn.create_function(\"REGEXP\", 2, regular_expression) # 2 here means two parameters. REGEXP is a fixed value\n", "\n", "self._sql_cursor = self._sql_conn.cursor()\n", "self._sql_cursor.execute('''CREATE TABLE IF NOT EXISTS thoughts\n", " (date TEXT, type TEXT, text TEXT, images TEXT)''')\n", "\n", "for day in days:\n", " #print(day)\n", " self._sql_cursor.execute(\"INSERT INTO thoughts VALUES (?,?,?,?)\", day)\n", " self._sql_conn.commit()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "#1. create a memory-based database\n", "import sqlite3\n", "conn = sqlite3.connect(':memory:')\n", "\n", "#2. create a regular expression function inside of sql\n", "import re\n", "def regular_expression(expr, item):\n", " reg = re.compile(expr)\n", " return reg.search(item) is not None\n", "conn.create_function(\"REGEXP\", 2, regular_expression) # 2 here means two parameters. REGEXP is a fixed value\n", "\n", "#3. create a table, and insert two values to it\n", "cursor = conn.cursor()\n", "cursor.execute('CREATE TABLE aTable (aColumn TEXT)')\n", "cursor.executemany(\n", " 'INSERT INTO aTable (aColumn) VALUES (?)',\n", " [('aaa\"fuckingA\"',),('bbb\"fuckingB\"',), ('just\"AA\"',)])\n", "\n", "#4. select a column from table by re\n", "cursor.execute('SELECT aColumn FROM aTable WHERE aColumn REGEXP (?)',[r'\\w+\"\\w+B\"'])\n", "data=cursor.fetchall()\n", "print(data)\n", "print()\n", "\n", "#5. select rows from table by re\n", "for row in cursor.execute('SELECT * FROM aTable WHERE aColumn REGEXP (?)', [r'\\w+\"\\w+A\"']):\n", " print(row)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.5" } }, "nbformat": 4, "nbformat_minor": 2 }