Streamlit, on the other hand, is an open-source Python framework that simplifies the process of building web applications. It provides an intuitive API that enables developers to create powerful data applications with minimal effort. <\/p>\n\n\n\n
In this post, we’ll show you how to use TiDB Cloud and Streamlit to build an interactive Stock Data web application. We’ll walk you through the code to explain some of the building blocks of the application. You\u2019ll learn how to connect to TiDB Cloud, retrieve data from the Yahoo Finance API, and use Plotly to visualize the data. By the end of this tutorial, you’ll have a better understanding of how to leverage these powerful tools to build similar applications that can streamline your data analysis and visualization workflows.<\/p>\n\n\n\n
<\/span>Prerequisites<\/strong><\/span><\/h2>\n\n\n\nBefore you begin, you will need to set up the following:<\/p>\n\n\n\n
\n- A TiDB Cloud account<\/a> and a free Serverless Tier cluster on TiDB Cloud. For details, see TiDB Cloud Quick Start<\/a>.<\/li>\n\n\n\n
- Python 3<\/a><\/li>\n\n\n\n
- Git<\/a><\/li>\n<\/ul>\n\n\n\n
<\/span>Initial setup <\/strong><\/span><\/h2>\n\n\n\nSetting up the Stock Data Project<\/strong><\/h3>\n\n\n\n1. Clone the integration example code repository<\/a> for TiDB Cloud and Streamlit. From your terminal or command prompt (Windows user), run the following command:<\/p>\n\n\n\ngit clone https:\/\/github.com\/tidbcloud\/streamlit-stockdata-analysis.git <\/code><\/pre>\n\n\n\n2. Navigate to the project’s root directory “streamlit-stockdata-analysis”. Enter:<\/p>\n\n\n\n
cd streamlit-stockdata-analysis<\/code><\/pre>\n\n\n\n3. Install the project dependencies listed in requirements.txt. In the terminal window, enter:<\/p>\n\n\n\n
python3 -m pip install -r requirements.txt<\/code><\/pre>\n\n\n\nStoring Database Secrets<\/strong><\/h3>\n\n\n\nWe will now create a secrets file to store the connection information<\/a> for the TiDB Cloud cluster. The Streamlit app will reference this file to establish a connection with the TiDB database. For more information on connecting a Streamlit app with TiDB, please refer to the Streamlit tutorial, Connect Streamlit to TiDB<\/a>.<\/p>\n\n\n\n\n- In the project\u2019s root directory, create a file named .streamlit\/secrets.toml.<\/li>\n\n\n\n
- Add the following content to the file, replacing the placeholder values with your TiDB Cloud Cluster information:<\/li>\n<\/ol>\n\n\n\n
#.streamlit\/secrets.toml\n[tidb]\nhost = \"<TiDB_cluster_host>\"\nport = 4000\ndatabase = \"test\"\nuser = \"<TiDB_cluster_user>\"\npassword = \"<TiDB_cluster_password>\"\nssl_ca = \"<path_to_CA_store>\"<\/code><\/pre>\n\n\n\nThe Certificate Authority (CA) store path depends on your operating system. You can find this path in the Connection<\/strong> tab of the TiDB Cloud cluster. For example, on macOS, the path would be \/etc\/ssl\/cert.pem<\/code>.<\/p>\n\n\n\nFor more information on connecting a Streamlit app with TiDB, refer to the Streamlit documentation<\/a>.<\/p>\n\n\n\nCreating a TiDB Table for Storing Stock Data<\/strong><\/h3>\n\n\n\nTo store daily historical stock data for a ticker symbol in TiDB, follow these steps:<\/p>\n\n\n\n
\n- Log in to the TiDB Console<\/a> and navigate to the cluster overview page.
![\"\"](\"https:\/\/static.pingcap.com\/files\/2023\/05\/10150108\/image-37-1024x481.png\")
<\/figure><\/li>\n\n\n\n- On the left navigation pane, select the Chat2Query<\/strong> interface, and execute the SQL script shown below. The script creates a `stock_price_history` table inside the test database. This table stores information such as the volume of stock traded, dividends gained, and opening and closing prices.<\/li>\n<\/ol>\n\n\n\n
SQL\nCREATE TABLE test.stock_price_history\n(\n id int PRIMARY KEY AUTO_INCREMENT,\n Opening_Price DOUBLE,\n High DOUBLE,\n Low DOUBLE,\n Closing_Price DOUBLE,\n Volume BIGINT,\n Dividends DOUBLE,\n Market_Date DATE,\n Ticker VARCHAR(10)\n);<\/code><\/pre>\n\n\n\nThe stock data project in action<\/strong><\/h3>\n\n\n\nNow that you have completed the initial setup, it’s time to put the project into action. For this walkthrough, we will gather and analyze 10 years of stock historical data for Microsoft (MSFT) and Google (GOOGL). If you\u2019d rather track other stocks, substitute the ticker symbols you\u2019d like. The application retrieves data from the Yahoo Finance API, saves it in TiDB Cloud, and visualizes it using Plotly’s 3D charts. <\/p>\n\n\n\n
\n- Run the project on your local machine. From the terminal or command prompt (Windows user), enter
streamlit run stocks.py<\/code>.<\/li>\n\n\n\n- Launch the application in your web browser at http:\/\/localhost:8502.<\/li>\n\n\n\n
- In the default menu, select Collect Trade Data<\/strong>. Enter the ticker symbol for the stock you want to analyze, in this case, MSFT.<\/li>\n\n\n\n
- Leave the start and end dates to their default values, and click Get Data <\/strong>to retrieve 10 years of historical data for the selected stock.
![\"\"](\"https:\/\/static.pingcap.com\/files\/2023\/05\/10024353\/image-32-1024x508.png\")
<\/figure><\/li>\n\n\n\n- Click Save Data <\/strong>to store the retrieved stock data in TiDB Cloud.
![\"\"](\"https:\/\/static.pingcap.com\/files\/2023\/05\/10080909\/image-35-1024x482.png\")
<\/figure><\/li>\n\n\n\n- Repeat steps 3-5 to get data for Google, with the ticker symbol GOOGL.<\/li>\n\n\n\n
- To analyze the data, click Visualize Trade Data<\/strong>, enter the corresponding ticker symbols, keep the default date values, and click Visualize<\/strong>.
![\"\"](\"https:\/\/static.pingcap.com\/files\/2023\/05\/10080949\/image-36-1024x371.png\")
<\/figure><\/li>\n<\/ol>\n\n\n\nThis action will execute an analytical query on TiDB and display the results in a 3D chart that compares the total dividend paid and volume traded over the years for Google and Microsoft stocks.<\/p>\n\n\n\n![\"\"](\"https:\/\/static.pingcap.com\/files\/2023\/05\/10031005\/image-33.png\")
<\/figure>\n\n\n\n<\/span>Code walkthrough<\/strong><\/span><\/h2>\n\n\n\nIn this section, we’ll examine important elements of the Stock Data Project’s code. We\u2019ll explore the application\u2019s components and how they interact to collect, analyze, and visualize historical stock data. We’ll also discuss the various packages used and how they contribute to the application’s overall functionality.<\/p>\n\n\n\n
Packages used<\/strong><\/h3>\n\n\n\nimport streamlit as st\nimport yfinance as yf\nimport pandas as pd\nimport plotly.express as px\nimport pymysql<\/code><\/pre>\n\n\n\n
<\/span>Initial setup <\/strong><\/span><\/h2>\n\n\n\nSetting up the Stock Data Project<\/strong><\/h3>\n\n\n\n1. Clone the integration example code repository<\/a> for TiDB Cloud and Streamlit. From your terminal or command prompt (Windows user), run the following command:<\/p>\n\n\n\ngit clone https:\/\/github.com\/tidbcloud\/streamlit-stockdata-analysis.git <\/code><\/pre>\n\n\n\n2. Navigate to the project’s root directory “streamlit-stockdata-analysis”. Enter:<\/p>\n\n\n\n
cd streamlit-stockdata-analysis<\/code><\/pre>\n\n\n\n3. Install the project dependencies listed in requirements.txt. In the terminal window, enter:<\/p>\n\n\n\n
python3 -m pip install -r requirements.txt<\/code><\/pre>\n\n\n\nStoring Database Secrets<\/strong><\/h3>\n\n\n\nWe will now create a secrets file to store the connection information<\/a> for the TiDB Cloud cluster. The Streamlit app will reference this file to establish a connection with the TiDB database. For more information on connecting a Streamlit app with TiDB, please refer to the Streamlit tutorial, Connect Streamlit to TiDB<\/a>.<\/p>\n\n\n\n\n- In the project\u2019s root directory, create a file named .streamlit\/secrets.toml.<\/li>\n\n\n\n
- Add the following content to the file, replacing the placeholder values with your TiDB Cloud Cluster information:<\/li>\n<\/ol>\n\n\n\n
#.streamlit\/secrets.toml\n[tidb]\nhost = \"<TiDB_cluster_host>\"\nport = 4000\ndatabase = \"test\"\nuser = \"<TiDB_cluster_user>\"\npassword = \"<TiDB_cluster_password>\"\nssl_ca = \"<path_to_CA_store>\"<\/code><\/pre>\n\n\n\nThe Certificate Authority (CA) store path depends on your operating system. You can find this path in the Connection<\/strong> tab of the TiDB Cloud cluster. For example, on macOS, the path would be \/etc\/ssl\/cert.pem<\/code>.<\/p>\n\n\n\nFor more information on connecting a Streamlit app with TiDB, refer to the Streamlit documentation<\/a>.<\/p>\n\n\n\nCreating a TiDB Table for Storing Stock Data<\/strong><\/h3>\n\n\n\nTo store daily historical stock data for a ticker symbol in TiDB, follow these steps:<\/p>\n\n\n\n
\n- Log in to the TiDB Console<\/a> and navigate to the cluster overview page.
<\/figure><\/li>\n\n\n\n- On the left navigation pane, select the Chat2Query<\/strong> interface, and execute the SQL script shown below. The script creates a `stock_price_history` table inside the test database. This table stores information such as the volume of stock traded, dividends gained, and opening and closing prices.<\/li>\n<\/ol>\n\n\n\n
SQL\nCREATE TABLE test.stock_price_history\n(\n id int PRIMARY KEY AUTO_INCREMENT,\n Opening_Price DOUBLE,\n High DOUBLE,\n Low DOUBLE,\n Closing_Price DOUBLE,\n Volume BIGINT,\n Dividends DOUBLE,\n Market_Date DATE,\n Ticker VARCHAR(10)\n);<\/code><\/pre>\n\n\n\nThe stock data project in action<\/strong><\/h3>\n\n\n\nNow that you have completed the initial setup, it’s time to put the project into action. For this walkthrough, we will gather and analyze 10 years of stock historical data for Microsoft (MSFT) and Google (GOOGL). If you\u2019d rather track other stocks, substitute the ticker symbols you\u2019d like. The application retrieves data from the Yahoo Finance API, saves it in TiDB Cloud, and visualizes it using Plotly’s 3D charts. <\/p>\n\n\n\n
\n- Run the project on your local machine. From the terminal or command prompt (Windows user), enter
streamlit run stocks.py<\/code>.<\/li>\n\n\n\n- Launch the application in your web browser at http:\/\/localhost:8502.<\/li>\n\n\n\n
- In the default menu, select Collect Trade Data<\/strong>. Enter the ticker symbol for the stock you want to analyze, in this case, MSFT.<\/li>\n\n\n\n
- Leave the start and end dates to their default values, and click Get Data <\/strong>to retrieve 10 years of historical data for the selected stock.
<\/figure><\/li>\n\n\n\n- Click Save Data <\/strong>to store the retrieved stock data in TiDB Cloud.<\/figure><\/li>\n\n\n\n
- Repeat steps 3-5 to get data for Google, with the ticker symbol GOOGL.<\/li>\n\n\n\n
- To analyze the data, click Visualize Trade Data<\/strong>, enter the corresponding ticker symbols, keep the default date values, and click Visualize<\/strong>.<\/figure><\/li>\n<\/ol>\n\n\n\n
This action will execute an analytical query on TiDB and display the results in a 3D chart that compares the total dividend paid and volume traded over the years for Google and Microsoft stocks.<\/p>\n\n\n\n<\/figure>\n\n\n\n<\/span>Code walkthrough<\/strong><\/span><\/h2>\n\n\n\nIn this section, we’ll examine important elements of the Stock Data Project’s code. We\u2019ll explore the application\u2019s components and how they interact to collect, analyze, and visualize historical stock data. We’ll also discuss the various packages used and how they contribute to the application’s overall functionality.<\/p>\n\n\n\n
Packages used<\/strong><\/h3>\n\n\n\nimport streamlit as st\nimport yfinance as yf\nimport pandas as pd\nimport plotly.express as px\nimport pymysql<\/code><\/pre>\n\n\n\n
1. Clone the integration example code repository<\/a> for TiDB Cloud and Streamlit. From your terminal or command prompt (Windows user), run the following command:<\/p>\n\n\n\n 2. Navigate to the project’s root directory “streamlit-stockdata-analysis”. Enter:<\/p>\n\n\n\n 3. Install the project dependencies listed in requirements.txt. In the terminal window, enter:<\/p>\n\n\n\n We will now create a secrets file to store the connection information<\/a> for the TiDB Cloud cluster. The Streamlit app will reference this file to establish a connection with the TiDB database. For more information on connecting a Streamlit app with TiDB, please refer to the Streamlit tutorial, Connect Streamlit to TiDB<\/a>.<\/p>\n\n\n\n The Certificate Authority (CA) store path depends on your operating system. You can find this path in the Connection<\/strong> tab of the TiDB Cloud cluster. For example, on macOS, the path would be For more information on connecting a Streamlit app with TiDB, refer to the Streamlit documentation<\/a>.<\/p>\n\n\n\n To store daily historical stock data for a ticker symbol in TiDB, follow these steps:<\/p>\n\n\n\n Now that you have completed the initial setup, it’s time to put the project into action. For this walkthrough, we will gather and analyze 10 years of stock historical data for Microsoft (MSFT) and Google (GOOGL). If you\u2019d rather track other stocks, substitute the ticker symbols you\u2019d like. The application retrieves data from the Yahoo Finance API, saves it in TiDB Cloud, and visualizes it using Plotly’s 3D charts. <\/p>\n\n\n\n This action will execute an analytical query on TiDB and display the results in a 3D chart that compares the total dividend paid and volume traded over the years for Google and Microsoft stocks.<\/p>\n\n\n\n In this section, we’ll examine important elements of the Stock Data Project’s code. We\u2019ll explore the application\u2019s components and how they interact to collect, analyze, and visualize historical stock data. We’ll also discuss the various packages used and how they contribute to the application’s overall functionality.<\/p>\n\n\n\ngit clone https:\/\/github.com\/tidbcloud\/streamlit-stockdata-analysis.git <\/code><\/pre>\n\n\n\ncd streamlit-stockdata-analysis<\/code><\/pre>\n\n\n\npython3 -m pip install -r requirements.txt<\/code><\/pre>\n\n\n\nStoring Database Secrets<\/strong><\/h3>\n\n\n\n
\n
#.streamlit\/secrets.toml\n[tidb]\nhost = \"<TiDB_cluster_host>\"\nport = 4000\ndatabase = \"test\"\nuser = \"<TiDB_cluster_user>\"\npassword = \"<TiDB_cluster_password>\"\nssl_ca = \"<path_to_CA_store>\"<\/code><\/pre>\n\n\n\n\/etc\/ssl\/cert.pem<\/code>.<\/p>\n\n\n\nCreating a TiDB Table for Storing Stock Data<\/strong><\/h3>\n\n\n\n
\n
<\/figure><\/li>\n\n\n\nSQL\nCREATE TABLE test.stock_price_history\n(\n id int PRIMARY KEY AUTO_INCREMENT,\n Opening_Price DOUBLE,\n High DOUBLE,\n Low DOUBLE,\n Closing_Price DOUBLE,\n Volume BIGINT,\n Dividends DOUBLE,\n Market_Date DATE,\n Ticker VARCHAR(10)\n);<\/code><\/pre>\n\n\n\nThe stock data project in action<\/strong><\/h3>\n\n\n\n
\n
streamlit run stocks.py<\/code>.<\/li>\n\n\n\n<\/figure><\/li>\n\n\n\n<\/figure><\/li>\n\n\n\n<\/figure><\/li>\n<\/ol>\n\n\n\n<\/figure>\n\n\n\n<\/span>Code walkthrough<\/strong><\/span><\/h2>\n\n\n\n
Packages used<\/strong><\/h3>\n\n\n\n
import streamlit as st\nimport yfinance as yf\nimport pandas as pd\nimport plotly.express as px\nimport pymysql<\/code><\/pre>\n\n\n\n