count_datatype.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-10-24T13:29:49.738153Z",
     "start_time": "2024-10-24T13:29:40.718498Z"
    }
   },
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001B[1;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[1;31mKeyboardInterrupt\u001B[0m                         Traceback (most recent call last)",
      "Cell \u001B[1;32mIn[2], line 13\u001B[0m\n\u001B[0;32m     11\u001B[0m \u001B[38;5;66;03m# url = \"https://hf-mirror.com/datasets/cbdb/cbdb-sqlite/resolve/main/latest.7z?download=true\"\u001B[39;00m\n\u001B[0;32m     12\u001B[0m url \u001B[38;5;241m=\u001B[39m \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mhttps://huggingface.co/datasets/cbdb/cbdb-sqlite/resolve/main/latest.7z?download=true\u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[1;32m---> 13\u001B[0m r \u001B[38;5;241m=\u001B[39m \u001B[43mrequests\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mget\u001B[49m\u001B[43m(\u001B[49m\u001B[43murl\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mverify\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[38;5;28;43;01mFalse\u001B[39;49;00m\u001B[43m)\u001B[49m\n\u001B[0;32m     14\u001B[0m os\u001B[38;5;241m.\u001B[39mmakedirs(\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124m./cbdb_sqlite\u001B[39m\u001B[38;5;124m\"\u001B[39m, exist_ok\u001B[38;5;241m=\u001B[39m\u001B[38;5;28;01mTrue\u001B[39;00m)\n\u001B[0;32m     15\u001B[0m \u001B[38;5;28;01mwith\u001B[39;00m \u001B[38;5;28mopen\u001B[39m(\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124m./cbdb_sqlite/latest.7z\u001B[39m\u001B[38;5;124m\"\u001B[39m, \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mwb\u001B[39m\u001B[38;5;124m\"\u001B[39m) \u001B[38;5;28;01mas\u001B[39;00m f:\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\requests\\api.py:73\u001B[0m, in \u001B[0;36mget\u001B[1;34m(url, params, **kwargs)\u001B[0m\n\u001B[0;32m     62\u001B[0m \u001B[38;5;28;01mdef\u001B[39;00m \u001B[38;5;21mget\u001B[39m(url, params\u001B[38;5;241m=\u001B[39m\u001B[38;5;28;01mNone\u001B[39;00m, \u001B[38;5;241m*\u001B[39m\u001B[38;5;241m*\u001B[39mkwargs):\n\u001B[0;32m     63\u001B[0m \u001B[38;5;250m    \u001B[39m\u001B[38;5;124mr\u001B[39m\u001B[38;5;124;03m\"\"\"Sends a GET request.\u001B[39;00m\n\u001B[0;32m     64\u001B[0m \n\u001B[0;32m     65\u001B[0m \u001B[38;5;124;03m    :param url: URL for the new :class:`Request` object.\u001B[39;00m\n\u001B[1;32m   (...)\u001B[0m\n\u001B[0;32m     70\u001B[0m \u001B[38;5;124;03m    :rtype: requests.Response\u001B[39;00m\n\u001B[0;32m     71\u001B[0m \u001B[38;5;124;03m    \"\"\"\u001B[39;00m\n\u001B[1;32m---> 73\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m request(\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mget\u001B[39m\u001B[38;5;124m\"\u001B[39m, url, params\u001B[38;5;241m=\u001B[39mparams, \u001B[38;5;241m*\u001B[39m\u001B[38;5;241m*\u001B[39mkwargs)\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\requests\\api.py:59\u001B[0m, in \u001B[0;36mrequest\u001B[1;34m(method, url, **kwargs)\u001B[0m\n\u001B[0;32m     55\u001B[0m \u001B[38;5;66;03m# By using the 'with' statement we are sure the session is closed, thus we\u001B[39;00m\n\u001B[0;32m     56\u001B[0m \u001B[38;5;66;03m# avoid leaving sockets open which can trigger a ResourceWarning in some\u001B[39;00m\n\u001B[0;32m     57\u001B[0m \u001B[38;5;66;03m# cases, and look like a memory leak in others.\u001B[39;00m\n\u001B[0;32m     58\u001B[0m \u001B[38;5;28;01mwith\u001B[39;00m sessions\u001B[38;5;241m.\u001B[39mSession() \u001B[38;5;28;01mas\u001B[39;00m session:\n\u001B[1;32m---> 59\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m session\u001B[38;5;241m.\u001B[39mrequest(method\u001B[38;5;241m=\u001B[39mmethod, url\u001B[38;5;241m=\u001B[39murl, \u001B[38;5;241m*\u001B[39m\u001B[38;5;241m*\u001B[39mkwargs)\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\requests\\sessions.py:589\u001B[0m, in \u001B[0;36mSession.request\u001B[1;34m(self, method, url, params, data, headers, cookies, files, auth, timeout, allow_redirects, proxies, hooks, stream, verify, cert, json)\u001B[0m\n\u001B[0;32m    584\u001B[0m send_kwargs \u001B[38;5;241m=\u001B[39m {\n\u001B[0;32m    585\u001B[0m     \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mtimeout\u001B[39m\u001B[38;5;124m\"\u001B[39m: timeout,\n\u001B[0;32m    586\u001B[0m     \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mallow_redirects\u001B[39m\u001B[38;5;124m\"\u001B[39m: allow_redirects,\n\u001B[0;32m    587\u001B[0m }\n\u001B[0;32m    588\u001B[0m send_kwargs\u001B[38;5;241m.\u001B[39mupdate(settings)\n\u001B[1;32m--> 589\u001B[0m resp \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39msend(prep, \u001B[38;5;241m*\u001B[39m\u001B[38;5;241m*\u001B[39msend_kwargs)\n\u001B[0;32m    591\u001B[0m \u001B[38;5;28;01mreturn\u001B[39;00m resp\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\requests\\sessions.py:724\u001B[0m, in \u001B[0;36mSession.send\u001B[1;34m(self, request, **kwargs)\u001B[0m\n\u001B[0;32m    721\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m allow_redirects:\n\u001B[0;32m    722\u001B[0m     \u001B[38;5;66;03m# Redirect resolving generator.\u001B[39;00m\n\u001B[0;32m    723\u001B[0m     gen \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mresolve_redirects(r, request, \u001B[38;5;241m*\u001B[39m\u001B[38;5;241m*\u001B[39mkwargs)\n\u001B[1;32m--> 724\u001B[0m     history \u001B[38;5;241m=\u001B[39m [resp \u001B[38;5;28;01mfor\u001B[39;00m resp \u001B[38;5;129;01min\u001B[39;00m gen]\n\u001B[0;32m    725\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[0;32m    726\u001B[0m     history \u001B[38;5;241m=\u001B[39m []\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\requests\\sessions.py:724\u001B[0m, in \u001B[0;36m<listcomp>\u001B[1;34m(.0)\u001B[0m\n\u001B[0;32m    721\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m allow_redirects:\n\u001B[0;32m    722\u001B[0m     \u001B[38;5;66;03m# Redirect resolving generator.\u001B[39;00m\n\u001B[0;32m    723\u001B[0m     gen \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mresolve_redirects(r, request, \u001B[38;5;241m*\u001B[39m\u001B[38;5;241m*\u001B[39mkwargs)\n\u001B[1;32m--> 724\u001B[0m     history \u001B[38;5;241m=\u001B[39m [resp \u001B[38;5;28;01mfor\u001B[39;00m resp \u001B[38;5;129;01min\u001B[39;00m gen]\n\u001B[0;32m    725\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[0;32m    726\u001B[0m     history \u001B[38;5;241m=\u001B[39m []\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\requests\\sessions.py:265\u001B[0m, in \u001B[0;36mSessionRedirectMixin.resolve_redirects\u001B[1;34m(self, resp, req, stream, timeout, verify, cert, proxies, yield_requests, **adapter_kwargs)\u001B[0m\n\u001B[0;32m    263\u001B[0m     \u001B[38;5;28;01myield\u001B[39;00m req\n\u001B[0;32m    264\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[1;32m--> 265\u001B[0m     resp \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39msend(\n\u001B[0;32m    266\u001B[0m         req,\n\u001B[0;32m    267\u001B[0m         stream\u001B[38;5;241m=\u001B[39mstream,\n\u001B[0;32m    268\u001B[0m         timeout\u001B[38;5;241m=\u001B[39mtimeout,\n\u001B[0;32m    269\u001B[0m         verify\u001B[38;5;241m=\u001B[39mverify,\n\u001B[0;32m    270\u001B[0m         cert\u001B[38;5;241m=\u001B[39mcert,\n\u001B[0;32m    271\u001B[0m         proxies\u001B[38;5;241m=\u001B[39mproxies,\n\u001B[0;32m    272\u001B[0m         allow_redirects\u001B[38;5;241m=\u001B[39m\u001B[38;5;28;01mFalse\u001B[39;00m,\n\u001B[0;32m    273\u001B[0m         \u001B[38;5;241m*\u001B[39m\u001B[38;5;241m*\u001B[39madapter_kwargs,\n\u001B[0;32m    274\u001B[0m     )\n\u001B[0;32m    276\u001B[0m     extract_cookies_to_jar(\u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mcookies, prepared_request, resp\u001B[38;5;241m.\u001B[39mraw)\n\u001B[0;32m    278\u001B[0m     \u001B[38;5;66;03m# extract redirect url, if any, for the next loop\u001B[39;00m\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\requests\\sessions.py:746\u001B[0m, in \u001B[0;36mSession.send\u001B[1;34m(self, request, **kwargs)\u001B[0m\n\u001B[0;32m    743\u001B[0m         \u001B[38;5;28;01mpass\u001B[39;00m\n\u001B[0;32m    745\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m stream:\n\u001B[1;32m--> 746\u001B[0m     \u001B[43mr\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mcontent\u001B[49m\n\u001B[0;32m    748\u001B[0m \u001B[38;5;28;01mreturn\u001B[39;00m r\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\requests\\models.py:902\u001B[0m, in \u001B[0;36mResponse.content\u001B[1;34m(self)\u001B[0m\n\u001B[0;32m    900\u001B[0m         \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_content \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;01mNone\u001B[39;00m\n\u001B[0;32m    901\u001B[0m     \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[1;32m--> 902\u001B[0m         \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_content \u001B[38;5;241m=\u001B[39m \u001B[38;5;124;43mb\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mjoin\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43miter_content\u001B[49m\u001B[43m(\u001B[49m\u001B[43mCONTENT_CHUNK_SIZE\u001B[49m\u001B[43m)\u001B[49m\u001B[43m)\u001B[49m \u001B[38;5;129;01mor\u001B[39;00m \u001B[38;5;124mb\u001B[39m\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[0;32m    904\u001B[0m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_content_consumed \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;01mTrue\u001B[39;00m\n\u001B[0;32m    905\u001B[0m \u001B[38;5;66;03m# don't need to release the connection; that's been handled by urllib3\u001B[39;00m\n\u001B[0;32m    906\u001B[0m \u001B[38;5;66;03m# since we exhausted the data.\u001B[39;00m\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\requests\\models.py:820\u001B[0m, in \u001B[0;36mResponse.iter_content.<locals>.generate\u001B[1;34m()\u001B[0m\n\u001B[0;32m    818\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28mhasattr\u001B[39m(\u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mraw, \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mstream\u001B[39m\u001B[38;5;124m\"\u001B[39m):\n\u001B[0;32m    819\u001B[0m     \u001B[38;5;28;01mtry\u001B[39;00m:\n\u001B[1;32m--> 820\u001B[0m         \u001B[38;5;28;01myield from\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mraw\u001B[38;5;241m.\u001B[39mstream(chunk_size, decode_content\u001B[38;5;241m=\u001B[39m\u001B[38;5;28;01mTrue\u001B[39;00m)\n\u001B[0;32m    821\u001B[0m     \u001B[38;5;28;01mexcept\u001B[39;00m ProtocolError \u001B[38;5;28;01mas\u001B[39;00m e:\n\u001B[0;32m    822\u001B[0m         \u001B[38;5;28;01mraise\u001B[39;00m ChunkedEncodingError(e)\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\urllib3\\response.py:1060\u001B[0m, in \u001B[0;36mHTTPResponse.stream\u001B[1;34m(self, amt, decode_content)\u001B[0m\n\u001B[0;32m   1058\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[0;32m   1059\u001B[0m     \u001B[38;5;28;01mwhile\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m is_fp_closed(\u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_fp) \u001B[38;5;129;01mor\u001B[39;00m \u001B[38;5;28mlen\u001B[39m(\u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_decoded_buffer) \u001B[38;5;241m>\u001B[39m \u001B[38;5;241m0\u001B[39m:\n\u001B[1;32m-> 1060\u001B[0m         data \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mread\u001B[49m\u001B[43m(\u001B[49m\u001B[43mamt\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mamt\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mdecode_content\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mdecode_content\u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m   1062\u001B[0m         \u001B[38;5;28;01mif\u001B[39;00m data:\n\u001B[0;32m   1063\u001B[0m             \u001B[38;5;28;01myield\u001B[39;00m data\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\urllib3\\response.py:949\u001B[0m, in \u001B[0;36mHTTPResponse.read\u001B[1;34m(self, amt, decode_content, cache_content)\u001B[0m\n\u001B[0;32m    946\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28mlen\u001B[39m(\u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_decoded_buffer) \u001B[38;5;241m>\u001B[39m\u001B[38;5;241m=\u001B[39m amt:\n\u001B[0;32m    947\u001B[0m         \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_decoded_buffer\u001B[38;5;241m.\u001B[39mget(amt)\n\u001B[1;32m--> 949\u001B[0m data \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_raw_read\u001B[49m\u001B[43m(\u001B[49m\u001B[43mamt\u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m    951\u001B[0m flush_decoder \u001B[38;5;241m=\u001B[39m amt \u001B[38;5;129;01mis\u001B[39;00m \u001B[38;5;28;01mNone\u001B[39;00m \u001B[38;5;129;01mor\u001B[39;00m (amt \u001B[38;5;241m!=\u001B[39m \u001B[38;5;241m0\u001B[39m \u001B[38;5;129;01mand\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m data)\n\u001B[0;32m    953\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m data \u001B[38;5;129;01mand\u001B[39;00m \u001B[38;5;28mlen\u001B[39m(\u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_decoded_buffer) \u001B[38;5;241m==\u001B[39m \u001B[38;5;241m0\u001B[39m:\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\urllib3\\response.py:873\u001B[0m, in \u001B[0;36mHTTPResponse._raw_read\u001B[1;34m(self, amt, read1)\u001B[0m\n\u001B[0;32m    870\u001B[0m fp_closed \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mgetattr\u001B[39m(\u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_fp, \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mclosed\u001B[39m\u001B[38;5;124m\"\u001B[39m, \u001B[38;5;28;01mFalse\u001B[39;00m)\n\u001B[0;32m    872\u001B[0m \u001B[38;5;28;01mwith\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_error_catcher():\n\u001B[1;32m--> 873\u001B[0m     data \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_fp_read\u001B[49m\u001B[43m(\u001B[49m\u001B[43mamt\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mread1\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mread1\u001B[49m\u001B[43m)\u001B[49m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m fp_closed \u001B[38;5;28;01melse\u001B[39;00m \u001B[38;5;124mb\u001B[39m\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[0;32m    874\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m amt \u001B[38;5;129;01mis\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;28;01mNone\u001B[39;00m \u001B[38;5;129;01mand\u001B[39;00m amt \u001B[38;5;241m!=\u001B[39m \u001B[38;5;241m0\u001B[39m \u001B[38;5;129;01mand\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m data:\n\u001B[0;32m    875\u001B[0m         \u001B[38;5;66;03m# Platform-specific: Buggy versions of Python.\u001B[39;00m\n\u001B[0;32m    876\u001B[0m         \u001B[38;5;66;03m# Close the connection when no data is returned\u001B[39;00m\n\u001B[1;32m   (...)\u001B[0m\n\u001B[0;32m    881\u001B[0m         \u001B[38;5;66;03m# not properly close the connection in all cases. There is\u001B[39;00m\n\u001B[0;32m    882\u001B[0m         \u001B[38;5;66;03m# no harm in redundantly calling close.\u001B[39;00m\n\u001B[0;32m    883\u001B[0m         \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_fp\u001B[38;5;241m.\u001B[39mclose()\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\urllib3\\response.py:856\u001B[0m, in \u001B[0;36mHTTPResponse._fp_read\u001B[1;34m(self, amt, read1)\u001B[0m\n\u001B[0;32m    853\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_fp\u001B[38;5;241m.\u001B[39mread1(amt) \u001B[38;5;28;01mif\u001B[39;00m amt \u001B[38;5;129;01mis\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;28;01mNone\u001B[39;00m \u001B[38;5;28;01melse\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_fp\u001B[38;5;241m.\u001B[39mread1()\n\u001B[0;32m    854\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[0;32m    855\u001B[0m     \u001B[38;5;66;03m# StringIO doesn't like amt=None\u001B[39;00m\n\u001B[1;32m--> 856\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_fp\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mread\u001B[49m\u001B[43m(\u001B[49m\u001B[43mamt\u001B[49m\u001B[43m)\u001B[49m \u001B[38;5;28;01mif\u001B[39;00m amt \u001B[38;5;129;01mis\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;28;01mNone\u001B[39;00m \u001B[38;5;28;01melse\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_fp\u001B[38;5;241m.\u001B[39mread()\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\http\\client.py:463\u001B[0m, in \u001B[0;36mHTTPResponse.read\u001B[1;34m(self, amt)\u001B[0m\n\u001B[0;32m    460\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m amt \u001B[38;5;129;01mis\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;28;01mNone\u001B[39;00m:\n\u001B[0;32m    461\u001B[0m     \u001B[38;5;66;03m# Amount is given, implement using readinto\u001B[39;00m\n\u001B[0;32m    462\u001B[0m     b \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mbytearray\u001B[39m(amt)\n\u001B[1;32m--> 463\u001B[0m     n \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mreadinto\u001B[49m\u001B[43m(\u001B[49m\u001B[43mb\u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m    464\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28mmemoryview\u001B[39m(b)[:n]\u001B[38;5;241m.\u001B[39mtobytes()\n\u001B[0;32m    465\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[0;32m    466\u001B[0m     \u001B[38;5;66;03m# Amount is not given (unbounded read) so we must check self.length\u001B[39;00m\n\u001B[0;32m    467\u001B[0m     \u001B[38;5;66;03m# and self.chunked\u001B[39;00m\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\http\\client.py:507\u001B[0m, in \u001B[0;36mHTTPResponse.readinto\u001B[1;34m(self, b)\u001B[0m\n\u001B[0;32m    502\u001B[0m         b \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mmemoryview\u001B[39m(b)[\u001B[38;5;241m0\u001B[39m:\u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mlength]\n\u001B[0;32m    504\u001B[0m \u001B[38;5;66;03m# we do not use _safe_read() here because this may be a .will_close\u001B[39;00m\n\u001B[0;32m    505\u001B[0m \u001B[38;5;66;03m# connection, and the user is reading more bytes than will be provided\u001B[39;00m\n\u001B[0;32m    506\u001B[0m \u001B[38;5;66;03m# (for example, reading in 1k chunks)\u001B[39;00m\n\u001B[1;32m--> 507\u001B[0m n \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mfp\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mreadinto\u001B[49m\u001B[43m(\u001B[49m\u001B[43mb\u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m    508\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m n \u001B[38;5;129;01mand\u001B[39;00m b:\n\u001B[0;32m    509\u001B[0m     \u001B[38;5;66;03m# Ideally, we would raise IncompleteRead if the content-length\u001B[39;00m\n\u001B[0;32m    510\u001B[0m     \u001B[38;5;66;03m# wasn't satisfied, but it might break compatibility.\u001B[39;00m\n\u001B[0;32m    511\u001B[0m     \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_close_conn()\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\socket.py:716\u001B[0m, in \u001B[0;36mSocketIO.readinto\u001B[1;34m(self, b)\u001B[0m\n\u001B[0;32m    714\u001B[0m \u001B[38;5;28;01mwhile\u001B[39;00m \u001B[38;5;28;01mTrue\u001B[39;00m:\n\u001B[0;32m    715\u001B[0m     \u001B[38;5;28;01mtry\u001B[39;00m:\n\u001B[1;32m--> 716\u001B[0m         \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_sock\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mrecv_into\u001B[49m\u001B[43m(\u001B[49m\u001B[43mb\u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m    717\u001B[0m     \u001B[38;5;28;01mexcept\u001B[39;00m timeout:\n\u001B[0;32m    718\u001B[0m         \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_timeout_occurred \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;01mTrue\u001B[39;00m\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\ssl.py:1275\u001B[0m, in \u001B[0;36mSSLSocket.recv_into\u001B[1;34m(self, buffer, nbytes, flags)\u001B[0m\n\u001B[0;32m   1271\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m flags \u001B[38;5;241m!=\u001B[39m \u001B[38;5;241m0\u001B[39m:\n\u001B[0;32m   1272\u001B[0m         \u001B[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mValueError\u001B[39;00m(\n\u001B[0;32m   1273\u001B[0m           \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mnon-zero flags not allowed in calls to recv_into() on \u001B[39m\u001B[38;5;132;01m%s\u001B[39;00m\u001B[38;5;124m\"\u001B[39m \u001B[38;5;241m%\u001B[39m\n\u001B[0;32m   1274\u001B[0m           \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m\u001B[38;5;18m__class__\u001B[39m)\n\u001B[1;32m-> 1275\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mread\u001B[49m\u001B[43m(\u001B[49m\u001B[43mnbytes\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mbuffer\u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m   1276\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[0;32m   1277\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28msuper\u001B[39m()\u001B[38;5;241m.\u001B[39mrecv_into(buffer, nbytes, flags)\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\ssl.py:1133\u001B[0m, in \u001B[0;36mSSLSocket.read\u001B[1;34m(self, len, buffer)\u001B[0m\n\u001B[0;32m   1131\u001B[0m \u001B[38;5;28;01mtry\u001B[39;00m:\n\u001B[0;32m   1132\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m buffer \u001B[38;5;129;01mis\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;28;01mNone\u001B[39;00m:\n\u001B[1;32m-> 1133\u001B[0m         \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_sslobj\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mread\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;28;43mlen\u001B[39;49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mbuffer\u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m   1134\u001B[0m     \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[0;32m   1135\u001B[0m         \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_sslobj\u001B[38;5;241m.\u001B[39mread(\u001B[38;5;28mlen\u001B[39m)\n",
      "\u001B[1;31mKeyboardInterrupt\u001B[0m: "
     ]
    }
   ],
   "source": [
    "# import patool\n",
    "# Download CBDB sqlite\n",
    "# !git clone https://github.com/cbdb-project/cbdb_sqlite.git\n",
    "import requests\n",
    "import os\n",
    "import warnings\n",
    "import urllib3\n",
    "\n",
    "# 禁用所有 InsecureRequestWarning 警告\n",
    "warnings.simplefilter('ignore', urllib3.exceptions.InsecureRequestWarning)\n",
    "\n",
    "# url = \"https://hf-mirror.com/datasets/cbdb/cbdb-sqlite/resolve/main/latest.7z?download=true\"\n",
    "url = \"https://huggingface.co/datasets/cbdb/cbdb-sqlite/resolve/main/latest.7z?download=true\"\n",
    "r = requests.get(url, verify=False)\n",
    "os.makedirs(\"./cbdb_sqlite\", exist_ok=True)\n",
    "with open(\"./cbdb_sqlite/latest.7z\", \"wb\") as f:\n",
    "    f.write(r.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-10-24T13:30:13.148618Z",
     "start_time": "2024-10-24T13:30:13.127619Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "./cbdb_sqlite/latest.7z\n",
      "latest.db\n"
     ]
    }
   ],
   "source": [
    "# import glob\n",
    "import os\n",
    "latest_file = \"./cbdb_sqlite/latest.7z\"\n",
    "print(latest_file)\n",
    "latest_db = latest_file.split(\"/\")[-1].split(\".\")[0] + \".db\"\n",
    "print(latest_db)\n",
    "if os.path.isfile(latest_db):\n",
    "    os.remove(latest_db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-10-24T13:42:00.514177Z",
     "start_time": "2024-10-24T13:42:00.469645Z"
    }
   },
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "patool not found! Please install patool!",
     "output_type": "error",
     "traceback": [
      "\u001B[1;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[1;31mValueError\u001B[0m                                Traceback (most recent call last)",
      "Cell \u001B[1;32mIn[15], line 4\u001B[0m\n\u001B[0;32m      1\u001B[0m \u001B[38;5;66;03m# # Explode latest database file\u001B[39;00m\n\u001B[0;32m      2\u001B[0m \u001B[38;5;66;03m# 打印 patool 的安装路径\u001B[39;00m\n\u001B[0;32m      3\u001B[0m \u001B[38;5;28;01mfrom\u001B[39;00m \u001B[38;5;21;01mpyunpack\u001B[39;00m \u001B[38;5;28;01mimport\u001B[39;00m Archive\n\u001B[1;32m----> 4\u001B[0m \u001B[43mArchive\u001B[49m\u001B[43m(\u001B[49m\u001B[43mlatest_file\u001B[49m\u001B[43m)\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mextractall\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;124;43m.\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[43m)\u001B[49m\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\pyunpack\\__init__.py:113\u001B[0m, in \u001B[0;36mArchive.extractall\u001B[1;34m(self, directory, auto_create_dir, patool_path)\u001B[0m\n\u001B[0;32m    111\u001B[0m         \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mextractall_zipfile(directory)\n\u001B[0;32m    112\u001B[0m     \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[1;32m--> 113\u001B[0m         \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mextractall_patool\u001B[49m\u001B[43m(\u001B[49m\u001B[43mdirectory\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mpatool_path\u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m    115\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mbackend \u001B[38;5;241m==\u001B[39m \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mzipfile\u001B[39m\u001B[38;5;124m\"\u001B[39m:\n\u001B[0;32m    116\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m is_zipfile:\n",
      "File \u001B[1;32mF:\\conda\\envs\\cbdbChange\\lib\\site-packages\\pyunpack\\__init__.py:58\u001B[0m, in \u001B[0;36mArchive.extractall_patool\u001B[1;34m(self, directory, patool_path)\u001B[0m\n\u001B[0;32m     56\u001B[0m     patool_path \u001B[38;5;241m=\u001B[39m _exepath(\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mpatool\u001B[39m\u001B[38;5;124m\"\u001B[39m)\n\u001B[0;32m     57\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m patool_path:\n\u001B[1;32m---> 58\u001B[0m     \u001B[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mValueError\u001B[39;00m(\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mpatool not found! Please install patool!\u001B[39m\u001B[38;5;124m\"\u001B[39m)\n\u001B[0;32m     59\u001B[0m patool_cmd \u001B[38;5;241m=\u001B[39m [\n\u001B[0;32m     60\u001B[0m     sys\u001B[38;5;241m.\u001B[39mexecutable,\n\u001B[0;32m     61\u001B[0m     patool_path,\n\u001B[1;32m   (...)\u001B[0m\n\u001B[0;32m     66\u001B[0m     \u001B[38;5;66;03m#                     '--verbose',\u001B[39;00m\n\u001B[0;32m     67\u001B[0m ]\n\u001B[0;32m     68\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mpassword \u001B[38;5;129;01mis\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;28;01mNone\u001B[39;00m:\n",
      "\u001B[1;31mValueError\u001B[0m: patool not found! Please install patool!"
     ]
    }
   ],
   "source": [
    "# # Explode latest database file\n",
    "# 打印 patool 的安装路径\n",
    "# from pyunpack import Archive\n",
    "# Archive(latest_file).extractall(\".\")"
   ]
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "解压完成！\n"
     ]
    }
   ],
   "source": [
    "import py7zr\n",
    "\n",
    "# 指定压缩文件和目标目录\n",
    "archive = './cbdb_sqlite/latest.7z'\n",
    "output_dir = ''\n",
    "\n",
    "# 解压文件\n",
    "with py7zr.SevenZipFile(archive, 'r') as archive:\n",
    "    archive.extractall(output_dir)\n",
    "\n",
    "print(\"解压完成！\")\n",
    "\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-10-24T13:58:01.467724Z",
     "start_time": "2024-10-24T13:57:39.826737Z"
    }
   },
   "execution_count": 20
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-10-25T03:34:27.147579Z",
     "start_time": "2024-10-25T03:34:24.269711Z"
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "import sqlite3\n",
    "import pandas as pd\n",
    "import os\n",
    "global CONN\n",
    "latest_file = \"./cbdb_sqlite/latest.7z\"\n",
    "database_file = os.path.basename(latest_file).split(\".\")[0]+\".db\"\n",
    "CONN = sqlite3.connect(database_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-10-25T03:34:30.751893Z",
     "start_time": "2024-10-25T03:34:30.735894Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT 'BIOG_MAIN' AS TableName, COUNT(*) AS RowCount FROM BIOG_MAIN UNION ALL SELECT 'ASSOC_DATA' AS TableName, COUNT(*) AS RowCount FROM ASSOC_DATA UNION ALL SELECT 'BIOG_ADDR_DATA' AS TableName, COUNT(*) AS RowCount FROM BIOG_ADDR_DATA UNION ALL SELECT 'ALTNAME_DATA' AS TableName, COUNT(*) AS RowCount FROM ALTNAME_DATA UNION ALL SELECT 'KIN_DATA' AS TableName, COUNT(*) AS RowCount FROM KIN_DATA UNION ALL SELECT 'ENTRY_DATA' AS TableName, COUNT(*) AS RowCount FROM ENTRY_DATA UNION ALL SELECT 'POSTED_TO_OFFICE_DATA' AS TableName, COUNT(*) AS RowCount FROM POSTED_TO_OFFICE_DATA UNION ALL SELECT 'STATUS_DATA' AS TableName, COUNT(*) AS RowCount FROM STATUS_DATA UNION ALL SELECT 'TEXT_CODES' AS TableName, COUNT(*) AS RowCount FROM TEXT_CODES\n"
     ]
    }
   ],
   "source": [
    "tables = ['BIOG_MAIN', 'ASSOC_DATA', 'BIOG_ADDR_DATA', 'ALTNAME_DATA', 'KIN_DATA', 'ENTRY_DATA', 'POSTED_TO_OFFICE_DATA', 'STATUS_DATA', 'TEXT_CODES']  \n",
    "sql_str = ''    \n",
    "for table in tables:  \n",
    "    sql_str += f\"SELECT '{table}' AS TableName, COUNT(*) AS RowCount FROM {table} UNION ALL \"    \n",
    "# 去除最后一个 UNION ALL  \n",
    "sql_str = sql_str[:-11]\n",
    "print(sql_str)\n",
    "# sql_str \n"
   ]
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据库中的表： [('ADDR_BELONGS_DATA',), ('ADDR_CODES',), ('ADDR_PLACE_DATA',), ('ADDR_XY',), ('ADDRESSES',), ('ALTNAME_CODES',), ('ALTNAME_DATA',), ('APPOINTMENT_TYPE_CODES',), ('ASSOC_CODE_TYPE_REL',), ('ASSOC_CODES',), ('ASSOC_DATA',), ('ASSOC_TYPES',), ('ASSUME_OFFICE_CODES',), ('BIOG_ADDR_CODES',), ('BIOG_ADDR_DATA',), ('BIOG_INST_CODES',), ('BIOG_INST_DATA',), ('BIOG_MAIN',), ('BIOG_SOURCE_DATA',), ('BIOG_TEXT_DATA',), ('CBDB_NAME_LIST',), ('CHORONYM_CODES',), ('CopyMissingTables',), ('CopyTables',), ('CopyTablesDefault',), ('COUNTRY_CODES',), ('DYNASTIES',), ('ENTRY_CODE_TYPE_REL',), ('ENTRY_CODES',), ('ENTRY_DATA',), ('ENTRY_TYPES',), ('ETHNICITY_TRIBE_CODES',), ('EVENT_CODES',), ('EVENTS_ADDR',), ('EVENTS_DATA',), ('EXTANT_CODES',), ('ForeignKeys',), ('FormLabels',), ('GANZHI_CODES',), ('HOUSEHOLD_STATUS_CODES',), ('INDEXYEAR_TYPE_CODES',), ('KIN_DATA',), ('KIN_Mourning',), ('KIN_MOURNING_STEPS',), ('KINSHIP_CODES',), ('LITERARYGENRE_CODES',), ('MEASURE_CODES',), ('NIAN_HAO',), ('OCCASION_CODES',), ('OFFICE_CATEGORIES',), ('OFFICE_CODE_TYPE_REL',), ('OFFICE_CODES',), ('OFFICE_CODES_CONVERSION',), ('OFFICE_TYPE_TREE',), ('OFFICE_TYPE_TREE_backup',), ('PARENTAL_STATUS_CODES',), ('PLACE_CODES',), ('POSSESSION_ACT_CODES',), ('POSSESSION_ADDR',), ('POSSESSION_DATA',), ('POSTED_TO_ADDR_DATA',), ('POSTED_TO_OFFICE_DATA',), ('POSTING_DATA',), ('SCHOLARLYTOPIC_CODES',), ('SOCIAL_INSTITUTION_ADDR',), ('SOCIAL_INSTITUTION_ADDR_TYPES',), ('SOCIAL_INSTITUTION_ALTNAME_CODES',), ('SOCIAL_INSTITUTION_ALTNAME_DATA',), ('SOCIAL_INSTITUTION_CODES',), ('SOCIAL_INSTITUTION_CODES_CONVERSION',), ('SOCIAL_INSTITUTION_NAME_CODES',), ('SOCIAL_INSTITUTION_TYPES',), ('STATUS_CODE_TYPE_REL',), ('STATUS_CODES',), ('STATUS_DATA',), ('STATUS_TYPES',), ('TablesFields',), ('TablesFieldsChanges',), ('TEXT_BIBLCAT_CODE_TYPE_REL',), ('TEXT_BIBLCAT_CODES',), ('TEXT_BIBLCAT_TYPES',), ('TEXT_CODES',), ('TEXT_INSTANCE_DATA',), ('TEXT_ROLE_CODES',), ('TEXT_TYPE',), ('TMP_INDEX_YEAR',), ('YEAR_RANGE_CODES',)]\n"
     ]
    }
   ],
   "source": [
    "cursor = CONN.cursor()\n",
    "cursor.execute(\"SELECT name FROM sqlite_master WHERE type='table';\")\n",
    "print(\"数据库中的表：\", cursor.fetchall())\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-10-25T03:34:34.359962Z",
     "start_time": "2024-10-25T03:34:34.339721Z"
    }
   },
   "execution_count": 5
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-10-25T03:34:39.980166Z",
     "start_time": "2024-10-25T03:34:37.826297Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "               TableName  RowCount\n0              BIOG_MAIN    641568\n1             ASSOC_DATA    187878\n2         BIOG_ADDR_DATA    440059\n3           ALTNAME_DATA    196850\n4               KIN_DATA    540631\n5             ENTRY_DATA    253437\n6  POSTED_TO_OFFICE_DATA   1976515\n7            STATUS_DATA     60912\n8             TEXT_CODES     57387",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>TableName</th>\n      <th>RowCount</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>BIOG_MAIN</td>\n      <td>641568</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>ASSOC_DATA</td>\n      <td>187878</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>BIOG_ADDR_DATA</td>\n      <td>440059</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>ALTNAME_DATA</td>\n      <td>196850</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>KIN_DATA</td>\n      <td>540631</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>ENTRY_DATA</td>\n      <td>253437</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>POSTED_TO_OFFICE_DATA</td>\n      <td>1976515</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>STATUS_DATA</td>\n      <td>60912</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>TEXT_CODES</td>\n      <td>57387</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tablecount_df = pd.read_sql_query(sql_str, CONN).drop_duplicates().dropna()\n",
    "tablecount_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-10-25T03:34:43.803087Z",
     "start_time": "2024-10-25T03:34:43.790086Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "                   DataTypes  RowCount\n0       Number of Persons 人數    641568\n1   Social Associations 社會關係    187878\n2  Biographical Addresses 地址    440059\n3         Alternate Names 別名    196850\n4     Kin Relationships 親屬關係    540631\n5       Entry into Office 入仕    253437\n6         Office Postings 任官   1976515\n7    Social Distinction 社會區分     60912\n8                   Texts 著作     57387",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>DataTypes</th>\n      <th>RowCount</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Number of Persons 人數</td>\n      <td>641568</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Social Associations 社會關係</td>\n      <td>187878</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Biographical Addresses 地址</td>\n      <td>440059</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Alternate Names 別名</td>\n      <td>196850</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Kin Relationships 親屬關係</td>\n      <td>540631</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Entry into Office 入仕</td>\n      <td>253437</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Office Postings 任官</td>\n      <td>1976515</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>Social Distinction 社會區分</td>\n      <td>60912</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>Texts 著作</td>\n      <td>57387</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datatypes = [\"Number of Persons 人數\",\"Social Associations 社會關係\", \"Biographical Addresses 地址\", \"Alternate Names 別名\", \"Kin Relationships 親屬關係\", \"Entry into Office 入仕\", \"Office Postings 任官\", \"Social Distinction 社會區分\", \"Texts 著作\"]\n",
    "tablecount_df['TableName'] = tablecount_df['TableName'].replace(dict(zip(tablecount_df['TableName'], datatypes)))  \n",
    "tablecount_df = tablecount_df.rename(columns={'TableName': \"DataTypes\"})\n",
    "tablecount_df "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-10-25T03:34:50.189739Z",
     "start_time": "2024-10-25T03:34:50.168736Z"
    }
   },
   "outputs": [],
   "source": [
    "tablecount_df.to_csv('data_types.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-10-25T03:34:52.481091Z",
     "start_time": "2024-10-25T03:34:52.469088Z"
    }
   },
   "outputs": [],
   "source": [
    "# Read the content of old JavaScript files\n",
    "with open('script.js', 'r') as file:  \n",
    "    js_code = file.read()  \n",
    "  \n",
    "# Delete old data  \n",
    "start_delimiter = 'const chart2Data = ['  \n",
    "end_delimiter = '];// Chart 2 Data'\n",
    "start_index = js_code.find(start_delimiter)  \n",
    "end_index = js_code.find(end_delimiter) + len(end_delimiter)  \n",
    "js_code = js_code[:start_index] + js_code[end_index:]\n",
    "# new data\n",
    "js_data = ',\\n'.join(  \n",
    "    f'{{ label: \"{dyname}\", value: {count} }}'  \n",
    "    for dyname,count in zip(tablecount_df[\"DataTypes\"],tablecount_df[\"RowCount\"])  \n",
    ")\n",
    "new_data = f'{start_delimiter}\\n{js_data}\\n{end_delimiter}'  \n",
    "  \n",
    "js_code = new_data + \"\\n\" +js_code  \n",
    "  \n",
    "# Write updated JavaScript file \n",
    "with open('script.js', 'w') as file:  \n",
    "    file.write(js_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-10-25T03:37:26.321452Z",
     "start_time": "2024-10-25T03:37:26.306472Z"
    }
   },
   "outputs": [],
   "source": [
    "# update the date\n",
    "import datetime  \n",
    "import re \n",
    "\n",
    "svg_path = os.path.join(os.getcwd(), 'images', 'svg4.svg')  \n",
    "with open(svg_path, 'r',encoding='utf-8') as file:  \n",
    "    svg_content = file.read()  \n",
    "  \n",
    "# get current date  \n",
    "current_time = datetime.datetime.now().strftime('%Y年%m月%d日') \n",
    "current_time_english = datetime.datetime.now().strftime('%B %d, %Y') \n",
    "  \n",
    "updated_svg_content = re.sub(r'更新日期：\\d+年\\d+月\\d+日', f'更新日期：{current_time}', svg_content)    \n",
    "updated_svg_content = re.sub(r'Updated on: \\w+ \\d+, \\d+', f'Updated on: {current_time_english}', updated_svg_content)\n",
    "with open(svg_path, 'w',encoding='utf-8') as file:  \n",
    "    file.write(updated_svg_content) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-10-25T03:37:30.196898Z",
     "start_time": "2024-10-25T03:37:30.190900Z"
    }
   },
   "outputs": [],
   "source": [
    "# close the db\n",
    "CONN.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.9.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}