本帖最后由 523066680 于 2024-1-2 17:31 编辑
把一些之前做的补发上来
第一步，创建空的 Sqlite 数据表 | use utf8; | | use Encode; | | use Modern::Perl; | | use File::Slurp; | | | | use JSON qw/from_json to_json/; | | STDOUT->autoflush(1); | | use Modern::Perl; | | use DBI; | | | | binmode(STDOUT, ":encoding(gbk)"); | | | | | | | | | | | | | | | | | | | | my $database = 'stock_data_init.sqlite'; | | unlink $database if (-f $database and decode('gbk', __FILE__) =~ /创建/ ); | | my $dbh = DBI->connect("dbi:SQLite:dbname=$database") or die "can not connect DB: $DBI::errstr"; | | | | | | $dbh->do( utf8( <<'DAY' ) ); | | CREATE TABLE IF NOT EXISTS 日K ( -- 暂时去掉 IF NOT EXISTS | | id INTEGER PRIMARY KEY AUTOINCREMENT, | | symbol TEXT NOT NULL, | | date TEXT NOT NULL, | | open REAL, | | high REAL, | | low REAL, | | close REAL, | | volume INTEGER, --成交量 | | ma5 REAL, | | ma10 REAL, | | ma20 REAL, | | ma30 REAL, | | ma60 REAL, | | ma120 REAL, | | ma250 REAL, | | 换手率 REAL, | | 涨跌幅 REAL, | | 振幅 REAL, | | 融券余额 REAL, --如果这里使用Unicode形式，会被自动转为UTF8 | | 融券余量 REAL, | | 融券净卖出 REAL, | | 融券卖出量 REAL, | | 融券偿还量 REAL | | ); | | DAY | | | | $dbh->do( utf8( <<'WEEK' ) ); | | CREATE TABLE IF NOT EXISTS 周K ( | | id INTEGER PRIMARY KEY AUTOINCREMENT, | | symbol TEXT NOT NULL, | | date TEXT NOT NULL, | | open REAL, | | high REAL, | | low REAL, | | close REAL, | | volume INTEGER, --成交量 | | ma5 REAL, | | ma10 REAL, | | ma20 REAL, | | ma30 REAL, | | ma60 REAL, | | 涨跌幅 REAL, | | 换手率 REAL | | ); | | WEEK | | | | $dbh->do( utf8( <<'MONTH' ) ); | | CREATE TABLE IF NOT EXISTS 月K ( | | id INTEGER PRIMARY KEY AUTOINCREMENT, | | symbol TEXT NOT NULL, | | date TEXT NOT NULL, | | open REAL, | | high REAL, | | low REAL, | | close REAL, | | volume INTEGER, --成交量 | | ma5 REAL, | | ma10 REAL, | | ma20 REAL, | | ma30 REAL, | | 涨跌幅 REAL, | | 换手率 REAL | | ); | | MONTH | | | | | | | | for my $ktype ( qw/日K 周K 月K/ ) | | { | | printf "%s\n", $ktype; | | $dbh->do( utf8("CREATE INDEX index_${ktype}_symbol ON ${ktype} ( symbol )") ); | | $dbh->do( utf8("CREATE INDEX index_${ktype}_date ON ${ktype} ( date )") ); | | $dbh->do( utf8("CREATE UNIQUE INDEX index_${ktype}_symbol_date ON ${ktype} ( symbol, date )") ); | | } | | | | | | insert_stock_data( "TEST", "2023-09-06", "11.2", "13.5", "20020" ); | | | | | | query_stock_data( "TEST" ); | | | | | | sub insert_stock_data | | { | | | | my $insert = $dbh->prepare(utf8("INSERT INTO 日K (symbol, date, open, close, volume) VALUES (?, ?, ?, ?, ?)")); | | | | for ( 1 .. 1 ) | | { | | $insert->execute( @_ ); | | } | | } | | | | | | sub query_stock_data | | { | | my ($symbol) = @_; | | | | my $query = $dbh->prepare( utf8("SELECT * FROM 日K WHERE symbol = ?") ); | | $query->execute($symbol); | | | | while (my $row = $query->fetchrow_hashref()) | | { | | print uni(dump_json( $row )); | | } | | } | | | | sub dump_json | | { | | my ($data) = @_; | | return to_json( $data, { allow_blessed => 1, allow_tags => 1, pretty => 1, canonical => 1 }); | | } | | | | sub gbk { encode('gbk', $_[0]) } | | sub utf8 { encode('utf8', $_[0]) } | | sub u2gbk { encode('gbk', decode('utf8', $_[0])) } | | sub uni { decode('utf8', $_[0]) }COPY |
第二步，下载2022至今的所有日K数据 (歪枣网)
- | use utf8; | | use Encode; | | use Modern::Perl; | | use File::Slurp; | | use Mojo::UserAgent; | | use File::Path qw/make_path/; | | use Date::Format; | | use Date::Parse; | | use JSON qw/from_json to_json/; | | STDOUT->autoflush(1); | | binmode(STDOUT, ":encoding(gbk)"); | | | | my $TOKEN = "歪枣网token"; | | | | my $ua = Mojo::UserAgent->new(); | | my $index = from_json( uni(scalar(read_file( "index.json" ))) ); | | my $code_map = $index->{'index_by_code'}; | | my @codes = sort keys %$code_map; | | my $total = scalar( @codes ); | | my $end_date = get_last_date(); | | my $begin_date = "2022-01-01"; | | | | my $LASTDAY = $end_date; | | $LASTDAY =~ s/-//g; | | my $wdir = "D:/Local/歪枣网/股票数据-日K-2022-${LASTDAY}"; | | make_path( gbk($wdir) ) unless -d gbk($wdir); | | | | printf "total: %s\n", $total; | | printf "end_date: %s\n", $end_date; | | | | | | | | | | my $it = 0; | | my $count = 0; | | for my $code ( @codes ) | | { | | my $name = $code_map->{ $code }; | | $name =~ s/\s//g; | | $it++; | | | | | | | | $count ++; | | my $output = gbk("${wdir}/${code}.txt"); | | printf "%s %s [%d/%d]\n", $code, $name, $it, $total; | | if ( not -f $output ) | | { | | get_data_dayKline( $code, $begin_date, $end_date, $output ); | | } | | | | } | | | | printf "total: %d\n", $count; | | | | sub get_data_dayKline | | { | | my ($code, $start_date, $end_date, $output) = @_; | | | | | | | | my %args = ( | | 'fq' => 1, | | 'type' => 1, | | 'ktype' => 101, | | 'code' => $code, | | 'startDate' => $start_date, | | 'endDate' => $end_date, | | 'fields' => 'tdate,open,high,low,close,cjl,hsl', | | 'export' => "0", | | 'token' => $TOKEN, | | ); | | | | my $res = $ua->get( "http://api.waizaowang.com/doc/getDayKLine", form => \%args )->result; | | | | | | write_file( $output, utf8( $args{'fields'} ."\n" ) ); | | append_file( $output, $res->body() ); | | } | | | | sub get_last_date | | { | | my $hour = time2str( "%H", time() ); | | my $end_date; | | | | if ( $hour ge "15" ) { $end_date = time2str("%Y-%m-%d", time() + 3600*24); } | | else { $end_date = time2str("%Y-%m-%d", time()); } | | } | | | | | | sub gbk { encode('gbk', $_[0]) } | | sub utf8 { encode('utf8', $_[0]) } | | sub u2gbk { encode('gbk', decode('utf8', $_[0])) } | | sub uni { decode('utf8', $_[0]) }COPY |
第三步，遍历本地文件，数据整合到数据库
- | | | | | | | | | use utf8; | | use Encode; | | use Modern::Perl; | | use File::Slurp; | | use File::Basename; | | use File::Copy; | | use List::MoreUtils qw/zip/; | | use JSON qw/from_json to_json/; | | STDOUT->autoflush(1); | | use Modern::Perl; | | use DBI; | | | | binmode(STDOUT, ":encoding(gbk)"); | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | my $database = 'stock_data.sqlite'; | | my $dbh = DBI->connect("dbi:SQLite:dbname=$database") or die "can not connect DB: $DBI::errstr"; | | | | my $src_dir = "D:/Local/歪枣网/股票数据-日K-2022-20231009"; | | my @files = glob gbk("${src_dir}/*.txt"); | | my $total = scalar( @files ); | | | | my $db_symbols = get_symbols_of_table( $dbh, utf8("日K") ); | | | | | | | | | | $dbh->begin_work; | | | | my $n = 0; | | for my $f ( @files ) | | { | | my ($symbol) = ($f =~ /(\d+)\.txt/); | | | | if ( exists $db_symbols->{$symbol} ) | | { | | printf "%s - data already exists in the db\n", decode('gbk', $f); | | next; | | } | | | | printf "%s [%d/%d]\n", decode('gbk', $f), ++$n, $total; | | load_daily_kline_to_db( $dbh, $f ); | | | | } | | | | $dbh->commit; | | | | | | sub load_daily_kline_to_db | | { | | my ($dbh, $file) = @_; | | | | my ($symbol) = ($file =~ /(\d+)\.txt/); | | my @lines = read_file( $file ); | | my @keys = split( /,/, $lines[0] ); | | | | | | my $insert = $dbh->prepare( | | utf8("INSERT INTO 日K (symbol, date, open, high, low, close, volume, 换手率) VALUES (?, ?, ?, ?, ?, ?, ?, ?)") | | ); | | | | my $total = scalar( @lines ) - 1; | | my $part_size = int($total / 100); | | | | | | | | | | for my $id ( 1 .. $#lines ) | | { | | $lines[$id] =~ s/;\r?\n//; | | my @values = split( /,/, $lines[$id] ); | | | | $insert->execute( $symbol, @values ); | | } | | | | $insert->finish; | | | | say ""; | | } | | | | | | sub get_symbols_of_table | | { | | my ($dbh, $tb_name) = @_; | | my %hash; | | | | | | | | my $list = $dbh->selectcol_arrayref( qq(SELECT DISTINCT symbol FROM $tb_name) ); | | | | grep { $hash{$_} = 1 } @$list; | | | | return \%hash; | | } | | | | | | | | sub query_stock_data | | { | | my ($symbol) = @_; | | | | my $query = $dbh->prepare("SELECT * FROM 日K WHERE symbol = ?"); | | $query->execute($symbol); | | | | while (my $row = $query->fetchrow_hashref()) | | { | | print uni(dump_json( $row )); | | } | | } | | | | sub dump_json | | { | | my ($data) = @_; | | return to_json( $data, { allow_blessed => 1, allow_tags => 1, pretty => 1, canonical => 1 }); | | } | | | | sub gbk { encode('gbk', $_[0]) } | | sub utf8 { encode('utf8', $_[0]) } | | sub u2gbk { encode('gbk', decode('utf8', $_[0])) } | | sub uni { decode('utf8', $_[0]) }COPY |
|
发表于 2023-3-2 22:03
| 
发表于 2023-3-2 22:50
| 
发表于 2024-1-5 22:11
| 
