ウェブサイト検索

Python で SQL を使用する: SQLAlchemy と Pandas

データベースへの接続、SQL クエリの実行、データの分析と視覚化の方法に関する簡単なチュートリアル。

データ サイエンティストは、詳細なデータ分析、データの視覚化、モデリングに Python が必要です。ただし、データがリレーショナル データベースに保存されている場合は、SQL (構造化照会言語) を使用してデータを抽出して操作する必要があります。しかし、SQL と Python を統合してデータの可能性を最大限に引き出すにはどうすればよいでしょうか?

このチュートリアルでは、SQLAlchemy と Pandas を使用して SQL の能力と Python の柔軟性を組み合わせる方法を学びます。データベースに接続する方法、SQLAlchemy を使用して SQL クエリを実行する方法、Pandas を使用してデータを分析および視覚化する方法を学びます。

以下を使用して Pandas と SQLAlchemy をインストールします。

pip install pandas sqlalchemy

1. Pandas データフレームを SQL テーブルとして保存する

CSV データセットを使用して SQL テーブルを作成するには、次の手順を実行します。

  1. SQLAlchemy を使用して SQLite データベースを作成します。

  2. Pandas を使用して CSV データセットをロードします。 country_poluation データセットは、2017 年から 2023 年までの世界すべての国の大気質指数 (AQI) で構成されています。 
  3. すべての AQI 列をオブジェクトから数値に変換し、欠損値のある行を削除します。 

# Import necessary packages
import pandas as pd
import psycopg2
from sqlalchemy import create_engine
 
# creating the new db
engine = create_engine(
    "sqlite:///kdnuggets.db")
 
# read the CSV dataset
data = pd.read_csv("/work/air_pollution new.csv")

col = ['2017', '2018', '2019', '2020', '2021', '2022', '2023']

for s in col:
    data[s] = pd.to_numeric(data[s], errors='coerce')

    data = data.dropna(subset=[s])

  1. Pandas データフレームを SQL テーブルとして保存します。 「to_sql」関数にはテーブル名とエンジン オブジェクトが必要です。   

# save the dataframe as a SQLite table
data.to_sql('countries_poluation', engine, if_exists='replace')

その結果、SQLite データベースがファイル ディレクトリに保存されます。 

注: このチュートリアルでは、Python コードをシームレスに実行するために Deepnote を使用しています。 Deepnote は、あらゆるデータ サイエンス コードを迅速に実行するのに役立つ無料の AI クラウド ノートブックです。 

2. Pandas を使用した SQL テーブルのロード

SQL データベースからテーブル全体を Pandas データフレームとしてロードするには、次の手順を実行します。

  1. データベースの URL を指定して、データベースとの接続を確立します。

  2. `pd.read_sql_table` 関数を使用してテーブル全体をロードし、Pandas データフレームに変換します。この関数には、テーブル アニメ、エンジン オブジェクト、および列名が必要です。 
  3. 上位 5 行を表示します。  

import pandas as pd
import psycopg2
from sqlalchemy import create_engine
 
# establish a connection with the database
engine = create_engine("sqlite:///kdnuggets.db")
 
# read the sqlite table
table_df = pd.read_sql_table(
    "countries_poluation",
    con=engine,
    columns=['city', 'country', '2017', '2018', '2019', '2020', '2021', '2022',
       '2023']
)
 
table_df.head()

SQL テーブルがデータフレームとして正常にロードされました。つまり、Seaborn、Matplotlib、Scipy、Numpy などの一般的な Python パッケージを使用してデータ分析と視覚化を実行できるようになりました。

3. Pandas を使用した SQL クエリの実行

1 つのテーブルに制限する代わりに、「pd.read_sql」関数を使用してデータベース全体にアクセスできます。単純な SQL クエリを作成し、エンジン オブジェクトを指定するだけです。

SQL クエリは、「countries_population」テーブルの 2 つの列を表示し、「2023」列で並べ替えて、上位 5 つの結果を表示します。

# read table data using sql query
sql_df = pd.read_sql(
    "SELECT city,[2023] FROM countries_poluation ORDER BY [2023] DESC LIMIT 5",
    con=engine
)
 
print(sql_df)

世界で最も大気質の悪い都市トップ 5 に到達しました。 

         city  2023
0       Lahore  97.4
1        Hotan  95.0
2      Bhiwadi  93.3
3  Delhi (NCT)  92.7
4     Peshawar  91.9

4. Pandas での SQL クエリ結果の使用

SQL クエリの結果を使用して、さらなる分析を実行することもできます。たとえば、Pandas を使用して上位 5 つの都市の平均を計算します。 

average_air = sql_df['2023'].mean()
print(f"The average of top 5 cities: {average_air:.2f}")

出力:

The average of top 5 cities: 94.06

または、x および y 引数とプロットのタイプを指定して棒プロットを作成します。 

sql_df.plot(x="city",y="2023",kind = "barh");

結論

SQLAlchemy を Pandas で使用する可能性は無限です。 SQL クエリを使用して簡単なデータ分析を実行できますが、結果を視覚化したり、機械学習モデルをトレーニングしたりするには、モデルを Pandas データフレームに変換する必要があります。 

このチュートリアルでは、SQL データベースを Python にロードし、データ分析を実行し、視覚化を作成する方法を学習しました。このガイドを気に入っていただけた場合は、Python の組み込み sqlite3 モジュールの使用方法を詳しく説明した「Python で SQLite データベースを操作するためのガイド」も気に入っていただけるでしょう。

関連記事