{"id":501,"date":"2017-05-01T10:36:09","date_gmt":"2017-05-01T01:36:09","guid":{"rendered":"http:\/\/www.typea.info\/blog\/index.php\/2017\/05\/01\/3webmysqlpythonvisual_studio_code\/"},"modified":"2017-05-01T10:36:09","modified_gmt":"2017-05-01T01:36:09","slug":"3webmysqlpythonvisual_studio_code","status":"publish","type":"post","link":"https:\/\/www.typea.info\/blog\/index.php\/2017\/05\/01\/3webmysqlpythonvisual_studio_code\/","title":{"rendered":"\u30ca\u30f3\u30d0\u30fc\u30ba3\u306eWeb\u30da\u30fc\u30b8\u3092\u89e3\u6790\u3057\u7d50\u679c\u3092MySQL\u306b\u767b\u9332\u3057\u3066\u51fa\u73fe\u983b\u5ea6\u306e\u30d2\u30b9\u30c8\u30b0\u30e9\u30e0\u8868\u793a\u3059\u308bPython\u30b5\u30f3\u30d7\u30eb\u30d7\u30ed\u30b0\u30e9\u30e0\u3092Visual Studio Code\u3067\u4f5c\u6210\u3057\u3066\u5b9f\u884c\u3059\u308b"},"content":{"rendered":"

<\/a>\"\" <\/p>\n

1.\u6982\u8981<\/h2>\n

Web\u30da\u30fc\u30b8\u304b\u3089\u30c7\u30fc\u30bf\u3092\u53d6\u3063\u3066\u304d\u3066\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u683c\u7d0d\u3057\u3001\u89e3\u6790\u3059\u308b\u307e\u3067\u306e\u5927\u307e\u304b\u306a\u6d41\u308c\u3092\u8a66\u3059\u3002<\/p>\n

\u5bfe\u8c61\u306e\u30c7\u30fc\u30bf\u306b\u306f\u3001\u69cb\u9020\u3082\u7c21\u5358\u3067\u30c7\u30fc\u30bf\u4ef6\u6570\u3082\u307b\u3069\u307b\u3069(\u6570\u5343\u4ef6)\u3042\u308b\u30ca\u30f3\u30d0\u30fc\u30ba3\u306e\u7d50\u679c\u3092\u4f7f\u7528\u3057\u3066\u307f\u308b\u3002<\/p>\n

http:\/\/www.takarakujinet.co.jp\/numbers3\/index2.html<\/a><\/p>\n

\"numbers3_02\"<\/a><\/p>\n

2.\u74b0\u5883<\/h2>\n

2.1 Python venv \u74b0\u5883<\/h3>\n

Visual Studio Code \u3067 Python \u3092\u4f7f\u3048\u308b\u3088\u3046\u306b\u3057\u3066\u3001NumPy\u3001SciPy\u3001matplotlib\u306f\u3001\u3053\u306e\u9593\u8a2d\u5b9a\u3057\u305f\u306e\u3067<\/a>\u3001venv\u306e\u74b0\u5883\u3092\u6709\u52b9\u5316\u3057\u3001Web\u30da\u30fc\u30b8\u89e3\u6790\u7528\u3001MySQL\u767b\u9332\u7528\u306b\u4ee5\u4e0b\u306e\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb<\/p>\n

\u4eee\u60f3\u74b0\u5883\u3092\u30a2\u30af\u30c6\u30a3\u30d6\u5316<\/p>\n

PS C:\\workspaces\\vscode> cd ..\r\nPS C:\\workspaces> cd .\\venv\\datascience\\Scripts\\\r\nPS C:\\workspaces\\venv\\datascience\\Scripts> .\\activate\r\n(datascience) PS C:\\workspaces\\venv\\datascience\\Scripts><\/pre>\n
\u95a2\u9023\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb<\/p>\n
(datascience) PS C:\\workspaces\\venv\\datascience\\Scripts> pip install beautifulsoup4\r\n(datascience) PS C:\\workspaces\\venv\\datascience\\Scripts> pip install lxml\r\n(datascience) PS C:\\workspaces\\venv\\datascience\\Scripts> pip install pymysql\r\n<\/pre>\n
2.2 MySQL\u306e\u8a2d\u5b9a\u3068\u30c6\u30fc\u30d6\u30eb\u306e\u4f5c\u6210<\/h3>\n
MySQL\u3092\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb\u3057\u3066<\/a>\u3001\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210<\/p>\n
CREATE TABLE `numbers3` (\r\n  `kaigou` int(11) NOT NULL,\r\n  `chusen_date` date DEFAULT NULL,\r\n  `chusen_num` varchar(3) DEFAULT NULL,\r\n  `straight_kuchi` int(11) DEFAULT NULL,\r\n  `straight_yen` int(11) DEFAULT NULL,\r\n  `box_kuchi` int(11) DEFAULT NULL,\r\n  `box_yen` int(11) DEFAULT NULL,\r\n  `set_straight_kuchi` int(11) DEFAULT NULL,\r\n  `set_straight_yen` int(11) DEFAULT NULL,\r\n  `set_box_kuchi` int(11) DEFAULT NULL,\r\n  `set_box_yen` int(11) DEFAULT NULL,\r\n  `min_kuchi` int(11) DEFAULT NULL,\r\n  `min_yen` int(11) DEFAULT NULL,\r\n  PRIMARY KEY (`kaigou`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\r\n<\/pre>\n
3.\u30b5\u30f3\u30d7\u30eb\u30bd\u30fc\u30b9<\/h2>\n
3.1 Web\u30da\u30fc\u30b8\u3092\u89e3\u6790\u3057\u3066\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u767b\u9332<\/h3>\n
# -*- coding: utf-8 -*-\r\n'''\r\nnumbers3 \u306e\u7d50\u679c\u3092 Database \u3078\u767b\u9332\u3059\u308b\r\n'''\r\nfrom urllib.request import urlopen\r\nfrom urllib.parse import urlencode\r\nfrom bs4 import BeautifulSoup\r\nimport re\r\nimport math \r\nimport time\r\nimport pymysql\r\nfrom settings import DB_HOST, DB_USER, DB_PASSWORD, DB_SCHEME\r\n\r\nDEFAULT_HOWMANY = 100\r\n\r\ndef save_numbers3_history(from_k, to_k):\r\n    '''\r\n    \u6307\u5b9a\u7bc4\u56f2\u306e\u30ca\u30f3\u30d0\u30fc\u30ba3\u306e\u7d50\u679c\u3092\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u53d6\u8fbc\u3080\r\n    from_k - \u958b\u59cb\u56de\r\n    to_k - \u7d42\u4e86\u56de\r\n    '''\r\n    rec_map = get_numbers3_history(from_k, to_k)\r\n\r\n    try:\r\n        con = pymysql.connect(\r\n            host=DB_HOST,\r\n            user=DB_USER,\r\n            password=DB_PASSWORD,\r\n            db=DB_SCHEME,\r\n            charset='utf8mb4',\r\n            cursorclass=pymysql.cursors.DictCursor)\r\n\r\n        with con.cursor() as cur:\r\n            for rec in rec_map.values():\r\n                sql = \"\"\"\r\n                    INSERT INTO `app`.`numbers3`\r\n                    (`kaigou`,\r\n                    `chusen_date`,\r\n                    `chusen_num`,\r\n                    `straight_kuchi`,\r\n                    `straight_yen`,\r\n                    `box_kuchi`,\r\n                    `box_yen`,\r\n                    `set_straight_kuchi`,\r\n                    `set_straight_yen`,\r\n                    `set_box_kuchi`,\r\n                    `set_box_yen`,\r\n                    `min_kuchi`,\r\n                    `min_yen`)\r\n                    VALUES(\r\n                    {0[kaigou]},\r\n                    {0[chusen_date]},\r\n                    {0[chusen_num]},\r\n                    {0[straight_kuchi]},\r\n                    {0[straight_yen]},\r\n                    {0[box_kuchi]},\r\n                    {0[box_yen]},\r\n                    {0[set_straight_kuchi]},\r\n                    {0[set_straight_yen]},\r\n                    {0[set_box_kuchi]},\r\n                    {0[set_box_yen]},\r\n                    {0[min_kuchi]},\r\n                    {0[min_yen]});\r\n                    \"\"\".format(rec)\r\n                print(sql)\r\n                cur.execute(sql)\r\n            con.commit()\r\n    except Exception as exception:\r\n        print(exception)\r\n    finally:\r\n        con.close()\r\n    print('finish work')\r\n\r\ndef get_numbers3_history(from_k, to_k):\r\n    '''\r\n    \u6307\u5b9a\u7bc4\u56f2\u306e\u30ca\u30f3\u30d0\u30fc\u30ba3\u306e\u7d50\u679c\u3092\u53d6\u5f97\u3059\u308b\r\n    from_k - \u958b\u59cb\u56de\r\n    to_k - \u7d42\u4e86\u56de\r\n    '''\r\n    result = {}\r\n\r\n    start_kai = int(from_k)\r\n    end_kai = int(to_k)\r\n    calc_diff = lambda end_kai, start_kai: end_kai - start_kai + 1\r\n    diff_kai = calc_diff(end_kai, start_kai)\r\n    howmany = DEFAULT_HOWMANY if DEFAULT_HOWMANY < diff_kai else diff_kai\r\n\r\n    while end_kai >= start_kai:\r\n        result.update(\r\n            get_numbers3(\r\n                end_kai,\r\n                howmany if calc_diff(end_kai, start_kai) > howmany else calc_diff(end_kai, start_kai)))\r\n\r\n        end_kai = end_kai - howmany\r\n\r\n    return result\r\n\r\ndef get_numbers3(kaigou, howmany):\r\n    '''\r\n    \u6307\u5b9a\u7bc4\u56f2\u306e\u30ca\u30f3\u30d0\u30fc\u30ba3\u306e\u7d50\u679c\u3092WEB\u304b\u3089\u53d6\u5f97\u3059\u308b\r\n    kaigou - \u6307\u5b9a\u56de\r\n    howmany - \u6307\u5b9a\u56de\u3092\u542b\u307f\u3001\u4f55\u56de\u904e\u53bb\u5206\u3092\u53d6\u5f97\u3059\u308b\u304b\r\n    '''\r\n\r\n    time.sleep(2)\r\n\r\n    result = {}\r\n\r\n    post_data = {\r\n        'searchway':'kaigou', # kaigou or day\r\n        'year':'',\r\n        'month':'',\r\n        'day':'',\r\n        'kaigou':kaigou,\r\n        'howmany':howmany\r\n        }\r\n\r\n    # HTTP \u30ea\u30af\u30a8\u30b9\u30c8\u306f\u3001data \u30d1\u30e9\u30e1\u30fc\u30bf\u30fc\u304c\u6307\u5b9a\u3055\u308c\u305f\u5834\u5408 POST \u306b\u3001\u6307\u5b9a\u3055\u308c\u306a\u3044\u5834\u5408\u306b GET \u306b\u306a\u308a\u307e\u3059\u3002\r\n    url = 'http:\/\/www.takarakujinet.co.jp\/ajax\/numbers3\/pastResultPage.do'\r\n    html = urlopen(url, urlencode(post_data).encode('utf-8'))\r\n    print(\"url:{0}, data:{1}\", url, post_data)\r\n    soup = BeautifulSoup(html, \"lxml\")\r\n    #print(soup)\r\n    tr_tag_list = soup.find_all(\"tr\")\r\n\r\n    header_keys = [\r\n        'kaigou',\r\n        'chusen_date',\r\n        'chusen_num',\r\n        'straight_kuchi',\r\n        'box_kuchi',\r\n        'set_straight_kuchi',\r\n        'set_box_kuchi',\r\n        'min_kuchi']\r\n\r\n    yen_keys = ['straight_yen', 'box_yen', 'set_straight_yen', 'set_box_yen', 'min_yen']\r\n\r\n    rec = None\r\n    for tr_tag in tr_tag_list:\r\n        td_tag_list = tr_tag.find_all(\"td\")\r\n\r\n        is_header_tr = tr_tag.find('td', {'rowspan':'2'})\r\n        if is_header_tr:\r\n            rec = {}\r\n            td_pos = 0\r\n            for td_tag in td_tag_list:\r\n                val = td_tag.get_text()\r\n                if td_pos == 0:\r\n                    result[val] = rec\r\n                if td_pos == 1:\r\n                    val = re.sub(r'\\r\\n[ ]*$', r'', val)\r\n                    val = re.sub(r'\\r\\n[ ]*', r'\/', val)\r\n                    val = re.sub(r'\/', r'-', val)\r\n                    val = \"'{0}'\".format(val)\r\n                if td_pos == 2:\r\n                    val = \"'{0}'\".format(val)\r\n                if td_pos >= 3:\r\n                    if re.match(r'.*\u8a72\u5f53\u306a\u3057.*', val):\r\n                        val = '0'\r\n                    else:\r\n                        val = re.sub(r'[^0-9]', '', val)\r\n                        if val == '':\r\n                            val = 'null'\r\n                rec[header_keys[td_pos]] = val\r\n\r\n                td_pos = td_pos + 1\r\n        else:\r\n            td_pos = 0\r\n            for td_tag in td_tag_list:\r\n                val = td_tag.get_text()\r\n                if re.match(r'.*\u8a72\u5f53\u306a\u3057.*', val):\r\n                    val = '0'\r\n                else:\r\n                    val = re.sub(r'[^0-9]', '', val)\r\n                assert rec != None, \"rec must not be None\"\r\n                rec[yen_keys[td_pos]] = val\r\n                td_pos = td_pos + 1\r\n\r\n    return result\r\n\r\nif __name__ == '__main__':\r\n    save_numbers3_history('1', '11')\r\n<\/pre>\n
\u767b\u9332\u3067\u304d\u305f\uff01<\/p>\n
\"numbers3\"<\/a><\/p>\n
3.2 \u30d2\u30b9\u30c8\u30b0\u30e9\u30e0\u3092\u8868\u793a<\/h3>\n
# -*- coding: utf-8 -*-\r\n'''\r\n\u30ca\u30f3\u30d0\u30fc\u30ba3\u306e\u30d2\u30b9\u30c8\u30b0\u30e9\u30e0\u3092\u4f5c\u6210\r\n'''\r\nimport matplotlib.pyplot as plt\r\nimport numpy as np\r\n\r\nimport pymysql\r\nfrom settings import DB_HOST,DB_USER,DB_PASSWORD,DB_SCHEME\r\n\r\ndef get_conn():\r\n    '''\r\n    MySQL \u30b3\u30cd\u30af\u30b7\u30e7\u30f3\u3092\u53d6\u5f97\r\n    '''\r\n    conn = pymysql.connect(\r\n        host=DB_HOST,\r\n        user=DB_USER,\r\n        password=DB_PASSWORD,\r\n        db=DB_SCHEME,\r\n        charset='utf8mb4',\r\n        cursorclass=pymysql.cursors.DictCursor)\r\n\r\n    return conn\r\n\r\ndef fetch_all(sql):\r\n    '''\r\n    SELECT SQL\u3092\u5b9f\u884c\u3057\u7d50\u679c\u3092\u5168\u4ef6\u8fd4\u3059\r\n    '''\r\n    conn = get_conn()\r\n    result = {}\r\n    try:\r\n        with conn.cursor() as cur:\r\n            cur.execute(sql)\r\n            result = cur.fetchall()\r\n    finally:\r\n        conn.close()\r\n    return result\r\n\r\ndef hist_chusen_num(hist_type, pos=None):\r\n    '''\r\n    \u62bd\u9078\u7d50\u679c\u304b\u3089\u30d2\u30b9\u30c8\u30b0\u30e9\u30e0\u3092\u751f\u6210\u3059\u308b\r\n    '''\r\n    #http:\/\/matplotlib.org\/api\/pyplot_api.html#matplotlib.pyplot.bar\r\n    recs = fetch_all('SELECT chusen_num FROM numbers3')\r\n    hist = {}\r\n\r\n    if hist_type == 'all':\r\n        # 3\u6841\u306e\u6574\u6570\u3068\u3057\u3066\u306e\u51fa\u73fe\u983b\u5ea6\r\n        data_list = [int(rec['chusen_num']) for rec in recs]\r\n        bins = np.arange(0, 1000)\r\n        hist = np.histogram(data_list, bins)\r\n        plt.bar(bins[:-1], hist[0])\r\n\r\n    if hist_type == 'num':\r\n        # 1\u6841\u305a\u3064\u306e\u51fa\u73fe\u983b\u5ea6\r\n        nested = [list(rec['chusen_num']) for rec in recs]\r\n        data_list = [int(x) for inner in nested for x in inner]\r\n        bins = np.arange(0, 11)\r\n        hist = np.histogram(data_list, bins)\r\n        plt.bar(bins[:-1], hist[0])\r\n\r\n    if hist_type == 'pos':\r\n        # \u51fa\u73fe\u4f4d\u7f6e\u3092\u6307\u5b9a\u3057\u305f\u983b\u5ea6\r\n        data_list = [int(list(rec['chusen_num'])[pos]) for rec in recs]\r\n        bins = np.arange(0, 11)\r\n        hist = np.histogram(data_list, bins)\r\n        plt.bar(bins[:-1], hist[0])\r\n\r\n    plt.show()\r\n    \r\nif __name__ == '__main__':\r\n    hist_chusen_num('all')\r\n    #hist_chusen_num('num')\r\n    #hist_chusen_num('pos',pos=1)<\/pre>\n
\u51fa\u73fe\u5024\u306e\u5206\u5e03\u30d2\u30b9\u30c8\u30b0\u30e9\u30e0\u3092\u8868\u793a\u3067\u304d\u305f\uff01<\/p>\n
\"numbers3_03\"<\/a><\/p>\n
\u4ee5\u4e0a\u3002<\/p>\n
<\/a>\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"
1.\u6982\u8981 Web\u30da\u30fc\u30b8\u304b\u3089\u30c7\u30fc\u30bf\u3092\u53d6\u3063\u3066\u304d\u3066\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306b\u683c\u7d0d\u3057\u3001\u89e3\u6790\u3059\u308b\u307e\u3067\u306e\u5927\u307e\u304b\u306a\u6d41\u308c\u3092\u8a66\u3059\u3002 \u5bfe\u8c61\u306e\u30c7\u30fc\u30bf\u306b\u306f\u3001\u69cb\u9020\u3082\u7c21\u5358\u3067\u30c7\u30fc\u30bf\u4ef6\u6570\u3082\u307b\u3069\u307b\u3069(\u6570\u5343\u4ef6)\u3042\u308b\u30ca\u30f3\u30d0\u30fc\u30ba3\u306e\u7d50\u679c\u3092\u4f7f\u7528\u3057\u3066\u307f\u308b\u3002 http:\/\/w […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"sns_share_botton_hide":"","vkExUnit_sns_title":"","_vk_print_noindex":"","sitemap_hide":"","_veu_custom_css":"","veu_display_promotion_alert":"","vkexunit_cta_each_option":"","footnotes":""},"categories":[59,20,12,35,86],"tags":[],"class_list":["post-501","post","type-post","status-publish","format-standard","hentry","category-mysql","category-python","category-visualstudio","category-math","category-statistics"],"veu_head_title_object":{"title":"","add_site_title":""},"_links":{"self":[{"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/posts\/501","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=501"}],"version-history":[{"count":0,"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/posts\/501\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=501"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=501"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=501"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}