PythonでSQL入門
SQLに入門したい
名前を聞いたことはあるけど、触れたことは無かったSQL。今後、必要になる可能性があるので、少し予習してみることにしました。
Twitterでつぶやいてみたら、色々本を勧めていただいたのでとりあえず2冊買いました。
どちらも初心者向けで分かりやすいと思いますが、個人的にはスッキリわかるSQL入門が好みかもしれません。
SQLとは何か？
SQL（Structured Query Language）というデータベースを操作する専用の言語です。データベース、個人レベルならCSVとかエクセルで良いのですが、大規模になるとSQLを使うことが多いようですね。理由はパフォーマンスとか、読み書き検索の使い勝手とか色々あるのでしょう（比較とかはしてないです）。
ちなみにSQLは通は「シークル」というらしいです。シークルといって通ぶりましょう。
そして、データベースを管理するにはSQLに従ってデータを管理するシステム（RDBMS）が必要です。RDBMSには、MySQL、PostgreSQL、SQLiteといったものがあります。
今回は、入門編として「SQL 第2版 ゼロからはじめるデータベース操作」でメインで説明されていた「PostgreSQL」をセットアップして、簡単な動作の確認をして、Pythonからデータベースを操作するところまでをやってみました。
「とりあえずSQLに触れたい！」という人はスッキリわかるSQL入門で紹介されていた「dokoQL」がブラウザだけでSQLの編集と実行を試せるので便利です。内部は「PostgreSQL」が使われているようです。
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に関する理解を深めていこうかなと思います。
参考リンク
PostgreSQLセットアップ
Python, sqlalchemy
Discussion
敷居の低さを考えるとPythonでSQL入門するのでしたらsqliteが楽だと思います
標準ライブラリにあるのでpython入ってさえいれば以下のように数行コード書くだけでSQL実行まで
持っていけますよ
SQLAlchemyのようなORMを使うのでしたら後々sqliteで性能や機能的に不満が出てきた時点でDBを用意して
create_engine等のドライバ指定している実装箇所を変更すれば移行出来るのでその段階から準備してもいいかと思います
コメントありがとうございます！sqliteだとこんなに簡単なのですね。参考になります。