時々、Excelで読み込めない形式のデータや、独自形式のデータをExcelのシートに貼り付けたい場合がある。Pythonの豊富なライブラリを使えば様々なデータ形式を手軽に読み込める。今回は、SQLite形式のデータをPythonで読み込んでExcelに貼り付けてみよう。

  • SQLiteのデータをPythonで読み込んでExcelに挿入したところ

    SQLiteのデータをPythonで読み込んでExcelに挿入したところ

Excelに貼り付けたいけどExcelだけでは読めないデータもある

Excelのデータインポート機能は非常に高機能だ。CSV/TSVなど汎用テキストデータだけでなく、Webでよく使われるXMLデータからJSONデータのインポートにまで対応している。

しかし、時々、Excelで読み込めない形式のデータや、独自形式のデータをExcelのシートに貼り付けたい場合がある。Excelで直接読めなくても別のアプリを使ってクリップボード経由でExcelに貼り付けることができる場合もある。

もしそれでも駄目ならPythonで読み込んで差し込むことができる。Pythonの豊富なライブラリを使えば様々なデータ形式を手軽に読み込める。今回は、Excelで読めないデータファイルの例として、SQLite形式を取り上げる。SQLiteのデータファイルからPythonを使って任意のデータを取り出しExcelに貼り付けるプログラムを紹介する。

SQLiteとは何か

なお、今回変換元のデータ形式であるSQLiteについて調べてみよう。SQLiteというのは、パブリックドメインの軽量組み込みの小型データベースだ。まず、ライセンスが自由であること、そして、関係データベース(RDBMS)の基本機能を備えておりSQLを利用してデータを操作できるので使い勝手が良い。そのため気軽にアプリに組み込んで使われている。

有名なところではChromeやFirefoxなどのWebブラウザにまで組み込まれている。このように広範に利用されているため、SQLiteからデータを気軽に取り出して使いたい場面も多い。今回は、SQLiteからデータを取り出してExcelに貼り付けるプログラムを紹介する。なお、ちょうど筆者が公開しているフリーの英和辞典データもSQLite形式で提供しているので、これを例として使ってみよう。

SQLiteの中のデータをExcelに貼り付けよう

なお、一般的には「(ファイルの形式) + python」のキーワードを調べると、Pythonを使ってそのファイル形式を読む方法が見つかる。かなりマイナーなデータ形式でも、PyPI(Python Package Index)など有志が公開しているPythonパッケージのリポジトリに読み込みライブラリが登録されている場合も多い。

しかし、今回目的とするSQLiteは、Pythonの標準ライブラリに含まれており、何もインストールすることなく扱うことができる。逆に、PythonでExcelを扱うためには、Excelファイルを操作する「openpyxl」をインストールする必要がある。

コマンドライン(WindowsならPowerShell、macOSならターミナル.app)を起動して、以下のコマンドを実行し、openpyxlをインストールしよう。(WindowsでAnacondaを使っている場合は、Windowsメニューから[Anaconda > Anaconda Prompt]を実行し以下のコマンドを実行しよう。)

# openpyxlをインストールするコマンド
pip install openpyxl

また、今回のサンプルSQLiteデータとして、こちらから『英和辞書データ (ejdict-hand)」のSQLite版を選んでダウンロードしよう。ZIPファイルを解凍し、ejdict.sqlite3をプログラムと同じフォルダにコピーしよう。

今回は、SQLite形式のデータベースからデータを取り出して、データベース内の単語を最大300件抽出してExcelに貼り付け「dict.xlsx」というファイルに保存するプログラムを作ってみよう。

# PythonでSQLiteを扱うのに必要な宣言 --- (*1)
import sqlite3
# Excelファイルを作成するのに必要な宣言
import openpyxl as excel

# データベースのテーブルitemsからデータを最大300件取り出す --- (*2)
sql = 'SELECT * FROM items LIMIT 300'

# データベースに接続してデータを取り出す --- (*3)
conn = sqlite3.connect("ejdict.sqlite3")
c = conn.cursor()
rows = c.execute(sql)

# 取り出したデータをExcelに貼り付ける --- (*4)
book = excel.Workbook() # 新規ブック作成
sheet = book.worksheets[0] # 先頭のシート
for i, n in enumerate(rows): # --- (*4a)
   sheet.cell(i+1, 1).value = n[1] # 単語
   sheet.cell(i+1, 2).value = n[2] # 意味

# Excelファイルに保存 --- (*5)
book.save("dict.xlsx")
print("ok")

上記のプログラムを「sqlite_to_excel.py」という名前で保存しよう。そして、コマンドラインで「python sqlite_to_excel.py」とコマンドを打つか、IDLEなどのツールでプログラムを実行してみよう。

すると「dict.xlsx」というファイルが作成され、Excelで開くと次のように表示される。

  • ExcelでSQLiteから取り出したデータを開いたところ

    ExcelでSQLiteから取り出したデータを開いたところ

プログラムを確認してみよう。(＊1)の部分ではsqlite3とopenpyxlのモジュールを読み込んでいる。Pythonでは各ライブラリを読み込みプログラム内で使う場合に、このようにimportで宣言が必要となる。

(＊2)ではSQLiteデータベースのテーブルitemsからデータを300件取り出すというSQLを記述している。SQLが分かれば問題ないだろうが、SQLに詳しくなくても、文字列のitemsの部分を任意のテーブル名に変えればデータを一気に取り出すことができる。テーブル名の確認方法については後ほど紹介する。

(＊3)の部分ではSQLiteのデータベースを開いて(＊2)で用意したSQLを実行する。これでデータベースを読み込む準備は整った。

(＊4)の部分では取り出したデータをExcelに貼り付けている。openpyxlを使うと既存のExcelファイルを読み込むこともできるし、ここでやっているように新規ブックを作成しそこに貼り付けることもできる。

(＊4a)の部分はデータベースからデータを一つ読み込むごとにExcelワークシートに読み出したデータを差し込んでいる。ここは『sheet.cell(何行目, 何列目).value = 値』のように指定するとセルに値を設定できる。

そして、最後(＊5)の部分でExcelファイルを保存する。

SQLiteでデータベースの構造を調べるコマンド

ところで、SQLiteファイルの中がどのような構造になっているのかを調べたいときは、SQLiteのコマンドラインツールを使って確認することができる。SQLiteのWebサイトでは、SQLiteに関するライブラリやコマンドラインツールが配布されている。[Download]のリンクをクリックし、OSごとのツールを取得しよう。

原稿執筆時点の最新版3.34.0のWindowsはこちらからダウンロードできる。(macOS版はこちら)

そして、ZIPファイルを解凍したら、中に入っているsqlite3.exeとSQLiteのファイルを同じフォルダに配置しよう。なお、初回実行時は、以下のような警告が出るので、[詳細情報]をクリックし続いて[実行]のボタンをクリックしよう。

  • ExcelでSQLiteから取り出したデータを開いたところ

    ExcelでSQLiteから取り出したデータを開いたところ

それからPowerShellなどのコマンドラインを開き「.\sqlite3 (SQLiteファイル名)」を実行しよう。すると、コマンドラインからSQLiteのファイルを操作できるようになる。

そして、「.scheme」とコマンドを入力すると、データベースの構造が一覧表示される。

  • 「.schema」と入力するとデータベースの構造が表示される

    「.schema」と入力するとデータベースの構造が表示される

この構造を見ると、このデータベースには「items」というテーブルがあることが分かる。このようにしてテーブル名が分かったら、先ほどのプログラムのSQL文の「items」の部分を任意のテーブル名に書き換えることができるだろう。

sqlite3のコマンドを終了するには、「.quit」とタイプするか、PowerShellのウィンドウを直接閉じれば良い。

なお、SQLiteについては、以前の連載31回目 でも紹介しているの参考になるだろう。

まとめ

以上、今回はSQLiteのデータベースを読み込んで、Excelに貼り付ける方法を紹介した。SQLiteはデータベース(RDBMS)でありSQLを利用して操作する必要があるが、Pythonの標準ライブラリで操作できる。また、openpyxlモジュールを使うなら、読み出したデータをExcelに貼り付けるのも難しくない。このようにして、Pythonのいろいろなモジュールを組み合わせてデータの変換ツールを作ることもできるだろう。

自由型プログラマー。くじらはんどにて、プログラミングの楽しさを伝える活動をしている。代表作に、日本語プログラミング言語「なでしこ」 、テキスト音楽「サクラ」など。2001年オンラインソフト大賞入賞、2004年度未踏ユース スーパークリエータ認定、2010年 OSS貢献者章受賞。技術書も多く執筆している。直近では、「シゴトがはかどる Python自動処理の教科書(マイナビ出版)」「すぐに使える!業務で実践できる! PythonによるAI・機械学習・深層学習アプリのつくり方 TensorFlow2対応(ソシム)」「マンガでざっくり学ぶPython(マイナビ出版)」など。