「GolangDeveloper。Professional」コースの将来の学生と興味のあるすべての人のために、興味深い資料の翻訳を用意しました。
また、「Go-channelsoutsideandinside」というトピックに関するオープンウェビナーにご招待します。Goのチャネルは、この言語の基盤と機能の1つです。それらがどのように機能するかを理解することは、それらを正しく最適に使用するための鍵です。参加してトピックについて話し合ってください。
前書き
(SQL), , . SQLite - , , . , , SQLite 281 . SQLite sqlite3, .
: , . SQL , SQL .
, .
SQLite: SQL. SQLite — , . , , .
SQL: Structured Query Language ( ) — . . SQL — , .
HTTP- Go, (trades) SQLite. Python, .
Go github.com/mattn/go-sqlite3, C SQLite.
: go-sqlite cgo, . cgo , , .
Python sqlite3 read_sql Pandas, . .
Go
, , trades.go.
1: Trade
37 // Trade - / .
38 type Trade struct {
39 Time time.Time
40 Symbol string
41 Price float64
42 IsBuy bool
43 }
1 Trade. Time, , Symbol, ( , , AAPL), Price, , , , .
2:
24 schemaSQL = `
25 CREATE TABLE IF NOT EXISTS trades (
26 time TIMESTAMP,
27 symbol VARCHAR(32),
28 price FLOAT,
29 buy BOOLEAN
30 );
31
32 CREATE INDEX IF NOT EXISTS trades_time ON trades(time);
33 CREATE INDEX IF NOT EXISTS trades_symbol ON trades(symbol);
34 `
2 , Trade. 25 trades. 26-29 , Trade. 32-33 , time symbol.
3:
16 insertSQL = `
17 INSERT INTO trades (
18 time, symbol, price, buy
19 ) VALUES (
20 ?, ?, ?, ?
21 )
22 `
3 SQL- . 20 - ?
. fmt.Sprintf
SQL- — SQL-.
— . , , , . , .
4: DB
45 // DB - .
46 type DB struct {
47 sql *sql.DB
48 stmt *sql.Stmt
49 buffer []Trade
50 }
4 DB. 47 . 48 ( ) , 49 .
5: NewDB
53 // NewDB Trades SQLite.
API .
54 func NewDB(dbFile string) (*DB, error) {
55 sqlDB, err := sql.Open("sqlite3", dbFile)
56 if err != nil {
57 return nil, err
58 }
59
60 if _, err = sqlDB.Exec(schemaSQL); err != nil {
61 return nil, err
62 }
63
64 stmt, err := sqlDB.Prepare(insertSQL)
65 if err != nil {
66 return nil, err
67 }
68
69 db := DB{
70 sql: sqlDB,
71 stmt: stmt,
72 buffer: make([]Trade, 0, 1024),
73 }
74 return &db, nil
75 }
5 DB. 55 “sqlite3
”. 60 SQL-, trades, . 64 InsertSQL. 72 0 1024.
: , API DB - ( sql.DB). API , . — -.
6: Add
77 // Add . ,
.
79 func (db *DB) Add(trade Trade) error {
80 if len(db.buffer) == cap(db.buffer) {
81 return errors.New("trades buffer is full")
82 }
83
84 db.buffer = append(db.buffer, trade)
85 if len(db.buffer) == cap(db.buffer) {
86 if err := db.Flush(); err != nil {
87 return fmt.Errorf("unable to flush trades: %w", err)
88 }
89 }
90
91 return nil
92 }
6 Add. 84 (trade) . 85 , , , Flush 86, .
7: Flush
94 // Flush .
95 func (db *DB) Flush() error {
96 tx, err := db.sql.Begin()
97 if err != nil {
98 return err
99 }
100
101 for _, trade := range db.buffer {
102 _, err := tx.Stmt(db.stmt).Exec(trade.Time, trade.Symbol, trade.Price, trade.IsBuy)
103 if err != nil {
104 tx.Rollback()
105 return err
106 }
107 }
108
109 db.buffer = db.buffer[:0]
110 return tx.Commit()
111 }
7 Flush. 96 . 101 , 102 . , rollback 104. 109 . , 110 commit.
8: Close
113 // Close ( Flush) .
114 func (db *DB) Close() error {
115 defer func() {
116 db.stmt.Close()
117 db.sql.Close()
118 }()
119
120 if err := db.Flush(); err != nil {
121 return err
122 }
123
124 return nil
125 }
8 Close. 120 Flush, . 116 117 (close) . , DB, defer db.Close(), , .
9:
5 // main test , sql .
6 // _ "github.com/mattn/go-sqlite3"
7
8 import (
9 "database/sql"
10 "errors"
11 "fmt"
12 "time"
13 )
9 . 5 database/sql, API SQL. database/sql
- .
, trades
, , sqlite3 (, github.com/mattn/go-sqlite3). , “sqlite3”, , Go, , — .
10:
66 func ExampleDB() {
67 dbFile := "/tmp/db-test" + time.Now().Format(time.RFC3339)
68 db, err := trades.NewDB(dbFile)
69 if err != nil {
70 fmt.Println("ERROR: create -", err)
71 return
72 }
73 defer db.Close()
74
75 const count = 10_000
76 for i := 0; i < count; i++ {
77 trade := trades.Trade{
78 Time: time.Now(),
79 Symbol: "AAPL",
80 Price: rand.Float64() * 200,
81 IsBuy: i%2 == 0,
82 }
83 if err := db.Add(trade); err != nil {
84 fmt.Println("ERROR: insert - ", err)
85 return
86 }
87 }
88
89 fmt.Printf("inserted %d records\n", count)
90 // :
91 // inserted 10000 records
92 }
10 ( ). 67 , 73 defer
. 76 , 83 .
Python
Python analysis_trades.py.
11:
02 import sqlite3
03 from contextlib import closing
04 from datetime import datetime
05
06 import pandas as pd
11 , Python-. 2 sqlite3, 6 — pandas.
12: Select SQL
08 select_sql = """
09 SELECT * FROM trades
10 WHERE time >= ? AND time <= ?
11 """
12 SQL- . 10 trades. 10 WHERE . Go- ?
SQL- .
13:
14 def load_trades(db_file, start_time, end_time):
15 """ db_file ."""
16 conn = sqlite3.connect(db_file)
17 with closing(conn) as db:
18 df = pd.read_sql(select_sql, db, params=(start_time, end_time))
19
20 # detect_types=sqlite3.PARSE_DECLTYPES, Go , sqlite3 Python .
22 # https://bugs.python.org/issue29099# . Https://bugs.python.org/issue29099
23 df["time"] = pd.to_datetime(df["time"])
24 return df
13 . 16 . 17 , - defer
Go, , . 18 pandas read_sql
SQL- DataFrame. Python API (, database/sql), Pandas . 23 time Timestamp pandas. SQLite, TIMESTAMP .
14:
27 def average_price(df):
28 """ df, (stock, buy)"""
29 return df.groupby(["symbol", "buy"], as_index=False)["price"].mean()
14 , symbol buy. 29 DataFrame groupby
symbol buy. as_index=False
, symbol buy . price .
15:
symbol,buy,price
AAPL,0,250.82925665004535
AAPL,1,248.28277375538832
GOOG,0,250.11537993385295
GOOG,1,252.4726772487683
MSFT,0,250.9214212695317
MSFT,1,248.60187022941685
NVDA,0,250.3844763417279
NVDA,1,249.3578146208962
15 Python .
SQLite . SQLite — , . SQLite, .
, . , :
Flush
Close
DB -
Python-
SQL, SQL. , select .
SQL, , Go , sqlx, gorm, Python SQLAlchemy, Pandas.
. , SQL SQLite .
"Golang Developer. Professional".
«Go- ».