– 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 (1) ~
link：Building your own database through TEJ API
Table of Contents
First we have to install the packages on Python. (tejapi, datetime, sqlite3)
pip install tejapi
pip install datetime
pip install sqlite3
The next step is to import these packages and set up your own api_key. If you don’t have one, you can apply for a trial on the TEJ API official website. But the trial version has limits on the range of data that could be obtained
import tejapi
import datetime
import sqlite3
The database which will be used in this chapter is contained in 小資方案. If you want to use data without limit, check it on TEJ E-shop
import tejapi
tejapi.ApiConfig.api_key = “your api_key”
Now we can get the data through these codes👀
TSMC = tejapi.get(
'TWN/EWPRCD', # database
coid = '2330', # Stock/Company symbol
mdate={'gte':'2020-01-01', 'lte':'2020-12-31'}, # date
paginate=True, # data can be obtained in stages through this parameter
opts={'columns': ['mdate','open_d','high_d','low_d','close_d']},
# columns select
)TSMC
Saving data to Database:
# 1
TSMC = tejapi.get(
'TWN/EWPRCD',
coid = '2330',
mdate={'gt':'2020-01-01'},
paginate=True,
opts={'columns': ['mdate','open_d','high_d','low_d','close_d']},
)
# 2
conn = sqlite3.connect('example.db')
# 3
TSMC.to_sql(name='TSMC', con=conn, if_exists="replace", index=False)
# 4
conn.close()
Remember to check the database after running the code
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.
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. Surely, we will write more articles related to data analysis and applications through Medium in the future!
Finally, if you like this topic, please click 👏 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.
