Python MySQLのテーブルから取得したデータのリスト化

Question

Python初心者に力をお貸し下さい。

現在以下のようなMySQL(RDS)のテーブルからデータを取得し、リスト形式にしたいと思っております。
色々処理を考えていましたが、うまいやり方が浮かばず良い方法があればお教えください。

【環境】
Python3.7.6
EC2からRDSへpymysqlでデータを取りに行きます

Table:START_END_ITEM

Start_datetime	End_datetime	Item
2021/3/1 12:00:00	2021/3/1 14:00:00	Apple
2021/3/1 16:00:00	2021/3/1 17:00:00	Peach
2021/3/1 19:00:00	2021/3/1 22:00:00	Orange

【欲しい形】
[2021/3/1 14:00:00,2021/3/1 16:00:00,Apple]
[2021/3/1 17:00:00,2021/3/1 19:00:00,Peach]

End_datetimeから次のStart_datetimeになるまでEnd_datetimeのItemを持たせたい。

Comments

[ppaul]

「MySQLのテーブルからデータを取得し」というのはできているのですか？

[begginer_pygo]

ご質問いただきありがとうございます。
以下のようなシンプルなクエリで取得してから、欲しい形に成型出来ないかと考えていました。
もしクエリを投げる時点で欲しい形でとれる方法などがあればご教示ください

Select * from START_END_TIME where Start_datetime >= 2021/3/1 00:00:00

Answer 1

「うまいやり方」というのは難しいですね。
データの抽出処理は基本的にRDBMS側に極力移譲したいところですが、一方でRDBMSは一般的には、各々のレコードにアクセスをする機能は弱いという特徴があるように思います。
saziさんやhoshi-takanoriの回答のとおり、SQLでなんとかする方法で今後の保守もいけそうならそれでもよいと思います。
RDBMS側が弱いと言われている各々のレコードにアクセスをする処理をPython側でやるなら、以下のような実装でどうでしょうか。

import pymysql
# MySQLに接続する
connection = pymysql.connect(host='localhost',
                            user='ユーザー名',
                            password='パスワード',
                            db='データベース名',
                            charset='utf8',
                            cursorclass=pymysql.cursors.DictCursor)
# SQLを操作する
with connection.cursor() as cursor:
    #「my_table」から「tw_id」が重複を省いた仮テーブル「my_table_temp」を作成する
    sql = "select * from START_END_ITEM where Start_datetime >= '2021/3/1 00:00:00' order by Start_datetime;"
    cursor.execute(sql)
    records = cursor.fetchall()

    # 「欲しい形」のリストを作成
    new_records = [[records[idx]['End_datetime'], records[idx+1]['Start_datetime'], records[idx]['Item']] for idx in range(len(records)-1)]

# MySQLから切断する
connection.close()

# 結果表示
for record in new_records:
    print(record[0].strftime("%Y/%m/%d %H:%M:%S"), record[1].strftime("%Y/%m/%d %H:%M:%S"), str(record[2]))

こちらのほうがやっている処理がわかりやすくなるので、保守しやすくはなるように思います。
ただ、先に書いたとおり、Python側に極力抽出してくるデータはRDBMS側で絞り込んだほうがパフォーマンスはよいので、一概にPython側でやるのがいいとは言えません。
begginer_pygoさんの利用したいケースによって「うまいやり方」がどれかは違ってくるとは思います。

Answer 2

fetchallすると以下のようなデータ(result)が得られると考えて、その後をpythonで処理する方法です。
日時が文字列でもDatetime型でも同じ方法で可能です。
Start_datetimeがすでに昇順に並んでいるなら、ソートの行は不要です。

出力が違っていたので回答を修正しました。

import pandas as pd
df = pd.DataFrame(result, columns = ["Start_datetime", "End_datetime", "Item"])
df = df.sort_values("Start_datetime")
df2 = df.copy()
df2["End_datetime"] = df["Start_datetime"].shift(-1)
df2["Start_datetime"] = df["End_datetime"]
df3 = df2.iloc[:-1,:]
print(df3)

実行例

>>> print(result)
[('2021/3/1 12:00:00', '2021/3/1 14:00:00', 'Apple'), ('2021/3/1 16:00:00', '2021/3/1 17:00:00', 'Peach'), ('2021/3/1 19:00:00', '2021/3/1 22:00:00', 'Orange')]
>>> import pandas as pd
>>> df = pd.DataFrame(result, columns = ["Start_datetime", "End_datetime", "Item"])
>>> df = df.sort_values("Start_datetime")
>>> df2 = df.copy()
>>> df2["End_datetime"] = df["Start_datetime"].shift(-1)
>>> df2["Start_datetime"] = df["End_datetime"]
>>> df3 = df2.iloc[:-1,:]
>>> print(df3)
      Start_datetime       End_datetime   Item
0  2021/3/1 14:00:00  2021/3/1 16:00:00  Apple
1  2021/3/1 17:00:00  2021/3/1 19:00:00  Peach

Comments

[begginer_pygo]

解答頂きありがとうございます。もう1点だけ質問させてください。
ご提示いただいた方法でdf2の内容で取得できることまでは理解できました。
一方で欲しい形として記載したものとはStart_datetimeが異なってしまっています。
　【欲しい形】
　　[2021/3/1 14:00:00,2021/3/1 16:00:00,Apple]
　　[2021/3/1 17:00:00,2021/3/1 19:00:00,Peach]
Start_datetimeを変える場合もshiftを使う形でしょうか？

[ppaul]

すみません。出力をよく読んでいませんでした。
回答を修正しました。

Answer 3

たぶん Python でやった方がいいとは思いますが、MySQL 8 だとこんな感じですね。

テーブル定義

create table START_END_ITEM (
  Start_datetime datetime,
  End_datetime datetime,
  Item varchar(255)
);

insert into START_END_ITEM values ('2021/3/1 12:00:00', '2021/3/1 14:00:00', 'Apple');
insert into START_END_ITEM values ('2021/3/1 16:00:00', '2021/3/1 17:00:00', 'Peach');
insert into START_END_ITEM values ('2021/3/1 19:00:00', '2021/3/1 22:00:00', 'Orange');

問い合わせ

select End_datetime, Start_datetime, Item from
  (select @n := @n + 1 as n, End_datetime, Item from START_END_ITEM, (select @n := 1) as x) as t1,
  (select @m := @m + 1 as m, Start_datetime from START_END_ITEM, (select @m := 0) as y) as t2
where n = m;

結果

+---------------------+---------------------+-------+
| End_datetime        | Start_datetime      | Item  |
+---------------------+---------------------+-------+
| 2021-03-01 14:00:00 | 2021-03-01 16:00:00 | Apple |
| 2021-03-01 17:00:00 | 2021-03-01 19:00:00 | Peach |
+---------------------+---------------------+-------+

Answer 4

MySQLのバージョンが8.0以降なら**lead()**が使えます。
MySQL 8.0 新機能 Window Function

8.0以降でなければ、模倣する事で同様なことが出来ます。
MySQLで分析関数を模倣3（後編）

SQLでの対応では無い場合は、テーブルをStart_datetimeの降順で読み込んで処理するとかですね。