【Pandas】DB→DataFrame, DataFrame→DBに変換する！

データ分析ライブラリであるPandasを使うとDBから取得したデータをDataFrameに変換したり、DataFrameをDBにinsertすることが簡単にできる。

今回はその方法について記載する。

まずは書き方から紹介！

DB→DataFrame

import sqlalchemy as sa
engine = sa.create_engine(
    sa.engine.url.URL.create(
        drivername="mysql+pymysql", # or postgresql
        username=db_user,  # e.g. "my-database-user"
        password=db_pass,  # e.g. "my-database-password"
        host=db_hostname,  # e.g. "127.0.0.1"
        port=db_port,  # e.g. 3306
        database=db_name,  # e.g. "my-database-name"
    )
)

sql_query="""
select * from testdata;
"""
df = pd.read_sql(sql=sql_query, con=engine)

DataFrame→DB

import sqlalchemy as sa
engine = sa.create_engine(
    sa.engine.url.URL.create(
        drivername="mysql+pymysql",
        username=db_user,  # e.g. "my-database-user"
        password=db_pass,  # e.g. "my-database-password"
        host=db_hostname,  # e.g. "127.0.0.1"
        port=db_port,  # e.g. 3306
        database=db_name,  # e.g. "my-database-name"
    )
)

data = """
id,title,
1,title1
2,title2
"""
# dataframe作成
df=pd.read_csv(StringIO(data))
table_name="table"
df.to_sql(table_name, con=engine, if_exists='replace')

実際に試してみる

環境構築

実際にdbサーバーとpandasを扱うためのjupyterサーバーをDockerを使って構築する。

Dockerfile

FROM jupyter/datascience-notebook
ENV TZ=Asia/Tokyo
USER root
RUN apt-get update \
  && apt-get -y install libpq-dev python-dev \
  && pip install psycopg2-binary \
  && apt-get clean

docker-compose.yml

version: "2"
services:
  jupyter:
    build: .
    tty: true
    volumes:
      - ./work:/home/jovyan/work
    ports:
      - 10000:8888
    command: start-notebook.sh --NotebookApp.token=''
  postgres:
    image: postgres:13-alpine  
    environment:
      POSTGRES_DB: dvdrental
      POSTGRES_PASSWORD: pass

上記ファイルを作成後、以下コマンドを実行することで環境構築することができる。

docker-compose up -d

実際に処理を実行してみる。

Dataframe→DB

data = """
id,title,
1,title1
2,title2
"""
# dataframe作成
import sqlalchemy
import pandas as pd
from io import StringIO

df_test=pd.read_csv(StringIO(data))

mysql_engine = sqlalchemy.create_engine(
    # Equivalent URL:
    # postgresql+pg8000://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
    sqlalchemy.engine.url.URL.create(
        drivername="mysql+pymysql",
        username="root",  # e.g. "my-database-user"
        password="root",  # e.g. "my-database-password"
        host="mysql",  # e.g. "127.0.0.1"
        port=3306,  # e.g. 5432
        database="test"  # e.g. "my-database-name"
    ),
)
psql_engine = sqlalchemy.create_engine(
    # Equivalent URL:
    # postgresql+pg8000://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
    sqlalchemy.engine.url.URL.create(
        drivername="postgresql",
        username="postgres",  # e.g. "my-database-user"
        password="pass",  # e.g. "my-database-password"
        host="postgres",  # e.g. "127.0.0.1"
        port=5432,  # e.g. 5432
        database="test"  # e.g. "my-database-name"
    ),
)
# dataframeをmysqlへinsert
df_test.to_sql('test', con=mysql_engine, if_exists='replace')
# dataframeをpostgresへinsert
df_test.to_sql('test', con=psql_engine, if_exists='replace')

実際にデータが投入されているか確認してみる。

# postgres
docker-compose postgres sh
# コンテナ内で以下を実行し確認
psql -U postgres
\c test
select * from test;
 id | title
----+--------
  1 | title1
  2 | title2
(2 rows)


# mysql
docker-compose mysql bash
# コンテナ内で実行
mysql -uroot -proot
use test
select * from test;

+------+--------+
| id   | title  |
+------+--------+
|    1 | title1 |
|    2 | title2 |
+------+--------+
2 rows in set (0.00 sec)

DB→Dataframe

import sqlalchemy
import pandas as pd

query = """
select * from test
"""

mysql_engine = sqlalchemy.create_engine(
    # Equivalent URL:
    # postgresql+pg8000://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
    sqlalchemy.engine.url.URL.create(
        drivername="mysql+pymysql",
        username="root",  # e.g. "my-database-user"
        password="root",  # e.g. "my-database-password"
        host="mysql",  # e.g. "127.0.0.1"
        port=3306,  # e.g. 5432
        database="test"  # e.g. "my-database-name"
    ),
)
psql_engine = sqlalchemy.create_engine(
    # Equivalent URL:
    # postgresql+pg8000://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
    sqlalchemy.engine.url.URL.create(
        drivername="postgresql",
        username="postgres",  # e.g. "my-database-user"
        password="pass",  # e.g. "my-database-password"
        host="postgres",  # e.g. "127.0.0.1"
        port=5432,  # e.g. 5432
        database="test"  # e.g. "my-database-name"
    ),
)
# dataframeをmysqlへinsert
pd.read_sql(sql=query,con=mysql_engine)
# dataframeをpostgresへinsert
pd.read_sql(sql=query,con=psql_engine)

まとめ

• dataframe→dbへの変換
  • pandas.DataFrame.to_sql
• dataframe→dbへの変換
  • pandas.read_sql

を利用する。