{"id":14316,"date":"2021-03-15T00:44:46","date_gmt":"2021-03-14T16:44:46","guid":{"rendered":"https:\/\/www.tejwin.com\/?post_type=insight&#038;p=14316"},"modified":"2023-08-16T11:45:23","modified_gmt":"2023-08-16T03:45:23","slug":"%e3%80%90introduction2%e3%80%91building-your-own-database-through-tej-api","status":"publish","type":"insight","link":"https:\/\/www.tejwin.com\/en\/insight\/%e3%80%90introduction2%e3%80%91building-your-own-database-through-tej-api\/","title":{"rendered":"Building your own database through TEJ\u00a0API(II)"},"content":{"rendered":"\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.tejwin.com\/wp-content\/uploads\/17252wDjgTBO5xNCWf9CTQg.jpg\" alt=\"\"\/><\/figure>\n\n\n\n<p>&#8211; update your database automatically<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>Continuing with the TEJ API data collecting\/reading\/saving instructions which we have learned in the previous episode, <strong>this episode focuses on the way to update your database automatically.<\/strong> It is consistent with the previous episode, so friends who have not read the previous episode, please go back and read the introduction (1) ~<\/p>\n<\/blockquote>\n\n\n\n<p><strong>link\uff1a<a href=\"https:\/\/www.tejwin.com\/en\/insight\/building-your-own-database-through-tej-api\/\" class=\"ek-link\">Building your own database through TEJ API<\/a><\/strong><\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_81 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-69f10dd40b540\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"ez-toc-cssicon\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-69f10dd40b540\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.tejwin.com\/en\/insight\/%e3%80%90introduction2%e3%80%91building-your-own-database-through-tej-api\/#Highlights_of_this_article\" >Highlights of this article<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.tejwin.com\/en\/insight\/%e3%80%90introduction2%e3%80%91building-your-own-database-through-tej-api\/#Links_related_to_this_article\" >Links related to this article<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.tejwin.com\/en\/insight\/%e3%80%90introduction2%e3%80%91building-your-own-database-through-tej-api\/#TEJ_API_Data_Collecting%EF%BC%8DUsing_stock_data_as_an_example\" >TEJ API Data Collecting\uff0dUsing stock data as an&nbsp;example<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.tejwin.com\/en\/insight\/%e3%80%90introduction2%e3%80%91building-your-own-database-through-tej-api\/#How_to_Update_the_Database\" >How to Update the&nbsp;Database<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.tejwin.com\/en\/insight\/%e3%80%90introduction2%e3%80%91building-your-own-database-through-tej-api\/#Windows_Task_Scheduler\" >Windows Task Scheduler<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.tejwin.com\/en\/insight\/%e3%80%90introduction2%e3%80%91building-your-own-database-through-tej-api\/#Conclusion\" >Conclusion<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.tejwin.com\/en\/insight\/%e3%80%90introduction2%e3%80%91building-your-own-database-through-tej-api\/#Links_related_to_this_article_again\" >Links related to this article&nbsp;again!<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Highlights_of_this_article\"><\/span><strong>Highlights of this article <\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>TEJ API Data Collecting<\/strong><\/li>\n\n\n\n<li><strong>How to Update the Database<\/strong><\/li>\n\n\n\n<li><strong>Windows Task Scheduler<\/strong><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Links_related_to_this_article\"><\/span><strong>Links related to this article<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>API Official Website: <\/strong><a href=\"https:\/\/api.tej.com.tw\/\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>TEJ API Official Website<\/strong><\/a><\/li>\n\n\n\n<li><strong>The Product Package: <\/strong><a href=\"https:\/\/eshop.tej.com.tw\/E-Shop\/Edata_caseIntro\/1\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>TEJ E SHOP<\/strong><\/a><\/li>\n\n\n\n<li><strong>Source Code: <\/strong><a href=\"https:\/\/github.com\/tejtw\/TEJAPI_Python_Medium_Rookies\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>TEJ GITHUB<\/strong><\/a><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"TEJ_API_Data_Collecting%EF%BC%8DUsing_stock_data_as_an_example\"><\/span>TEJ API Data Collecting\uff0dUsing stock data as an&nbsp;example<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>First we have to install the packages on Python. (tejapi, datetime, sqlite3)<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">pip install tejapi<br>pip install datetime<br>pip install sqlite3<\/pre>\n\n\n\n<p>The next step is to import these packages and set up <strong>your own api_key<\/strong>. If you don\u2019t have one, you can <a href=\"https:\/\/api.tej.com.tw\/trial.html\" target=\"_blank\" rel=\"noreferrer noopener\" class=\"ek-link\"><strong>apply for a trial <\/strong><\/a> on the TEJ API official website. But the trial version <strong>has limits on the range of data that could be obtained<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import tejapi<br>import datetime<br>import sqlite3<\/pre>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>The database which will be used in this chapter is contained in \u5c0f\u8cc7\u65b9\u6848. If you want to use data without limit, check it on<a href=\"https:\/\/eshop.tej.com.tw\/E-Shop\/Edata_intro\" target=\"_blank\" rel=\"noreferrer noopener\"> TEJ E-shop<\/a><\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-preformatted\">import tejapi<br>tejapi.ApiConfig.api_key = \u201cyour api_key\u201d<\/pre>\n\n\n\n<p>Now we can get the data through these codes\ud83d\udc40<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">TSMC = tejapi.get(<br>'TWN\/EWPRCD',  # database<br>coid = '2330',  # Stock\/Company symbol<br>mdate={'gte':'2020-01-01', 'lte':'2020-12-31'},  # date<br>paginate=True,  # data can be obtained in stages through this parameter<br>opts={'columns': ['mdate','open_d','high_d','low_d','close_d']},<br># columns select<br>)TSMC<\/pre>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><img decoding=\"async\" src=\"https:\/\/www.tejwin.com\/wp-content\/uploads\/0SLUg1CsrFAOCwx1M.png\" alt=\"\"\/><\/figure>\n\n\n\n<p>Saving data to Database:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Grab data through tejapi<\/li>\n\n\n\n<li>Connect to the database (example.db)<\/li>\n\n\n\n<li>Save data into the database (example.db)<\/li>\n\n\n\n<li>Close connection<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-preformatted\"># 1<br>TSMC = tejapi.get(<br>'TWN\/EWPRCD',  <br>coid = '2330',  <br>mdate={'gt':'2020-01-01'},  <br>paginate=True,  <br>opts={'columns': ['mdate','open_d','high_d','low_d','close_d']},<br>)<br># 2<br>conn = sqlite3.connect('example.db')<br># 3<br>TSMC.to_sql(name='TSMC', con=conn, if_exists=\"replace\", index=False)<br># 4<br>conn.close()<\/pre>\n\n\n\n<p><strong>Remember to check the database after running the code <\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><img decoding=\"async\" src=\"https:\/\/www.tejwin.com\/wp-content\/uploads\/0eL-udRX9A4p5jj_n.png\" alt=\"\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><img decoding=\"async\" src=\"https:\/\/www.tejwin.com\/wp-content\/uploads\/02pFLlQWsyepYUfAi.png\" alt=\"\"\/><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_to_Update_the_Database\"><\/span><strong>How to Update the&nbsp;Database<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Next, we can add for-loop to the steps above, change stock\/company symbols from individual to multi-stocks, and each execution will save data to the database.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Collect data from TWN\/EWNPRCSTD.<\/li>\n\n\n\n<li>Select stocks\/company symbols of all listed companies.<\/li>\n\n\n\n<li>Using the append method to store data into the database.<\/li>\n\n\n\n<li>Execute for-loop.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Windows_Task_Scheduler\"><\/span>Windows Task Scheduler<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Search <strong>task scheduler<\/strong> on the bottom-left in your windows system.<\/li>\n\n\n\n<li>After entering into the task scheduler, click <strong>create a task <\/strong>on your right, then <strong>name the task<\/strong>(ef. update database).<\/li>\n\n\n\n<li>Switch to <strong>task trigger, create a new one<\/strong>, turn the <strong>trigger to daily in <\/strong>first in the \u201c<strong>trigger <\/strong>\u201c field, and then <strong>set the start time at 3:30 pm.<\/strong> (Be careful! Today\u2019s data will be updated after 3 pm, so we should set the start time after 3 pm.).<\/li>\n\n\n\n<li>Switch to the \u201c<strong>Action<\/strong>\u201d field and <strong>create a new one<\/strong>, then choose to <strong>start a program<\/strong>\ud83d\udc77\uff1a<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First blank \u27a1\ufe0f <strong>python location(path)<\/strong><\/li>\n\n\n\n<li>Second blank \u27a1\ufe0f<strong> file\u2019s name (.py)<\/strong><\/li>\n\n\n\n<li>Third blank \u27a1\ufe0f<strong> file\u2019s location(path)<\/strong><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><img decoding=\"async\" src=\"https:\/\/www.tejwin.com\/wp-content\/uploads\/0O-8MxDRmlMtQTCS.png\" alt=\"\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><img decoding=\"async\" src=\"https:\/\/www.tejwin.com\/wp-content\/uploads\/0dP0WR9k-rgMpltAw.png\" alt=\"\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><img decoding=\"async\" src=\"https:\/\/www.tejwin.com\/wp-content\/uploads\/0TizzDSU7WhvoWg6Y.png\" alt=\"\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><img decoding=\"async\" src=\"https:\/\/www.tejwin.com\/wp-content\/uploads\/06C-INynBLoP-8Q24.png\" alt=\"\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>What we share with you this time is the introduction to get the data through TEJ API and combines with windows task scheduler to complete the goal of automatic database updating. <strong>Surely, we will write more articles related to data analysis and applications through Medium in the future!<\/strong><br>Finally, if you like this topic, please click \ud83d\udc4f below, giving us more support and encouragement. Additionally, if you have any questions or suggestions, please leave a message or email us, we will try our best to reply to you.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Links_related_to_this_article_again\"><\/span>Links related to this article&nbsp;again!<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>API Official Website: <\/strong><a href=\"https:\/\/api.tej.com.tw\/\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>TEJ API Official Website<\/strong><\/a><\/li>\n\n\n\n<li><strong>The Product Package: <\/strong><a href=\"https:\/\/eshop.tej.com.tw\/E-Shop\/Edata_caseIntro\/1\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>TEJ E SHOP<\/strong><\/a><\/li>\n\n\n\n<li><strong>Source Code: <\/strong><a href=\"https:\/\/github.com\/tejtw\/TEJAPI_Python_Medium_Rookies\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>TEJ GITHUB<\/strong><\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>&#8211; update your database automatically Continuing with the TEJ API data collecting\/reading\/saving instructions which we have learned in the previous episode, this episode focuses on the way to update your database automatically. It is consistent with the previous episode, so friends who have not read the previous episode, please go back and read the introduction [&hellip;]<\/p>\n","protected":false},"featured_media":14317,"template":"","tags":[2904,2572,2575,2371,2640,3006],"insight-category":[690,50],"class_list":["post-14316","insight","type-insight","status-publish","has-post-thumbnail","hentry","tag-beginners-guide","tag-data-analysis","tag-database","tag-python","tag-tej-api","tag-tejapi-beginners-guide","insight-category-data-analysis","insight-category-fintech"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/insight\/14316","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/insight"}],"about":[{"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/types\/insight"}],"version-history":[{"count":0,"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/insight\/14316\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/media\/14317"}],"wp:attachment":[{"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/media?parent=14316"}],"wp:term":[{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/tags?post=14316"},{"taxonomy":"insight-category","embeddable":true,"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/insight-category?post=14316"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}