PythonでのSQLite操作とSQL・SELECT文によるデータ抽出方法解説｜データベース・プログラミング入門

2022年9月10日

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

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

こんな方におすすめ！

Python環境下でSQLを実行し、SQLiteを操作する方法が知りたい
Pythonを用いてSQLによるデータ抽出（SELECT）方法が知りたい

データベースとは

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

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

SQLとは

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

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

SQLiteとは

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

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

【参考】データベース・SQLの学習に参考となる書籍

Python環境下でのSQL実行およびRDBMSの操作方法について詳しく学習したい方向けにおすすめの書籍を2点紹介します。

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

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

どちらも非常に分かりやすい優良書籍です。その他データベーススペシャリストやソフトウェア開発も見据えた方向けのおすすめ書籍・動画教材はこちらの記事で紹介しています。

今回の解説範囲

本記事ではPython環境下でSQLを実行し、SQLiteを操作する方法について解説します。またSQLの命令文は、利用頻度が最も高いデータ抽出(SELECT)の基本操作に絞って解説します。

【事前準備】SQLite・データベースの環境構築

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

SQLiteのブラウザをダウンロード

SQLite形式のデータをブラウザ上で閲覧・操作するためのソフトウェアをインストールします。

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

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

【引用：SQLite Sakila Sample Database】https://github.com/jOOQ/sakila

本記事では上図のER図に示すようなサンプルデータを用いてSQLiteの操作方法を解説します。

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

フォルダ構成

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

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

SQL操作に便利なPythonライブラリインストール

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

Pandas

pip install pandas

SQLAlchemy

データベースとPandasを接続するために必要なライブラリです。

pip install sqlalchemy

【実践】Python環境でSQLiteを操作｜SQL・SELECT編

それでは実際にPython環境でSQLiteと接続し、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

SELECT FROM｜データの参照

データ参照の基本となるSELECT文を用いてPythonコードを実行します。

データベースファイル内にあるcustomerテーブルにアクセスし、データ抽出する例を下記に示します。

テーブルにあるデータを全て参照

# SQL文
query = ''' 
            SELECT *         -- 全ての列を指定
            FROM customer    -- テーブルを指定
        '''

# 関数実行
sql_operation(query)

# 出力結果イメージ
#      customer_id  store_id first_name  last_name  ・・・
# 0              1         1       MARY      SMITH  ・・・ 
# 1              2         1   PATRICIA    JOHNSON  ・・・ 
# 2              3         1      LINDA   WILLIAMS  ・・・
# 3              4         2    BARBARA      JONES  ・・・
# 4              5         1  ELIZABETH      BROWN  ・・・

列を指定してデータ参照

# SQL文
query = ''' 
            SELECT first_name,last_name,email    -- 列を指定
            FROM customer                        -- テーブルを指定
        '''

# 関数実行
sql_operation(query)

WHERE｜データの参照結果を絞込み

SELECT文の最後に、WHERE節を付けると、検索の絞込条件を指定できます。

数値条件を用いたデータ絞込み

# SQL文
query = ''' 
            SELECT *            -- 列を指定
            FROM  customer      -- テーブルを指定
            WHERE store_id=1    -- 絞り込み条件を指定
        '''

# 関数実行
sql_operation(query)

以下の演算子を用いることで、詳細な絞込条件の指定が可能です。

=	左辺と右辺が等しい
<	左辺は右辺より小さい
>	左辺は右辺より大きい
<=	左辺は右辺より小さいか等しい
>=	左辺は右辺より大きいか等しい
<>	左辺と右辺は等しくない

文字列条件を用いたデータ絞込み

文字列を指定する場合、シングルクォーテーション「’ ‘」で囲んで条件指定します。

# SQL文
query = ''' 
            SELECT *                    -- 列を指定
            FROM  customer              -- テーブルを指定
            WHERE last_name = 'SMITH'   -- 絞り込み条件を指定
        '''

# 関数実行
sql_operation(query)

BETWEEN｜データ取得範囲の指定

BETWEEN演算子を用いると、データ取得範囲が指定できます。

# SQL文
query = ''' 
            SELECT *                              -- 列を指定
            FROM  customer                        -- テーブルを指定
            WHERE customer_id BETWEEN 10 AND 20   -- 絞り込み条件を指定
        '''

# 関数実行
sql_operation(query)

IN/NOT IN｜データ取得範囲の指定

IN演算子は、指定した値を含むデータを取得する際に有効な演算子です。一方で、NOT IN演算子は、指定した値以外のデータを取得したい場合に有効です。

IN演算子：IN（値1,値2,値3・・・)

# SQL文
query = ''' 
            SELECT *                           -- 列を指定
            FROM  customer                     -- テーブルを指定
            WHERE customer_id IN (1,3,5,7)     -- 絞り込み条件を指定
        '''

# 関数実行
sql_operation(query)

NOT IN演算子：NOT IN(値1,値2,値3・・・）

# SQL文
query = ''' 
            SELECT *                               -- 列を指定
            FROM  customer                         -- テーブルを指定
            WHERE customer_id NOT IN (1,3,5,7)     -- 絞り込み条件を指定
        '''

# 関数実行
sql_operation(query)

IS NULL/IS NOT NULL｜NULL判定

IS NULLは、列の値がNULLであることを判定するための演算子であり、IN NOT NULL演算子はNULLでないことを判定する演算子です。

IS NULL｜NULLであることを判定

# SQL文
query = ''' 
            SELECT *                 -- 列を指定
            FROM  customer           -- テーブルを指定
            WHERE email IS NULL      -- 絞り込み条件を指定
        '''

# 関数実行
sql_operation(query)

IS NOT NULL｜NULLでないことを判定

# SQL文
query = ''' 
            SELECT *                     -- 列を指定
            FROM  customer               -- テーブルを指定
            WHERE email IS NOT NULL      -- 絞り込み条件を指定
        '''

# 関数実行
sql_operation(query)

LIKE｜データのパターンマッチング

文字列があるパターンに合致するか判定することをパターンマッチングといいます。SQLでパターンマッチングを実行する場合、LIKE演算子を用います。

# SQL文
query = ''' 
            SELECT *                        -- 列を指定
            FROM  customer                  -- テーブルを指定
            WHERE first_name LIKE 'B%'      -- 絞り込み条件を指定
        '''

# 関数実行
sql_operation(query)

パターン文字列は、ある文字列をパターン化した文字と、通常文字の組み合わせとして表現できます。

例えば、「B」から名前が始まる顧客名を全て抽出したい場合を考えます。その場合、上記のように「B%」と記述してコードを実行すると、Bから名前が始まる顧客データを全て抽出できます。

パターン文字	意味
%	任意の0文字以上の文字列
–	任意の1文字

AND｜論理演算

複数の条件式を用いて目的のデータを抽出したい場合、論理演算子（AND）を用います。

単一のAND条件を指定する場合

# SQL文
query = ''' 
            SELECT *                 -- 列を指定
            FROM  customer           -- テーブルを指定
            WHERE store_id = 1       -- 絞り込み条件を指定
            AND   customer_id > 10   -- 絞り込み条件（AND）を追加指定
        '''

# 関数実行
sql_operation(query)

複数のAND条件を指定する場合

# SQL文
query = ''' 
            SELECT *                                -- 列を指定
            FROM  customer                          -- テーブルを指定
            WHERE store_id = 1                      -- 絞り込み条件を指定
            AND   (customer_id<10 AND store_id=1)   -- 絞り込み条件（AND）を追加指定
        '''

# 関数実行
sql_operation(query)

OR｜論理演算

OR演算子を用いると、ORで繋いだどちらか一方の条件が満たされた場合、その対象データが取得できます。

単一のOR条件を指定する場合

# SQL文
query = ''' 
            SELECT *                 -- 列を指定
            FROM  customer           -- テーブルを指定
            WHERE store_id = 1       -- 絞り込み条件を指定
            OR   customer_id > 10    -- 絞り込み条件（OR）を追加指定
        '''

# 関数実行
sql_operation(query)

複数のOR条件を指定する場合

# SQL文
query = ''' 
            SELECT *                                      -- 列を指定
            FROM  customer                                -- テーブルを指定
            WHERE store_id = 1                            -- 絞り込み条件を指定
            AND   (customer_id > 10 OR customer_id <=2)   -- 絞り込み条件（AND）を追加指定
        '''

# 関数実行
sql_operation(query)

DISTINCT｜重複レコードの除外

内容が重複するレコードを除外した上でデータ抽出したい場合、DISTINCT演算子が有効です。

# SQL文
query = ''' 
            SELECT DISTINCT(store_id)     -- 列を指定
            FROM customer                 -- テーブルを指定
        '''

# 関数実行
sql_operation(query)

ORDER BY｜データの並び替え

SELECT文の最後にORDER BY句を加えると、指定した列の値を基準に、参照結果の並び替えができます。

# SQL文
query = ''' 
            SELECT first_name, last_name     -- 列を指定
            FROM customer                    -- テーブルを指定
            ORDER BY first_name ASC          -- 並び順を指定
        '''

# 関数実行
sql_operation(query)

並び順は、昇順にする場合ASC、降順にする場合DESCと指定します。（何も指定しない場合のデフォルトはASCです）

LIMIT：レコードを限定して取得

抽出対象全てのレコードではなく、指定した数のみのレコードを抽出したい場合、LIMIT句が有効です。

# SQL文
query = ''' 
            SELECT first_name, last_name     -- 列を指定
            FROM customer                    -- テーブルを指定
            LIMIT 5                          -- 5レコードを取得
        '''

# 関数実行
sql_operation(query)