setup_mongodb.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## config & startup\n",
    "\n",
    "1. 修改docker-compose.yaml中的用户名和密码，控制台输入 `docker-compose up -d` 运行后台mongodb.\n",
    "2. 登录`http://localhost:8081/`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from mongodb_helper import *\n",
    "\n",
    "# mongodb config\n",
    "mg_host = \"localhost\"\n",
    "mg_port = 27017\n",
    "mg_user = \"root\"\n",
    "mg_password = \"admin\"\n",
    "\n",
    "cn_stock = 'cn_stock'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## cn_stock/trade_cal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overwrite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.read_csv(\"datasources/trade_cal.csv\", parse_dates=['dt'], date_format='%Y%m%d', dtype={'is_open': np.uint8})\n",
    "df = df.rename(columns={'dt': '_id', 'is_open': 'status'}) # mongodb要求必须有_id\n",
    "# print(df.info())\n",
    "\n",
    "with mongo_connect(mg_host, mg_port, mg_user, mg_password) as mg:\n",
    "    db = mg[cn_stock]\n",
    "    db.drop_collection('trade_cal')\n",
    "    db['trade_cal'].insert_many(df.to_dict(orient='records'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>_id</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1990-12-19</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1990-12-20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1990-12-21</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1990-12-24</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1990-12-25</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8307</th>\n",
       "      <td>2024-12-25</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8308</th>\n",
       "      <td>2024-12-26</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8309</th>\n",
       "      <td>2024-12-27</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8310</th>\n",
       "      <td>2024-12-30</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8311</th>\n",
       "      <td>2024-12-31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8312 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            _id  status\n",
       "0    1990-12-19       1\n",
       "1    1990-12-20       1\n",
       "2    1990-12-21       1\n",
       "3    1990-12-24       1\n",
       "4    1990-12-25       1\n",
       "...         ...     ...\n",
       "8307 2024-12-25       1\n",
       "8308 2024-12-26       1\n",
       "8309 2024-12-27       1\n",
       "8310 2024-12-30       1\n",
       "8311 2024-12-31       1\n",
       "\n",
       "[8312 rows x 2 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import quantdata as qd\n",
    "\n",
    "with qd.mongo_connect(mg_host, mg_port, mg_user, mg_password) as mg:\n",
    "    db = mg['cn_stock']\n",
    "    c = qd.mongo_get_trade_cal(db)\n",
    "    df = pd.DataFrame(c)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## cn_stock/stocks_basic_info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overwrite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\"datasources/stocks_basic_info.csv\", parse_dates=['list_date', 'delist_date'], date_format='%Y%m%d', index_col=0)\n",
    "df = df.drop(columns=['symbol'])\n",
    "df = df.rename(columns={'ts_code':'symbol', 'list_status':'status'})\n",
    "df = df.fillna('')\n",
    "# print(df.info())\n",
    "\n",
    "with mongo_connect(mg_host, mg_port, mg_user, mg_password) as mg:\n",
    "    db = mg[cn_stock]\n",
    "    db.drop_collection('stocks_basic_info')\n",
    "    insert_many_ignore_nan(db['stocks_basic_info'], df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>_id</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>area</th>\n",
       "      <th>industry</th>\n",
       "      <th>fullname</th>\n",
       "      <th>enname</th>\n",
       "      <th>cnspell</th>\n",
       "      <th>market</th>\n",
       "      <th>exchange</th>\n",
       "      <th>curr_type</th>\n",
       "      <th>status</th>\n",
       "      <th>list_date</th>\n",
       "      <th>is_hs</th>\n",
       "      <th>delist_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>66d070b0c8defb98f9334d9c</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>深圳</td>\n",
       "      <td>银行</td>\n",
       "      <td>平安银行股份有限公司</td>\n",
       "      <td>Ping An Bank Co., Ltd.</td>\n",
       "      <td>payh</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>CNY</td>\n",
       "      <td>L</td>\n",
       "      <td>1991-04-03</td>\n",
       "      <td>S</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>66d070b0c8defb98f9334d9d</td>\n",
       "      <td>000002.SZ</td>\n",
       "      <td>万科A</td>\n",
       "      <td>深圳</td>\n",
       "      <td>全国地产</td>\n",
       "      <td>万科企业股份有限公司</td>\n",
       "      <td>China Vanke Co.,Ltd.</td>\n",
       "      <td>wka</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>CNY</td>\n",
       "      <td>L</td>\n",
       "      <td>1991-01-29</td>\n",
       "      <td>S</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>66d070b0c8defb98f9334d9e</td>\n",
       "      <td>000004.SZ</td>\n",
       "      <td>国华网安</td>\n",
       "      <td>深圳</td>\n",
       "      <td>软件服务</td>\n",
       "      <td>深圳国华网安科技股份有限公司</td>\n",
       "      <td>Shenzhen Guohua Network Security Technology Co...</td>\n",
       "      <td>ghwa</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>CNY</td>\n",
       "      <td>L</td>\n",
       "      <td>1991-01-14</td>\n",
       "      <td>N</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>66d070b0c8defb98f9334d9f</td>\n",
       "      <td>000006.SZ</td>\n",
       "      <td>深振业A</td>\n",
       "      <td>深圳</td>\n",
       "      <td>区域地产</td>\n",
       "      <td>深圳市振业(集团)股份有限公司</td>\n",
       "      <td>Shenzhen Zhenye(Group) Co., Ltd.</td>\n",
       "      <td>szya</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>CNY</td>\n",
       "      <td>L</td>\n",
       "      <td>1992-04-27</td>\n",
       "      <td>S</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>66d070b0c8defb98f9334da0</td>\n",
       "      <td>000007.SZ</td>\n",
       "      <td>全新好</td>\n",
       "      <td>深圳</td>\n",
       "      <td>其他商业</td>\n",
       "      <td>深圳市全新好股份有限公司</td>\n",
       "      <td>Shenzhen Quanxinhao Co,.Ltd.</td>\n",
       "      <td>qxh</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>CNY</td>\n",
       "      <td>L</td>\n",
       "      <td>1992-04-13</td>\n",
       "      <td>N</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5621</th>\n",
       "      <td>66d070b0c8defb98f9336391</td>\n",
       "      <td>688555.SH</td>\n",
       "      <td>退泽达(退)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>泽达易盛(天津)科技股份有限公司</td>\n",
       "      <td>Essence Information Technology Co., Ltd.</td>\n",
       "      <td>tszd</td>\n",
       "      <td>科创板</td>\n",
       "      <td>SSE</td>\n",
       "      <td>CNY</td>\n",
       "      <td>D</td>\n",
       "      <td>2020-06-23</td>\n",
       "      <td>N</td>\n",
       "      <td>2023-07-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5622</th>\n",
       "      <td>66d070b0c8defb98f9336392</td>\n",
       "      <td>832317.BJ</td>\n",
       "      <td>观典防务(退)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>观典防务技术股份有限公司</td>\n",
       "      <td>Guandian Defense Technology Co.,Ltd.</td>\n",
       "      <td>gdfw</td>\n",
       "      <td>北交所</td>\n",
       "      <td>BSE</td>\n",
       "      <td>CNY</td>\n",
       "      <td>D</td>\n",
       "      <td>2020-07-27</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-04-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5623</th>\n",
       "      <td>66d070b0c8defb98f9336393</td>\n",
       "      <td>833874.BJ</td>\n",
       "      <td>泰祥股份(退)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>十堰市泰祥实业股份有限公司</td>\n",
       "      <td>Shiyan Taixiang Industry Co., Ltd.</td>\n",
       "      <td>txgf</td>\n",
       "      <td>北交所</td>\n",
       "      <td>BSE</td>\n",
       "      <td>CNY</td>\n",
       "      <td>D</td>\n",
       "      <td>2020-07-27</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-07-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5624</th>\n",
       "      <td>66d070b0c8defb98f9336394</td>\n",
       "      <td>833994.BJ</td>\n",
       "      <td>翰博高新(退)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>翰博高新材料(合肥)股份有限公司</td>\n",
       "      <td>Highbroad Advanced Material (Hefei) Co., Ltd.</td>\n",
       "      <td>hbgx</td>\n",
       "      <td>北交所</td>\n",
       "      <td>BSE</td>\n",
       "      <td>CNY</td>\n",
       "      <td>D</td>\n",
       "      <td>2020-07-27</td>\n",
       "      <td>N</td>\n",
       "      <td>2022-07-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5625</th>\n",
       "      <td>66d070b0c8defb98f9336395</td>\n",
       "      <td>T00018.SH</td>\n",
       "      <td>上港集箱(退)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>上海港集装箱股份有限公司</td>\n",
       "      <td>Shanghai Port Container Co., Ltd.</td>\n",
       "      <td>sgjx</td>\n",
       "      <td></td>\n",
       "      <td>SSE</td>\n",
       "      <td>CNY</td>\n",
       "      <td>D</td>\n",
       "      <td>2000-07-19</td>\n",
       "      <td>N</td>\n",
       "      <td>2006-10-20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5626 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           _id     symbol     name area industry  \\\n",
       "0     66d070b0c8defb98f9334d9c  000001.SZ     平安银行   深圳       银行   \n",
       "1     66d070b0c8defb98f9334d9d  000002.SZ      万科A   深圳     全国地产   \n",
       "2     66d070b0c8defb98f9334d9e  000004.SZ     国华网安   深圳     软件服务   \n",
       "3     66d070b0c8defb98f9334d9f  000006.SZ     深振业A   深圳     区域地产   \n",
       "4     66d070b0c8defb98f9334da0  000007.SZ      全新好   深圳     其他商业   \n",
       "...                        ...        ...      ...  ...      ...   \n",
       "5621  66d070b0c8defb98f9336391  688555.SH   退泽达(退)                 \n",
       "5622  66d070b0c8defb98f9336392  832317.BJ  观典防务(退)                 \n",
       "5623  66d070b0c8defb98f9336393  833874.BJ  泰祥股份(退)                 \n",
       "5624  66d070b0c8defb98f9336394  833994.BJ  翰博高新(退)                 \n",
       "5625  66d070b0c8defb98f9336395  T00018.SH  上港集箱(退)                 \n",
       "\n",
       "              fullname                                             enname  \\\n",
       "0           平安银行股份有限公司                             Ping An Bank Co., Ltd.   \n",
       "1           万科企业股份有限公司                               China Vanke Co.,Ltd.   \n",
       "2       深圳国华网安科技股份有限公司  Shenzhen Guohua Network Security Technology Co...   \n",
       "3      深圳市振业(集团)股份有限公司                   Shenzhen Zhenye(Group) Co., Ltd.   \n",
       "4         深圳市全新好股份有限公司                       Shenzhen Quanxinhao Co,.Ltd.   \n",
       "...                ...                                                ...   \n",
       "5621  泽达易盛(天津)科技股份有限公司           Essence Information Technology Co., Ltd.   \n",
       "5622      观典防务技术股份有限公司               Guandian Defense Technology Co.,Ltd.   \n",
       "5623     十堰市泰祥实业股份有限公司                 Shiyan Taixiang Industry Co., Ltd.   \n",
       "5624  翰博高新材料(合肥)股份有限公司      Highbroad Advanced Material (Hefei) Co., Ltd.   \n",
       "5625      上海港集装箱股份有限公司                  Shanghai Port Container Co., Ltd.   \n",
       "\n",
       "     cnspell market exchange curr_type status  list_date is_hs delist_date  \n",
       "0       payh     主板     SZSE       CNY      L 1991-04-03     S         NaT  \n",
       "1        wka     主板     SZSE       CNY      L 1991-01-29     S         NaT  \n",
       "2       ghwa     主板     SZSE       CNY      L 1991-01-14     N         NaT  \n",
       "3       szya     主板     SZSE       CNY      L 1992-04-27     S         NaT  \n",
       "4        qxh     主板     SZSE       CNY      L 1992-04-13     N         NaT  \n",
       "...      ...    ...      ...       ...    ...        ...   ...         ...  \n",
       "5621    tszd    科创板      SSE       CNY      D 2020-06-23     N  2023-07-07  \n",
       "5622    gdfw    北交所      BSE       CNY      D 2020-07-27     N  2022-04-26  \n",
       "5623    txgf    北交所      BSE       CNY      D 2020-07-27     N  2022-07-18  \n",
       "5624    hbgx    北交所      BSE       CNY      D 2020-07-27     N  2022-07-25  \n",
       "5625    sgjx             SSE       CNY      D 2000-07-19     N  2006-10-20  \n",
       "\n",
       "[5626 rows x 15 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import quantdata as qd\n",
    "\n",
    "with qd.mongo_connect(mg_host, mg_port, mg_user, mg_password) as mg:\n",
    "    db = mg['cn_stock']\n",
    "    c = qd.mongo_get_data(db, 'stocks_basic_info')\n",
    "    df = pd.DataFrame(c)\n",
    "df"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}