For future students of the course "Golang Developer. Professional" and all those interested, we prepared a translation of interesting material.
We also invite you to an open webinar on the topic "Go-channels outside and inside" . Channels in Go are one of the foundations and features of the language. Understanding how they work is the key to their correct and optimal use. Join to discuss the topic.
Introduction
(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- ».