iT邦幫忙

3

以Postgresql為主,再聊聊資料庫 PLPython 24節氣應用

在資訊系統中,曆法是重要的環節.昨天我們使用了Python
來取得24節氣的資料.參考以下

https://ithelp.ithome.com.tw/articles/10230451

24節氣是由立春(編號0)開始到次年的大寒(編號23),所以要查詢
完整的農曆年對應節氣,是要產生兩個西元年份,再來統整.

若我們將產生的結果轉存到資料庫中,處理起來及後續應用會更方便.
既然是透過Python取得,一般會使用Python的資料庫driver將結果
存入資料庫中.
今天我們要使用另外的方式,在Postgresql中使用 Stored Procedure
,而且是用Python.就是 PLPython.

神雕俠侶中的金輪法王的龍象般若功,練成之後威力很好很強大.
Python 與 Postgresql 對應到龍象.

https://ithelp.ithome.com.tw/upload/images/20200317/20050647zblEM7EICr.png

來看看怎樣使用吧.

在Postgresql中,每個使用者登入時都必須要到一個database,
database中可以視需要建立schema,以方便管理資料庫物件(table, function等等).

在此先建立一個 akifunc 的 schema,以方便管理 functions.

create schema akifunc;

因為 PLPython 與 Postgresql 之間, 資料型態
PG                      Python3
int2, int4              int
int8                    int
real,double,numeric     float
bytea                   bytes
text,char,varchar       str
其他型態                 str 

所以在此Python的 datetime 型態傳出來,我們使用了text,後續再轉型態.

create or replace function akifunc.solarterms (iyear int)
  returns table(tcycyear text, tsltrms text, iseq int, ttsz text)
as $_aki_$
  import eacal
 
  ct = eacal.EACal(zh_t=True)
  tcycyear = ct.get_cycle_year(iyear)
  terms = ct.get_annual_solar_terms(iyear)[2:] + ct.get_annual_solar_terms(iyear+1)[0:2]
    
  return [(tcycyear, tupl[0], tupl[1], tupl[2]) for tupl in terms]
$_aki_$ language plpython3u;

--
試試看查詢結果

select iyear
     , sol.*
  from generate_series(2019,2021) gs(iyear)
  join lateral 
       (select *
          from akifunc.solarterms(iyear)
       ) sol
       on true;

+-------+----------+---------+------+----------------------------------+
| iyear | tcycyear | tsltrms | iseq |               ttsz               |
+-------+----------+---------+------+----------------------------------+
|  2019 | 己亥     | 立春    |    0 | 2019-02-04 11:14:11.029273+08:00 |
|  2019 | 己亥     | 雨水    |    1 | 2019-02-19 07:03:50.211474+08:00 |
...
|  2019 | 己亥     | 小寒    |   22 | 2020-01-06 05:29:55.861494+08:00 |
|  2019 | 己亥     | 大寒    |   23 | 2020-01-20 22:54:28.653875+08:00 |
|  2020 | 庚子     | 立春    |    0 | 2020-02-04 17:03:09.399758+08:00 |
|  2020 | 庚子     | 雨水    |    1 | 2020-02-19 12:56:51.600730+08:00 |
|  2020 | 庚子     | 驚蟄    |    2 | 2020-03-05 10:56:46.578537+08:00 |
|  2020 | 庚子     | 春分    |    3 | 2020-03-20 11:49:32.896498+08:00 |
...
|  2021 | 辛丑     | 小寒    |   22 | 2022-01-05 17:13:49.823105+08:00 |
|  2021 | 辛丑     | 大寒    |   23 | 2022-01-20 10:38:52.179387+08:00 |
+-------+----------+---------+------+----------------------------------+
(72 rows)

在實際應用上,秒以下可以忽略.
所以後續建立 table時,將其精度先調整好.

create table solarterms (
  id int generated always as identity
, iyear int not null
, iseq smallint not null
, tcycyear text not null
, tsltrms text not null
, rtsz tstzrange not null
);

--
with t1 as (
select iyear
     , iseq
     , tcycyear
     , tsltrms
     , tstzrange(ttsz::timestamptz(0),
       lead(ttsz::timestamptz(0)) over(order by iyear, iseq),
       '[)')
  from generate_series(2019,2021) gs(iyear)
  join lateral 
       (select *
          from akifunc.solarterms(iyear)
       ) sol
       on true
)
insert into solarterms(iyear, iseq, tcycyear, tsltrms, rtsz)
select *
  from t1;
--
select *
  from solarterms;

+----+-------+------+----------+---------+-----------------------------------------------------+
| id | iyear | iseq | tcycyear | tsltrms |                        rtsz                         |
+----+-------+------+----------+---------+-----------------------------------------------------+
|  1 |  2019 |    0 | 己亥     | 立春    | ["2019-02-04 11:14:11+08","2019-02-19 07:03:50+08") |
|  2 |  2019 |    1 | 己亥     | 雨水    | ["2019-02-19 07:03:50+08","2019-03-06 05:09:40+08") |
|  3 |  2019 |    2 | 己亥     | 驚蟄    | ["2019-03-06 05:09:40+08","2019-03-21 05:58:23+08") |
...
| 25 |  2020 |    0 | 庚子     | 立春    | ["2020-02-04 17:03:09+08","2020-02-19 12:56:52+08") |
| 26 |  2020 |    1 | 庚子     | 雨水    | ["2020-02-19 12:56:52+08","2020-03-05 10:56:47+08") |
| 27 |  2020 |    2 | 庚子     | 驚蟄    | ["2020-03-05 10:56:47+08","2020-03-20 11:49:33+08") |
| 28 |  2020 |    3 | 庚子     | 春分    | ["2020-03-20 11:49:33+08","2020-04-04 15:38:09+08") |
| 29 |  2020 |    4 | 庚子     | 清明    | ["2020-04-04 15:38:09+08","2020-04-19 22:45:28+08") |
...
| 71 |  2021 |   22 | 辛丑     | 小寒    | ["2022-01-05 17:13:50+08","2022-01-20 10:38:52+08") |
| 72 |  2021 |   23 | 辛丑     | 大寒    | ["2022-01-20 10:38:52+08",)                         |
+----+-------+------+----------+---------+-----------------------------------------------------+
(72 rows)

Time: 0.904 ms

--------
後續可以使用 Postgresql 強大的 range type operator 來查詢.
例如可以用 包含於運算子 <@ 

select now()
     , tcycyear
     , tsltrms
     , rtsz 
  from solarterms
 where now() <@ rtsz;

+-------------------------------+----------+---------+-----------------------------------------------------+
|              now              | tcycyear | tsltrms |                        rtsz                         |
+-------------------------------+----------+---------+-----------------------------------------------------+
| 2020-03-17 12:19:27.806164+08 | 庚子     | 驚蟄    | ["2020-03-05 10:56:47+08","2020-03-20 11:49:33+08") |
+-------------------------------+----------+---------+-----------------------------------------------------+
(1 row)


1 則留言

0
海綿寶寶
iT邦大神 1 級 ‧ 2020-03-17 15:09:55

坦白說
我看這個「龍象」圖片看著有點不咋地
/images/emoticon/emoticon16.gif

看更多先前的回應...收起先前的回應...

https://ithelp.ithome.com.tw/upload/images/20200317/20050647Haip2uZopB.png

這有點太極的味道了
/images/emoticon/emoticon76.gif

還以為你會說
https://ithelp.ithome.com.tw/upload/images/20200317/20050647ZjryvQxmik.jpg

要說也是說這個
/images/emoticon/emoticon24.gif

我要留言

立即登入留言