ホーム > python > pandas > Python pandasでMySQLとSQL Serverからデータ取得

Python pandasでMySQLとSQL Serverからデータ取得

使用データ

今回はMySQLとSQL Serverを使って試してみます。
MySQLは資産記録アプリ開発②～DB作成～で立てたものに新しくDBを作成し、「pandas_test_table」というテーブルを下記の様なデータ構造で作成しました。
20230106161600

テスト用データをいくつか追加しておきます。

id	datetime	memo
1	2023-01-01 00:00:00	正月
2	2023-01-02 00:00:00	帰省
3	2023-01-04 00:00:00	大発会

SQL Serverは公式サイトからダウンロード可能です。
今回は「SQL Server 2019 Developer エディション」をインストールして使用しています。
SQL Server のダウンロード | Microsoft

インストールは指示に従ってインストールするだけなので、今回手順は省略します。
また、SQL Serverを使用する際はMicrosoftがSQL Server Management Studio (SSMS)というクライアントツールを用意してくれています。GUIで操作したい場合は、こちらも合わせてインストールしておくと便利です。
MySQLと同じようにデータベース、テーブルを作成しテスト用データを追加しておきます。

事前準備

下記ライブラリをインストールしておきます。


pip install SQLAlchemy
 # MySQL用
pip install PyMySQL
 # SQL Server用
pip install pyodbc

anacondaを使っている場合は下記などでインストール可能です。
ANACONDA.ORGで検索するといくつかパッケージが見つかります。


conda install -c conda-forge sqlalchemy
conda install -c conda-forge pymysql
conda install -c conda-forge pyodbc

また、SQL Serverを使用する場合はODBC Driverも必要になりますので下記からインストールしておく必要があります。
必要に応じてダウンロードしてください。
ODBC Driver for SQL Server のダウンロード - ODBC Driver for SQL Server | Microsoft Learn

MySQLからデータ取得

pandasのread_sql_query()を使って取得します。
公式ドキュメントの通り、引数で最低限sql、conを指定すると取得することが出来ます。
今回は用意したテーブルのデータすべてを取得するクエリを実行しています。
DBへ接続するための設定とクエリは必要に応じて書き換えてください。


import pandas as pd
import sqlalchemy

if __name__ == '__main__':
    # MySQLへ接続するための設定
    user = 'root'
    password = 'root'
    host = '127.0.0.1'
    port = 3306
    database = 'pandas_test'
    url = f'mysql+pymysql://{user}:{password}@{host}:{port}/{database}'

    con = sqlalchemy.create_engine(url)
    query = 'select * from pandas_test_table'
    df = pd.read_sql_query(query, con)
    print(df)
    # 出力結果
    #    id   datetime memo
    # 0   1 2023-01-01   正月
    # 1   2 2023-01-02   帰省
    # 2   3 2023-01-04  大発会

引数index_colを指定することで任意の列をindexとして取得することが可能です。


import pandas as pd
import sqlalchemy

if __name__ == '__main__':
    # MySQLへ接続するための設定
    user = 'root'
    password = 'root'
    host = '127.0.0.1'
    port = 3306
    database = 'pandas_test'
    conn_str = f'mysql+pymysql://{user}:{password}@{host}:{port}/{database}'

    con = sqlalchemy.create_engine(conn_str)
    query = 'select * from pandas_test_table'
    df = pd.read_sql_query(query, con, index_col='id')
    print(df)
    # 出力結果
    #     datetime memo
    # id                
    # 1  2023-01-01   正月
    # 2  2023-01-02   帰省
    # 3  2023-01-04  大発会

SQL Serverからデータ取得

SQL Serverの場合も基本的にやることは同じですが、接続文字列が変わってきます。
私はWindows認証で接続しているため、下記の様な接続文字列を使用しました。
また、urllib.parseを使ってURLエンコードも行っています。


import pandas as pd
import sqlalchemy
import urllib

if __name__ == '__main__':
    servername = 'DESKTOP-PGSQEV1'
    database = 'pandas_test'
    driver = 'ODBC Driver 17 for SQL Server'
    conn_str = 'mssql+pyodbc:///?odbc_connect=' + urllib.parse.quote_plus(f'DRIVER={driver};SERVER={servername};DATABASE={database};Trusted_Connection=yes')

    con = sqlalchemy.create_engine(conn_str)
    query = 'select * from pandas_test_table'
    df = pd.read_sql_query(query, con)
    print(df)
    # 出力結果
    #    id   datetime memo
    # 0   1 2023-01-01   正月
    # 1   2 2023-01-02   帰省
    # 2   3 2023-01-04  大発会

read_sql_table()、read_sql()

pandasでは他にもread_sql_table()という関数も用意されており、こちらを使用することでも同様にデータを取得することができます。
この関数ではqueryの代わりにテーブルを指定することでデータを取得することが出来るため、クエリが書けなくてもデータ取得可能です。


import pandas as pd
import sqlalchemy
import urllib

if __name__ == '__main__':
    servername = 'DESKTOP-PGSQEV1'
    database = 'pandas_test'
    driver = 'ODBC Driver 17 for SQL Server'
    conn_str = 'mssql+pyodbc:///?odbc_connect=' + urllib.parse.quote_plus(f'DRIVER={driver};SERVER={servername};DATABASE={database};Trusted_Connection=yes')

    con = sqlalchemy.create_engine(conn_str)
    df = pd.read_sql_table('pandas_test_table', con) # 第一引数でテーブル名指定
    print(df)
    # 出力結果
    #    id   datetime memo
    # 0   1 2023-01-01   正月
    # 1   2 2023-01-02   帰省
    # 2   3 2023-01-04  大発会

また、read_sql()という関数も用意されていますが、こちらは公式ドキュメントにも記載のある通りread_sql_table()とread_sql_query()のラッパー関数となっています。
第一引数のsqlにクエリまたはテーブル名を指定することでデータを取得することが出来ます。
例えば、下記の2つは同じ結果を取得することが出来ます。


import pandas as pd
import sqlalchemy
import urllib

if __name__ == '__main__':
    servername = 'DESKTOP-PGSQEV1'
    database = 'pandas_test'
    driver = 'ODBC Driver 17 for SQL Server'
    conn_str = 'mssql+pyodbc:///?odbc_connect=' + urllib.parse.quote_plus(f'DRIVER={driver};SERVER={servername};DATABASE={database};Trusted_Connection=yes')

    con = sqlalchemy.create_engine(conn_str)
    query = 'select * from pandas_test_table'
    df_query = pd.read_sql(query, con) # 第一引数でクエリ指定
    print(df_query)
    # 出力結果
    #    id   datetime memo
    # 0   1 2023-01-01   正月
    # 1   2 2023-01-02   帰省
    # 2   3 2023-01-04  大発会

    df_table = pd.read_sql('pandas_test_table', con) # 第一引数でテーブル名指定
    print(df_table)
    # 出力結果
    #    id   datetime memo
    # 0   1 2023-01-01   正月
    # 1   2 2023-01-02   帰省
    # 2   3 2023-01-04  大発会