Python環境でSQLを実行してSELECT文でデータ集計する方法｜SQLiteの接続方法解説付

2022年9月10日

当ページには広告が含まれています。

こんにちは、DXCEL WAVEの運営者(@dxcelwave)です！

こんな方におすすめ！

Python環境下でSQLを実行し、SQLite内のデータを抽出したい
SQLのSELECT文を用いてデータを集計する方法が知りたい

データベースとは

データベース（database, DB）とは、データの検索および蓄積が容易にできるよう整理された情報の集まりを指します。

データベースは、「データを格納するファイル」と「データの格納や検索の処理を実行するプログラム」から構成されます。このプログラムはデータベース管理システム（Database Management System, DBMS）と呼びます。

SQLとは

データベースにあるテーブルデータの読み書きは、SQL（Structured Query Language）と呼ばれるデータベース操作の専用言語で書かれた命令をクライアントからRDBMSに送ることで実現できます。

データベースおよびSQLについて詳しく知りたい方はこちらの記事をご覧下さい。

SQLiteとは

近年ではオープンソースのリレーショナルデータベース管理システム（RDBMS）が広く用いられており、SQLiteもその中の１つとして位置付けられます。

SQLiteは開発用ライブラリとして提供されているアプリケーション内に組み込み可能なRDBMSであり、Pythonに標準で付属しています。

【参考】Python環境下でSQLを実行する際に参考となる書籍

Python環境下でSQLを実行し、RDBMSからデータを抽出する方法について詳しく学習したい方向けに、おすすめの書籍を2点ご紹介します。

Pythonによるデータベース操作の基礎を習得したい方向け

実践的なプログラミングスキルを習得したい方向け

その他データベーススペシャリストやソフトウェア開発も見据えた方向けにも、厳選したおすすめ書籍・動画教材を紹介しています。詳しくはこちらの記事をご覧ください。

今回の解説範囲

本記事ではPython環境下でSQL（SELECT文）を実行し、データを集計する方法について解説します。本記事を通じて具体的に下記のようなことができるようになります。

COUNT｜レコードの個数集計
AVG｜特定カラムの平均値算出
MAX｜特定カラムの最大値算出
MIN｜特定カラムの最小値算出
SUM｜特定カラムの合計値算出
GROUP BYメソッドを用いたデータ集計

また、今回データ抽出先のデータベースとしてSQLiteを利用します。

Python環境とSQLiteをベースとし、SQLのSELECT基本操作を学習したい方はこちらを参照ください。

【事前準備】Python環境でSQLを実行｜SQLiteへの接続方法

まずはじめに、Python環境下でSQLを実行し、SQLiteを操作するための環境を構築します。

SQLiteのソフトウェアをダウンロード

SQLite形式のデータを閲覧・操作するのに便利です。

DB Browser for SQLiteのリンクにアクセスし、お使いのOSに応じたバージョンを選択し、ダウンロードしましょう。

サンプルデータのダウンロード

本記事ではSQLiteにアクセスし、上図のER図に示すようなサンプルデータを例に用いてデータ集計を行います。

SQLite Sakila Sample DatabaseのDownloadをクリックしましょう。Downloadしたフォルダ内のsqlite-sakila.dbファイルを今回利用します。

フォルダ構成

Pythonスクリプトファイルと前述でダウンロードしたsqlite-sakila.dbは同じディレクトリに配置しましょう。

フォルダ構成
  |
   --- Pythonスクリプト.py
  |
   --- sqlite-sakila.db

SQL操作を実現するためのPythonライブラリインストール

PandasおよびSQLAlchemyというPythonライブラリを今回用います。以下それぞれインストールしましょう。

Pandas

pip install pandas

SQLAlchemy

データベースとPandasを接続するために、SQLAlchemyが必要です。

pip install sqlalchemy

【SQL・Python実践】SELECT文を用いたデータ集計

それでは実際にPython環境下でSQLを実行していきます。データ集計には主にSELECT文を用いて記述します。

今回はsql_operation()という関数にSQLの命令文を引数として渡すことで、テーブルからデータを参照することとします。下記のコードを事前に実行しておきましょう。

import sqlite3
import pandas as pd

# データベース接続
db_name     = "sqlite-sakila.db"
connection  = sqlite3.connect(db_name)

# SQL分を引数に渡すと、Pandas.DataFrameを返す関数
def sql_operation(sql_query):
    # SQL文とDBのConnectionをもとにDataFrame作成
    df = pd.read_sql(sql_query, connection)
    # 出力
    return df

Count｜レコード集計

選択したカラムのレコード数を取得したい場合はCOUNTメソッドを用いて、下記のように記述します。

# SQL文
query = ''' 
            SELECT COUNT(customer_id)
            FROM customer
        '''

# 関数実行
sql_operation(query)

# 出力イメージ
#    COUNT(customer_id)
#                   599

COUNTメソッドを用いた集計の際は、WHERE句による絞込条件も合わせて記述することが多いです。以下store_idという絞込条件を追加した例を示します。

# SQL文
query = ''' 
            SELECT COUNT(customer_id)
            FROM customer
            WHERE store_id = 1
        '''

# 関数実行
sql_operation(query)

# 出力イメージ
#    COUNT(customer_id)
#                   326

AVG｜平均値算出

選択したカラムから値の平均値を求める際は、AVGメソッドを用いて下記のように記載します。

# SQL文
query = ''' 
            SELECT AVG(amount)
            FROM payment
        '''

# 関数実行
sql_operation(query)

# 出力イメージ
#    AVG(amount)
#       4.200667

MAX｜最大値算出

選択したカラムから値の最大値を求める際は、MAXメソッドを用いて下記のように記載します。

# SQL文
query = ''' 
            SELECT MAX(amount)
            FROM payment
        '''

# 関数実行
sql_operation(query)

# 出力イメージ
#    MAX(amount)
#          11.99

MIN｜最小値算出

選択したカラムから値の最小値を求める際は、MINメソッドを用いて下記のように記載します。

# SQL文
query = ''' 
            SELECT MIN(amount)
            FROM payment
        '''

# 関数実行
sql_operation(query)

# 出力イメージ
#    MIN(amount)
#              0

SUM｜合計算出

選択したカラムから値の合計値を求める際は、SUMメソッドを用いて下記のように記載します。

# SQL文
query = ''' 
            SELECT SUM(amount)
            FROM payment
        '''

# 関数実行
sql_operation(query)

# 出力イメージ
#    SUM(amount)
#       67416.51

GROUP BYを用いたデータ集計

最後にデータをまとめる際に有効なGROUP BYメソッドを組み合わせてデータを集計します。

# SQL文
query = ''' 
            SELECT store_id, COUNT(customer_id) 
            FROM customer                           -- テーブル名を指定
            GROUP BY store_id                       -- 集計方法
        '''

# 関数実行
sql_operation(query)

# 出力イメージ
#    store_id  COUNT(customer_id)
# 0         1                 326
# 1         2                 273

上記の例はstore_id別にレコード数を集計した結果となります。

データとしてもう少し見やすくするためには、以下のように名称を変更すると良いかもしれませんね。

# SQL文
query = ''' 
            SELECT store_id           AS 'ストア番号',
                   COUNT(customer_id) AS '顧客数' 
            FROM customer                             -- テーブル名を指定
            GROUP BY store_id                         -- 集計方法
        '''

# 関数実行
sql_operation(query)

# 出力イメージ
#    ストア番号  顧客数
# 0        1     326
# 1        2     273