During quantitative development, it is always a hassle to obtain brokerage data every time. Therefore, building a local database for storage can save a lot of unnecessary performance and resource overhead. In general, it is recommended to use a clickhouse database as your database storage. However, in order to get started quickly, this article will use postgresql as an example to do database storage.
Local postgresql I use the debian system to install it. Reason for using Debian: Debian's shell operation can be compatible with more Linux operating systems, such as Ubuntu series, Huawei Euler series, Kirin OS series, etc.
Step 1: Update your system
First, make sure your system is up to date. Open the terminal and execute:
sudo apt update sudo apt upgrade 
Step 2: Install PostgreSQL
PostgreSQL is included in the official Debian repository. You can install the latest PostgreSQL version directly. Run the following command to install:
sudo apt install postgresql 
Step 3: Start and enable the service
Once the installation is complete, the PostgreSQL service usually starts automatically. You can check the status of the service:
sudo systemctl status postgresql If the service is not running, you can start and set up boot startup:
sudo systemctl start postgresql sudo systemctl enable postgresql Step 4: Create a database and users
Once installed, you can start creating databases and users. First, switch to the postgres user:
sudo -i -u postgres You can then go to the PostgreSQL command-line interface:
psql
Here you can create a new user and database and grant the user permissions:
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword'; CREATE DATABASE mydb; GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser; Grant permission to public
-- Give specific users access to the schema
GRANT USAGE ON SCHEMA my_schema TO your_user; -- Give a specific user all permissions to all tables in the schema
GRANT ALL ON ALL TABLES IN SCHEMA my_schema TO your_user; -- Set default permissions to ensure that all new tables created in the schema in the future will automatically be given the appropriate permissions to specific users
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT ALL ON TABLES TO your_user; Exit psql with \q, and then go back to normal users using exit.
Step 5: Configure remote access
If you need to connect to your PostgreSQL database remotely, you need to modify the postgresql.conf and pg_hba.conf files. These files are usually located in the /etc/postgresql/version/main/ directory.
【Note】My current latest version is version 15, if you download it in the future may not be version 15, please configure it according to the actual version. For example, configure the default path to 15 which is your actual version, such as 16, 17, 18, and so on.
Edit postgresql.conf and set the listen_addresses to '*':
sudo vim /etc/postgresql/15/main/postgresql.conf Find listen_addresses in the file and modify it:
listen_addresses = '*' Before:
After the change:
Edit pg_hba.conf to add or modify rules to allow remote connections:
sudo vim /etc/postgresql/15/main/pg_hba.conf Add where appropriate:
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 md5 Restart the PostgreSQL service for the changes to take effect:
sudo systemctl restart postgresql You can then use some client-side tools to connect to the database, such as DataGrip, Navicat, Dbeaver, etc.
And there is no information such as the table structure in it.
Implement automatic creation of a specified table structure based on the table name. It is used to store the historical data of all the big A's. First, create an entity class and use it to specify the table field name, which is used to generate the table structure of all stocks in a unified manner.
and reserve three fields to store DIF, DEA, and MACD data
Create a simple helper class for manipulating database usage. After an instance is created, it provides a method to obtain the sqlsugarscope object and the collection of existing table information by default.
Finally, a method is provided to create a table structure, which is used to specify the entity classes required for the table structure and the table name. Table name: Here you can use the stock symbol as the table name
Create a table where you got the stock list before. For the sake of simplicity, the bosses can make other extensions to the helper class to support dependency injection, etc.
When you start executing the table, you can see that the table schema is being created continuously
Expand any table to see that the fields have been successfully created. These tables are then used to store all historical data for the ticker symbol corresponding to the table name.
After the execution is complete, a total of 5,361 tables can be displayed.
Next, add CRUD operations in the database operation helper class
After obtaining historical data, DIF, DEA, and MACD data are also calculated, and written into the database together:
When you run the Get History job, you can see the information about the acquisition records of brushing.
Open the database table and view the data, and you can see that the data is successfully written, and the DIF, DEA, AND MACD data are also successfully recorded. The data on the app is also consistent with Tongdaxin and other apps (the app shows the data after rounding)
Wait for the program to be executed, and the entire historical data will be successfully generated. Historical data can be used to easily calculate the historical simulation performance of your strategy and form your personal wealth.
The above is just a brick throw, you can also save minute data, weekly data, and automatically add the day's data after the close of each trading day for maintenance, etc., which will not be listed here.
Database core operation helper classes for reference:
public class DatabaseHelper { private DbTableInfo[] _initTables=new DbTableInfo[0]; private SqlSugarScope _sqlScope = ; public DatabaseHelper() { _sqlScope = GetDbScope(); GetTableList(_sqlScope); } /// <summary> /// 返回表名称 /// </summary> /// <returns></returns> public string[] GetStockTableNames() { return _initTables.Select(x => x.Name).ToArray(); } private SqlSugarScope GetDbScope() { var db = new SqlSugarScope(new ConnectionConfig() { ConnectionString = "Host=ip;Port=port;Username=username;Password=pwd;Database=dbname;", DbType = DbType.PostgreSQL, IsAutoCloseConnection = true, InitKeyType = InitKeyType.Attribute }); return db; } /// <summary> /// 获取已有的数据库表 /// </summary> /// <param name="db"></param> /// <param name="tableName"></param> /// <returns></returns> private void GetTableList(SqlSugarScope db) { _initTables = db.DbMaintenance.GetTableInfoList(false).ToArray(); } public void CreateTableWithCustomName<T>(string tableName) { try { if (!_initTables.Any(x => x.Name == tableName)) { _sqlScope.MappingTables.Add(typeof(T).Name, tableName); // 通过动态指定表名来初始化或迁移表结构 _sqlScope.CodeFirst.InitTables<T>(); // 解除映射 _sqlScope.MappingTables.Remove(new MappingTable() { EntityName = typeof(T).Name }); } else { Debug.WriteLine($"Table '{tableName}' already exists.");