💽

PythonでSQL入門

2021/10/28に公開2件

PostgreSQL

Python

SQL

tech

SQLに入門したい

名前を聞いたことはあるけど、触れたことは無かったSQL。今後、必要になる可能性があるので、少し予習してみることにしました。

Twitterでつぶやいてみたら、色々本を勧めていただいたのでとりあえず2冊買いました。

https://amzn.to/2ZtilxH

https://amzn.to/3nwsTER

どちらも初心者向けで分かりやすいと思いますが、個人的にはスッキリわかるSQL入門が好みかもしれません。

SQLとは何か？

SQL（Structured Query Language）というデータベースを操作する専用の言語です。データベース、個人レベルならCSVとかエクセルで良いのですが、大規模になるとSQLを使うことが多いようですね。理由はパフォーマンスとか、読み書き検索の使い勝手とか色々あるのでしょう（比較とかはしてないです）。

ちなみにSQLは通は「シークル」というらしいです。シークルといって通ぶりましょう。

そして、データベースを管理するにはSQLに従ってデータを管理するシステム（RDBMS）が必要です。RDBMSには、MySQL、PostgreSQL、SQLiteといったものがあります。

今回は、入門編として「SQL 第2版ゼロからはじめるデータベース操作」でメインで説明されていた「PostgreSQL」をセットアップして、簡単な動作の確認をして、Pythonからデータベースを操作するところまでをやってみました。

「とりあえずSQLに触れたい！」という人はスッキリわかるSQL入門で紹介されていた「dokoQL」がブラウザだけでSQLの編集と実行を試せるので便利です。内部は「PostgreSQL」が使われているようです。

https://dokoql.com

PostgreSQLセットアップ

PostgreSQLをセットアップしていきます。書籍ではWindows10のネイティブ環境にインストールしていましたが、私はWindows11のWSL2環境にセットアップしました。Linuxでも同じ操作でセットアップできると思います。

参考にしたサイトなどは、本記事の最後に載せておきます。

最初に、WSL2環境含めたWindows PCのセットアップに関しては以下記事参照ください。

以降はWSL2環境で実行してください。

最初にPostgreSQLをaptでインストールします。

$ sudo apt update
$ sudo apt install -y postgresql postgresql-contrib

インストールすると、初期ユーザとしてpostgresというユーザが作られるので、まずはPostgreSQLを起動してみます。

$ sudo -i -u postgres
$ psql

ところがエラー発生。

psql: could not connect to server: No such file or directory

色々調べたところ、以下実行して。Verの数字を確認。

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

以下コマンド実行してスタートすると、エラーは解消しました。

$ sudo pg_ctlcluster 12 main start

気を取り直して、以下コマンド実行するとPostgreSQLが起動します。

$ sudo -i -u postgres
$ psql

以下コマンドでも良いようです。

$ sudo -u postgres psql

テスト用に新しくユーザーを作っておきましょう。

$ createuser --interactive --pwprompt

今回は、ユーザー名karaage、パスワードkaraageとします。本番ではつけたら絶対ダメなやつです。

PostgreSQL動作確認

ここからは「SQL 第2版ゼロからはじめるデータベース操作」を参考にデータベースを構築します。

最初にDBを作ります。

postgres=# CREATE DATABASE shop;

DBにテーブルを作ります。

shop=# CREATE TABLE Shohin
shop-# (shohin_id CHAR(4) NOT NULL,
shop(# shohin_mei VARCHAR(100) NOT NULL,
shop(# shohin_bunrui VARCHAR(32) NOT NULL,
shop(# hanbai_tanka INTEGER ,
shop(# shiire_tanka INTEGER ,
shop(# torokubi DATE ,
shop(# PRIMARY KEY (shohin_id));
CREATE TABLE

テーブルにデータを入れます（最初の--の行はコメントです）。

shop=# -- DML:data
shop=# BEGIN TRANSACTION;
BEGIN
shop=# INSERT INTO Shohin VALUES ('0001', 'Tshirt', 'ifuku', 1000, 500, '2009-09-20');
INSERT 0 1
shop=# COMMIT;
COMMIT

データを確認します。

shop=# SELECT shohin_id FROM Shohin;
 shohin_id 
-----------
 0001
(1 row)

SQLにはいろいろな文法があるようですが、最低限の一通りの動作確認はこれでOKとします。

PythonでSQL

Pythonでpandas使ってCSVをいじるみたいにSQLを操作できたら便利そうですね。というわけで、Twitterで教えてもらったsqlalchemyというソフトを試してみることにします。

Pythonは、私は仮想環境を構築しています。仮想環境は必須ではないので、必要と感じたら以下記事参考にセットアップしてください。

必要なライブラリをインストールします。

$ pip3 install sqlalchemy
$ sudo apt install -y libpq-dev
$ pip3 install psycopg2

先程作ったデータベースを確認してみます。以下のようなPythonのコードを実行します。

from sqlalchemy import create_engine
from sqlalchemy import text
 

def main():
    engine = create_engine('postgresql://karaage:karaage@localhost/shop', echo=True)
    with engine.connect() as conn:
        result = conn.execute(text("SELECT shohin_id FROM Shohin;"))
        for row in result:
            print(row)

    engine.dispose()
 
if __name__ == "__main__":
    main()

$ python test_sql.py 
2021-10-28 01:32:30,115 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2021-10-28 01:32:30,115 INFO sqlalchemy.engine.Engine [raw sql] {}
2021-10-28 01:32:30,116 INFO sqlalchemy.engine.Engine select current_schema()
2021-10-28 01:32:30,116 INFO sqlalchemy.engine.Engine [raw sql] {}
2021-10-28 01:32:30,116 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2021-10-28 01:32:30,117 INFO sqlalchemy.engine.Engine [raw sql] {}
2021-10-28 01:32:30,117 INFO sqlalchemy.engine.Engine SELECT shohin_id FROM Shohin;
2021-10-28 01:32:30,118 INFO sqlalchemy.engine.Engine [generated in 0.00009s] {}
('0001',)

先程データベースを確認したときと同じ結果になりましたね。

今回は、データベースにPythonでアクセスするところまでを確認したかったので、SQL文を直接叩いていますが、sqlalchemyを使いこなせば、Pythonライクなデータベース処理をしたり、pandasと連携したりといったことも可能になりそうです。

まとめ

SQLに入門してみました。完全に理解した状態にもほど遠い状況ですが、SQL怖い状態は少し脱することができたのではないかなと思います。

うまく使いこなせば、Pythonでsqlalchemyを通じてpandasでデータベースの操作とかもできるようですね。そこまでいけば、ある程度自由自在になりそうですね。

ようやくスタートラインに立てたので、この環境で本を読みすすめてSQLに関する理解を深めていこうかなと思います。

https://amzn.to/2ZtilxH

https://amzn.to/3nwsTER

参考リンク

PostgreSQLセットアップ

Python, sqlalchemy

Discussion

坦々狸

敷居の低さを考えるとPythonでSQL入門するのでしたらsqliteが楽だと思います
標準ライブラリにあるのでpython入ってさえいれば以下のように数行コード書くだけでSQL実行まで
持っていけますよ

import sqlite3
con = sqlite3.connect('example.db')
cur = con.cursor()
cur.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
con.commit()
con.close()

SQLAlchemyのようなORMを使うのでしたら後々sqliteで性能や機能的に不満が出てきた時点でDBを用意して
create_engine等のドライバ指定している実装箇所を変更すれば移行出来るのでその段階から準備してもいいかと思います

karaage0703

コメントありがとうございます！sqliteだとこんなに簡単なのですね。参考になります。