SQLAlchemy から Pandas にデータを読み込む

Pandas で SQL からデータを読み込むにはどうすれば良いだろうか? [pandas.read_sql_query](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html) を読むと、どうやら SQL 文をそのまま書く方法と、SQLAlchemy という OR マッパを使う方法と二つがあったので、両方試してみた。例えばこんな感じのテーブルがある時:

```sql
CREATE TABLE EventData (
    deviceID varchar(64),
    timestamp int(10) unsigned,
    latitude double,
    longitude double,
    primary key (deviceID, timestamp)
);
```

deviceID あたりのレコード数を知りたいとする。まず色々準備。

```python
import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, Column, String, Float, Integer
from sqlalchemy.sql import select
from sqlalchemy import func

input_db_url = 'mysql+mysqlconnector://xxx'
engine = create_engine(input_db_url)
```

## SQL 文をそのまま使う

SQL 文をそのまま使う例は簡単。

```python
pd.read_sql_query(sql="SELECT deviceID, count(*) as rows from EventData GROUP BY deviceID", con=engine)
```

## SQLAlchemy を使う

read_sql_query の引数 sql には [SQLAlchemy selectable](https://docs.sqlalchemy.org/en/latest/core/selectable.html) というのが使える。これは SQL を直接文字列として記述する代わりに Python オブジェクトを組み合わせてクエリを表現するという物だ。クォート漏れを気にしなくて良いので、動的にクエリを生成する時は便利。多少冗長だが以下のように使う。

```python
metadata = MetaData()
event_data = Table('EventData', metadata, autoload=True, autoload_with=engine)
device_id = event_data.c.deviceID
pd.read_sql_query(sql=select([device_id, func.count('*').label('rows')]).group_by(device_id).limit(10), con=engine)
```

SQLAlchemy では Table や Column オブジェクトを組み合わせてクエリを構築する。普通は明示的にスキーマを定義するが、ここではサボって [reflection](https://docs.sqlalchemy.org/en/latest/core/reflection.html) 機能を使いテーブル定義をデータベースから読んでいる。

[MetaData](https://docs.sqlalchemy.org/en/latest/core/metadata.html) は engine と関連付けるとデータベース情報を保持する。ここでは、Table のコンストラクタで autoload を指定しているのでテーブル定義が自動的に読み込まれる。

```python
> metadata.tables

immutabledict({'EventData': Table('EventData', MetaData(bind=None), Column('deviceID', VARCHAR(length=64), table=<EventData>, primary_key=True, nullable=False), Column('timestamp', INTEGER(display_width=10, unsigned=True), table=<EventData>, primary_key=True, nullable=False), Column('latitude', DOUBLE(asdecimal=True), table=<EventData>), Column('longitude', DOUBLE(asdecimal=True), table=<EventData>), schema=None)})

> event_data

Table('EventData', MetaData(bind=None), Column('deviceID', VARCHAR(length=64), table=<EventData>, primary_key=True, nullable=False), Column('timestamp', INTEGER(display_width=10, unsigned=True), table=<EventData>, primary_key=True, nullable=False), Column('latitude', DOUBLE(asdecimal=True), table=<EventData>), Column('longitude', DOUBLE(asdecimal=True), table=<EventData>), schema=None)
```

テーブル内のカラムには columns (省略形 c) でアクセス出来る。カラム名がそのままプロパティになっている。

```python
> event_data.c.deviceID

Column('deviceID', VARCHAR(length=64), table=<EventData>, primary_key=True, nullable=False)
```

あとは [SQL Expression Language Tutorial](https://docs.sqlalchemy.org/en/latest/core/tutorial.html) などを参考にしてカラムを組み合わせてクエリを作る。str() で何となく出来上がるクエリが分かる。

```python
str(select([device_id, func.count('*').label('rows')]).group_by(device_id).limit(10))

'SELECT "EventData"."deviceID", count(:count_1) AS rows \nFROM "EventData" GROUP BY "EventData"."deviceID"\n LIMIT :param_1'
```

## SQLAlchemy でテーブルを Python class として扱う

Pandas だとあまり利点が無い気がするが、本来 SQLAlchemy は OR マッパとして使う。つまり、SQL データベースをあたかも Python オブジェクトのコレクションのように扱う。折角なので OR マッパとしての SQLAlchemy についても少し調べた。というか、順番としては頭からドキュメントを呼んでいくと OR マッパの長い説明の後ようやく selectable オブジェクトだけを使う方法を見つけた。

[Object Relational Tutorial](https://docs.sqlalchemy.org/en/latest/orm/tutorial.html) によると、テーブルを Python クラスとして表現するには declarative_base() で作ったベースクラスを使う。

```python
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class EventData(Base):
    __tablename__ = 'EventData'
    device_id = Column('deviceId', String, primary_key=True)
    timestamp = Column(Integer, primary_key=True)
    latitude = Column(Float)
    longitude = Column(Float)

    def __repr__(self):
        return 'EventData(%s, %s, %s, %s)' % (self.device_id, self.timestamp, self.latitude, self.longitude)
```

これでクラスの `__table__` を覗いてみると Table 定義が出来上がっている。

```python
EventData.__table__

Table('EventData', MetaData(bind=None), Column('deviceId', String(), table=<EventData>, primary_key=True, nullable=False), Column('timestamp', Integer(), table=<EventData>, primary_key=True, nullable=False), Column('latitude', Float(), table=<EventData>), Column('longitude', Float(), table=<EventData>), schema=None)
```

出来上がったクラスを使って実際に SQL を発行するには [Session](https://docs.sqlalchemy.org/en/latest/orm/session.html) というものを使う。

```python
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

for each in session.query(EventData)[0:10]:
    print(each)

session.close()

EventData(81b2d6, 1522711327, 35.6349139, 139.7683735)
EventData(81b2d6, 1522711332, 35.6352536, 139.7673153)
EventData(81b2d6, 1522711342, 35.6358887, 139.7653534)
EventData(81b2d6, 1522711347, 35.6361897, 139.7643313) ...
```

str() でクエリの内容を見ることも出来る。

```python
> str(session.query(EventData))

'SELECT `EventData`.`deviceId` AS `EventData_deviceId`, `EventData`.timestamp AS `EventData_timestamp`, `EventData`.latitude AS `EventData_latitude`, `EventData`.longitude AS `EventData_longitude` \nFROM `EventData`'
```

## 宿題

さて、ここで不思議なのは、session.query で作った Query オブジェクトは read_sql_query の時に select で使った Select オブジェクトとは別物だという事だ、Select オブジェクトを使うには、session ではなく [Connection](https://docs.sqlalchemy.org/en/latest/core/connections.html) を使う。

```python
conn = engine.connect()
query = select([EventData.device_id, EventData.timestamp, EventData.latitude, EventData.longitude]).limit(10)

for each in conn.execute(query.limit(10)):
    print(each)
```

つまり、

* [OR マッパを使う時](https://docs.sqlalchemy.org/en/latest/orm/tutorial.html) は Session
* [Select オブジェクトを使う時](https://docs.sqlalchemy.org/en/latest/core/tutorial.html) は Connection

という互換性の無い二つのやり方がある気がする。この二つの関係については後で調べる。

## 回答

[SQLAlchemy: engine, connection and session difference](https://stackoverflow.com/questions/34322471/sqlalchemy-engine-connection-and-session-difference) によれば、Session は OR マッパで SQL を自動生成する場合に使い、Connection は SQL を明示的に生成する場合に使うとある。ちなみに、Session にも execute 関数があるため、Select オブジェクトはどちらにも使える。