SQL
Table of Contents
庶民には無関係？
以前PyPIに水飲み場攻撃があるよって注意喚起したけど、あからさまな記事が 発表されたぞ。
450超の悪意あるPyPIパッケージを発見、Python開発者の暗号資産が標的
まあ、資産家が対象な訳で、庶民には関係ない？ それにしても、上手い方法 を考えだすものだな。
暗号データは意味不明な長大な文字列、コピペで対応するしかない。コピペし たデータはクリップボードに保存される。その時に、悪意のあるデータに置き 換えてしまえって作戦。そういう事がおきるようにする手法が、パッケージの 入力ミスというのを起因にする。
オイラーもパッケージの作り方を勉強しようかな。
Jupyter Notebook
Webをうろうろしてると、jupyterによく出あう。手元でも環境を作っておくか。 仮想環境が使えるようになったんで、躊躇なく試してみられる。
pip install notebook jupyter notebook
debianだと、一度インストールすると、恒久的に使えるように、配置される。
sakae@deb:~$ ls .local/bin/ f2py* jupyter-nbclassic-bundlerextension* f2py3* jupyter-nbclassic-extension* f2py3.9* jupyter-nbclassic-serverextension* ipython* jupyter-nbconvert* ipython3* jupyter-nbextension* jsonpointer* jupyter-notebook* jsonschema* jupyter-run* jupyter* jupyter-server* jupyter-bundlerextension* jupyter-serverextension* jupyter-dejavu* jupyter-troubleshoot* jupyter-events* jupyter-trust* jupyter-execute* numba* jupyter-kernel* pycc* jupyter-kernelspec* pygmentize* jupyter-migrate* send2trash* jupyter-nbclassic* wsdump*
python sqlite3
前回の北極熊(polars)をみていたら、これってSQLじゃんと言う事になった。 軽くsqlite3だなと言う事になった。幸いpythonでは、標準装備らしいですか ら。
import sqlite3
組み込みなモジュールは、専用コマンドで説明がみられるとな。
sakae@deb:~$ pydoc3 sqlite3
import sqlite3
cx = sqlite3.connect("test.db") # test.db will be created or opened
cu = cx.cursor()
# create a table
cu.execute("create table lang(name, first_appeared)")
# insert values into a table
cu.execute("insert into lang values (?, ?)", ("C", 1972))
# execute a query and iterate over the result
for row in cu.execute("select * from lang"):
print(row)
cx.close()
example
"IT研究者のひらめき本棚"なんて 本をみていたら、SQLパズルと言う本が紹介されてた。SQLは手続型では なくて、Lispみたいに、宣言型言語の範疇に属すらしい。例題に、『忙しい麻 酔医』なんてのが紹介されてた。
手術を複数かけもちして麻酔を施す。重複した場合は、掛け持ち数に応じて勤 務時間が減額される。この給与計算をSQLで表現しなさいという問題。
『SQLパズル 第2版』 に、サンプル問題が載っていた。
CREATE TABLE Procs( proc_id INTEGER, anest_name VARCHAR(64), start_time TIMESTAMP, end_time TIMESTAMP ); INSERT INTO Procs VALUES( 10, 'Baker', '01-07-01 08:00', '01-07-01 11:00'); INSERT INTO Procs VALUES( 20, 'Baker', '01-07-01 09:00', '01-07-01 13:00'); INSERT INTO Procs VALUES( 30, 'Dow' , '01-07-01 09:00', '01-07-01 15:30'); INSERT INTO Procs VALUES( 40, 'Dow' , '01-07-01 08:00', '01-07-01 13:30'); INSERT INTO Procs VALUES( 50, 'Dow' , '01-07-01 10:00', '01-07-01 11:30'); INSERT INTO Procs VALUES( 60, 'Dow' , '01-07-01 12:30', '01-07-01 13:30'); INSERT INTO Procs VALUES( 70, 'Dow' , '01-07-01 13:30', '01-07-01 14:30'); INSERT INTO Procs VALUES( 80, 'Dow' , '01-07-01 18:00', '01-07-01 19:00');
2人の麻酔医の勤務表。Baker医師は、9:00 - 11:00 の間、掛け持ちしてるな、 なんて事が一望できる。
手術番号の10から見ると20が重複。同じ医師の担当の20から見ると10が重複。 よって、手術番号の10、20は、重複数が2となる。別な医師の担当になる、80 番は、何処共重複がないので、重複数は1となるべき。
ans
さっぱり、開発の糸口がみつらかないので、先人の知恵を拝借。
可視化 SQL | 重なる時間と CROSS JOIN (その１)
可視化 SQL | 重なる時間と INNER JOIN (2)
本の回答例としては、 SQL_Puzzle_2nd_Ed.lzh なんてのが、公開されている。
CREATE VIEW Vprocs (id1, id2, total) AS SELECT P1.proc_id, P2.proc_id, COUNT(*) FROM Procs AS P1, Procs AS P2, Procs AS P3 WHERE P2.anest_name = P1.anest_name AND P3.anest_name = P1.anest_name AND P1.start_time <= P2.start_time AND P2.start_time < P1.end_time AND P3.start_time <= P2.start_time AND P2.start_time < P3.end_time GROUP BY P1.proc_id, P2.proc_id; SELECT id1 AS proc_id, MAX(total) AS max_inst_count FROM Vprocs GROUP BY id1;
mariadb
別解が出てたので試してみると、
sqlite> SELECT X.anest_name, MAX(X.proc_tally)
...> FROM (SELECT P1.anest_name, COUNT(DISTINCT proc_id)
...> FROM Procs AS P1, Clock AS C
...> WHERE C.clock_time BETWEEN P1.start_time AND P1.end_time
...> GROUP BY P1.anest_name) AS X(anest_name, proc_tally)
...> GROUP BY X.anest_name;
Parse error: near "(": syntax error
P1.end_time GROUP BY P1.anest_name) AS X(anest_name, proc_tally) GR
error here ---^
あえなく撃沈。それ以前に怪しい演算があるけどね。ちゃんとしたDBだったら サポートしてる？
sqlite3は何処でもDBを目指した軽い奴。重いDBとして、すぐに思いつくのは、 ボラクルとかMySQLとかPostgreDBとかだ。昔のよしみでMySQLに手をだしてみ る。何せボラクルはブランド品で庶民には手が出ない。ボラクル使ってて落馬 したら、それは馬の責任と言い逃れるためですから。
MySQLは性能が優れていたため、脅威を感じたボラクルは、それを買収しちゃっ た。飼い殺しが裏の目的ね。
で、MySQLの作者さんは、二女が生れた事もあって、彼女の名前を冠した mariadbのプロジェクトを推進(長女の名前は言うまでもないけど、My)。これがOSS界に認められて、広く採用されてい る。apt install mariadbで、簡単に導入できる。 多数のコマンドがmysqlって名前でも利用できるように、リンクされている。
MySQLは確か使い始めるまでに、何か儀式が有ったはず。
at FreeBSD
サーバーはFreeBSDが良いと思うので、リナはとりあえず無視します(個人の感 想です)。それから、systemdとかは、大嫌いだったりします。
[sakae@fb ~]$ mysql -u root -D doctor -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 23 Server version: 10.6.11-MariaDB FreeBSD Ports Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
一番最新の物を入れた。
root@localhost [doctor]> show tables; +------------------+ | Tables_in_doctor | +------------------+ | Procs | +------------------+ 1 row in set (0.000 sec) root@localhost [doctor]> select * from Procs; +---------+------------+---------------------+---------------------+ | proc_id | anest_name | start_time | end_time | +---------+------------+---------------------+---------------------+ | 10 | Baker | 2001-07-01 08:00:00 | 2001-07-01 11:00:00 | | 20 | Baker | 2001-07-01 09:00:00 | 2001-07-01 13:00:00 | | 30 | Dow | 2001-07-01 09:00:00 | 2001-07-01 15:30:00 | | 40 | Dow | 2001-07-01 08:00:00 | 2001-07-01 13:30:00 | | 50 | Dow | 2001-07-01 10:00:00 | 2001-07-01 11:30:00 | | 60 | Dow | 2001-07-01 12:30:00 | 2001-07-01 13:30:00 | | 70 | Dow | 2001-07-01 13:30:00 | 2001-07-01 14:30:00 | | 80 | Dow | 2001-07-01 18:00:00 | 2001-07-01 19:00:00 | +---------+------------+---------------------+---------------------+ 8 rows in set (0.000 sec)
テーブルを 作くった。
root@localhost [doctor]> CREATE VIEW Vprocs (id1, id2, total) AS
-> SELECT P1.proc_id, P2.proc_id, COUNT(*)
-> FROM Procs AS P1, Procs AS P2, Procs AS P3
-> WHERE P2.anest_name = P1.anest_name
-> AND P3.anest_name = P1.anest_name
-> AND P1.start_time <= P2.start_time
-> AND P2.start_time < P1.end_time
-> AND P3.start_time <= P2.start_time
-> AND P2.start_time < P3.end_time
-> GROUP BY P1.proc_id, P2.proc_id;
Query OK, 0 rows affected (0.002 sec)
root@localhost [doctor]> select * from Vprocs;
+------+------+-------+
| id1 | id2 | total |
+------+------+-------+
| 10 | 10 | 1 |
| 10 | 20 | 2 |
| 20 | 20 | 2 |
| 30 | 30 | 2 |
| 30 | 50 | 3 |
| 30 | 60 | 3 |
| 30 | 70 | 2 |
| 40 | 30 | 2 |
| 40 | 40 | 1 |
| 40 | 50 | 3 |
| 40 | 60 | 3 |
| 50 | 50 | 3 |
| 60 | 60 | 3 |
| 70 | 70 | 2 |
| 80 | 80 | 1 |
+------+------+-------+
15 rows in set (0.001 sec)
root@localhost [doctor]> SELECT id1 AS proc_id, MAX(total) AS max_inst_count
-> FROM Vprocs
-> GROUP BY id1;
+---------+----------------+
| proc_id | max_inst_count |
+---------+----------------+
| 10 | 2 |
| 20 | 2 |
| 30 | 3 |
| 40 | 3 |
| 50 | 3 |
| 60 | 3 |
| 70 | 2 |
| 80 | 1 |
+---------+----------------+
8 rows in set (0.001 sec)
sqlite3並みに動くか、とりあえず確認。
root@localhost [doctor]> SELECT X.anest_name, MAX(X.proc_tally)
-> FROM (SELECT P1.anest_name, COUNT(DISTINCT proc_id)
-> FROM Procs AS P1, Clock AS C
-> WHERE C.clock_time BETWEEN P1.start_time AND P1.end_time
-> GROUP BY P1.anest_name) AS X(anest_name, proc_tally)
-> GROUP BY X.anest_name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(anest_name, proc_tally)
GROUP BY X.anest_name' at line 5
問題のやつ。やっぱりダメだわさ。
マリア + 蛇
とか言うと、場末の某看板を思いだしてしまうのは、オイラーだけ？
大体マリア様と言ったら、西欧ではありがたーいお名前。日本だと観音様から 頂いたCANONが有名。最近だと魚君のアシスタントで出演してる香音ちゃんが いるかな。昔はマイちゃんとルビーの組み合わせで、掲示板を 作くったもの だ。この道はいつか来た道の再現です。
(mypy) [sakae@fb /tmp]$ cat doctor.py
import pymysql.cursors
# Connect to the database
connection = pymysql.connect(host='localhost',
user='root',
password='maria+canon+bosatu',
database='doctor',
cursorclass=pymysql.cursors.DictCursor)
with connection:
with connection.cursor() as cursor:
sql = """SELECT id1 AS proc_id, MAX(total) AS max_inst_count
FROM Vprocs
GROUP BY id1"""
cursor.execute(sql)
for row in cursor:
print(row)
userとpasswordは、自分用に変更する。この際だから、ご利益のある言霊をお 勧め、って、スピリチェリアルな世界ですよ。そう、その方面では有名な黄色 の髪の方がおられますなあ。
(mypy) [sakae@fb /tmp]$ python doctor.py
{'proc_id': 10, 'max_inst_count': 2}
{'proc_id': 20, 'max_inst_count': 2}
{'proc_id': 30, 'max_inst_count': 3}
{'proc_id': 40, 'max_inst_count': 3}
{'proc_id': 50, 'max_inst_count': 3}
{'proc_id': 60, 'max_inst_count': 3}
{'proc_id': 70, 'max_inst_count': 2}
{'proc_id': 80, 'max_inst_count': 1}
マリア + ルビー
mysql2 を見ると、1億5千万回もDLされてる。レール様々なのかな。単独で使 うような、ガッツのある人は余りいないだろうから。
ruby-mysql と ruby-mysql2 アッ、懐しい人に遭遇。お元気でなにより。昔よ く、お世話になったものだ。
require 'mysql2'
client = Mysql2::Client.new(host: "localhost",
username: "root",
password: 'maria+canon+bosatu',
database: 'doctor')
SQL='SELECT id1 AS proc_id, MAX(total) AS max_inst_count
FROM Vprocs
GROUP BY id1'
results = client.query(SQL)
results.each do |row|
puts row
end
rbenv vs. rvm
「Rubyを始めよう！・・・・rbenv? gem? rvm?」
複数のrubyを渡り歩くってレールの影響だな。レールとGemは外来種で、ruby の良き文化を破壊してると思うのは、オイラーだけ？
レールはSQL恐怖症をとり除けます。rubyとSQLとの間に介入して、ミスマッチ ングを解消しますと、言葉巧に素人に接近。また、面倒な事はGem任せで大丈 夫と、誇大広告を打った。これで皆がイチコロさ。
PostgreSQL
まあ、申し訳程度にリナでも動かしてみるか。
PostgreSQL 9.6.5文書 and man psql (good for terminal commands)
sakae@deb:~$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
doctor | sakae | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
全体の確認。
sakae@deb:~$ psql -U sakae -d doctor -h localhost
Password for user sakae:
psql (13.9 (Debian 13.9-0+deb11u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
doctor=# \dt;
List of relations
Schema | Name | Type | Owner
--------+-------+-------+-------
public | procs | table | sakae
(1 row)
doctor=# \d procs;
Table "public.procs"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
proc_id | integer | | |
anest_name | character varying(64) | | |
start_time | timestamp without time zone | | |
end_time | timestamp without time zone | | |
doctor=# \dv;
List of relations
Schema | Name | Type | Owner
--------+--------+------+-------
public | vprocs | view | sakae
(1 row)
tableとviewの確認は別々なコマンドなのね。
mysql2 と、pg の利用度およびDBのランキングを参考に、レールの侵食度合い をフェルミ推定してみよう。