為替データを取得してデータベースへFloatで保存し失敗したときのメモ
Web Scraping ぽいことをするC#の簡単なプログラムを書いたときの失敗メモ。
何をさせるのかはどうでもよかったのだが、為替データ（米ドル、豪ドル）を取得し、Windows 10 HomeのPCのMSSQLLocalDBに保存するようにした。ローカルDBはもともと存在したのか、Visual Studioをインストールしたからなのかは分からないが、あるものは使おう。
PS >sqllocaldb info .\IIS_LOCAL MSSQLLocalDB
まだ「独習C#」で勉強中の身で、async, awaitの理解があやふやだが、とりあえず動いているようだった。
using System; using System.Net; using System.Threading.Tasks; using System.Text.RegularExpressions; using System.Data.SqlClient; namespace SelfCSharp.Another { class GetExchangeRateAsync { static void Main(string[] args) { string [] currencies = {"us","au"}; string urlbase = "http://*************"; string url; string constr = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=test1db;Connect Timeout=60;Integrated Security=True"; string sqlstr = "insert into Exchange (Currency,Date, Rate) values (@Currency,@Date,@Rate)"; SqlConnection con; SqlCommand com; double rate; con = new SqlConnection(constr); con.Open(); foreach(var currency in currencies) { url = urlbase + currency; Console.WriteLine(url); Task<string> t = GetHtml(url); t.Wait(); var pattern = "<td class=\"grid-16 _fz-6l _fz-3l-sm _fw-b _ta-r\">(.+?)-(.+?)</td>"; var rgx = new Regex(pattern); var match = rgx.Match(t.Result); if (match.Success) { Console.WriteLine("**** match Success.****"); Console.WriteLine($"位置:{match.Index} マッチ文字列：{match.Value}"); foreach (Group m in match.Groups) { Console.WriteLine(m.Value); } Console.WriteLine("{0} {1}",DateTime.Now.Date,match.Groups[1].Value); rate = double.Parse(match.Groups[1].Value); Console.WriteLine("rate : {0}",rate); com = new SqlCommand(sqlstr, con); com.Parameters.AddWithValue("@Currency",currency); com.Parameters.AddWithValue("@Date",DateTime.Now.Date); com.Parameters.AddWithValue("@Rate",rate); com.ExecuteNonQuery(); com.Dispose(); } else { Console.WriteLine("**** match Failed.****"); } } con.Close(); Console.WriteLine("Main : Completed"); } static async Task<string> GetHtml(string url) { var client = new WebClient(); client.Encoding = System.Text.Encoding.UTF8; var result = await client.DownloadStringTaskAsync(url); return result; } } }
しかーし、テーブルを作成するときに
CREATE TABLE [dbo].[Exchange] ( [Currency] NCHAR(10) NOT NULL , [Date] DATE NOT NULL DEFAULT getdate(), [Rate] FLOAT NOT NULL, [Entry] DATETIME NOT NULL DEFAULT getdate(), PRIMARY KEY ([Currency], [Date]) )
と為替データを格納するテーブルExchangeのカラムRateをfloatで作成してしまうミスを犯し、以下のように値に誤差が含まれてしまっていた。
どうもdecimalにするのが常識らしい。（カラム名をEntryからTimestampに変更しています）
Currency Date Rate Timestamp ---------- ---------------- ------------------------ ----------------------- us 2021-07-31 109.66 2021-07-31 16:11:07.793 us 2021-08-01 109.66 2021-08-01 13:53:41.073 us 2021-08-02 109.69 2021-08-02 13:40:44.490 us 2021-08-03 109.13 2021-08-03 15:26:43.037 us 2021-08-04 109.03 2021-08-04 14:57:05.577 us 2021-08-05 109.66 2021-08-05 16:14:51.723 us 2021-08-06 109.81 2021-08-06 14:01:31.887 us 2021-08-10 110.31 2021-08-10 14:13:09.083 us 2021-08-11 110.65000000000001 2021-08-11 14:34:25.710 us 2021-08-12 110.39 2021-08-12 17:22:30.653 us 2021-08-13 110.40000000000001 2021-08-13 13:31:51.550 us 2021-08-14 109.56999999999999 2021-08-14 13:06:51.317 us 2021-08-15 109.56999999999999 2021-08-15 15:35:42.730 us 2021-08-17 109.23 2021-08-17 09:49:16.260 us 2021-08-18 109.59999999999999 2021-08-18 15:13:38.583 us 2021-08-19 109.59999999999999 2021-08-19 17:40:11.100
◎テーブルのカラムの型をfloatからdecimalに変更
インスタンスを開始しなくてもsqlcmdで接続できるようだ。また、-S以外はすべて小文字でも大丈夫のようだ。（どうせなら-Sも小文字を許容してほしかった。-eは既定値なので不要だが入れておいた）
PS >sqllocaldb start mssqllocaldb LocalDB インスタンス "MSSQLLocalDB" が開始されました。 PS >sqlcmd -S '(localdb)\mssqllocaldb' -e 1> use test1db 2> go use test1db Changed database context to 'test1db'. 1> alter table exchange alter column Rate DECIMAL(10,4) 2> go
少し不安だったが、カラムの型をDECIMAL(10,4)に変更したら、きれいに小数点以下4桁にしてくれた。
Currency Date Rate Timestamp ---------- ---------------- ------------ ----------------------- us 2021-07-31 109.6600 2021-07-31 16:11:07.793 us 2021-08-01 109.6600 2021-08-01 13:53:41.073 us 2021-08-02 109.6900 2021-08-02 13:40:44.490 us 2021-08-03 109.1300 2021-08-03 15:26:43.037 us 2021-08-04 109.0300 2021-08-04 14:57:05.577 us 2021-08-05 109.6600 2021-08-05 16:14:51.723 us 2021-08-06 109.8100 2021-08-06 14:01:31.887 us 2021-08-10 110.3100 2021-08-10 14:13:09.083 us 2021-08-11 110.6500 2021-08-11 14:34:25.710 us 2021-08-12 110.3900 2021-08-12 17:22:30.653 us 2021-08-13 110.4000 2021-08-13 13:31:51.550 us 2021-08-14 109.5700 2021-08-14 13:06:51.317 us 2021-08-15 109.5700 2021-08-15 15:35:42.730 us 2021-08-17 109.2300 2021-08-17 09:49:16.260 us 2021-08-18 109.6000 2021-08-18 15:13:38.583 us 2021-08-19 109.6000 2021-08-19 17:40:11.100
データベースの内容を確認するために( 私はPowerShellの)スクリプトを用意。
sqlcmd -S '(localdb)\mssqllocaldb' -d test1db -i sql.txt
sql.txtは最新１０件のデータを検索するようにrow_number()を使用した。
with ordered as
(
select row_number() over ( order by date desc ) as rowno, currency, date, rate from exchange where currency = 'us'
)
select currency, date, rate from ordered
where rowno <= 10 order by rowno desc
go
with ordered as
(
select row_number() over ( order by date desc ) as rowno, currency, date, rate from exchange where currency = 'au'
)
select currency, date, rate from ordered
where rowno <= 10 order by rowno desc
go
quit了
