pandas で PostgreSQL の読み書き

Arch Linux で必要なライブラリーのインストール

sudo pacman -S python-sqlalchemy

データベースの作成

postgre_create.py

#! /usr/bin/python
#
#	postgre_create.py
#
#					Mar/05/2023
# ------------------------------------------------------------------
import sys
import pandas as pd
#
from sqlalchemy import create_engine
# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
df = pd.DataFrame([
		["t1271","千葉",58471,"2003-9-25"],
		["t1272","勝浦",49523,"2003-3-16"],
		["t1273","市原",27461,"2003-6-21"],
		["t1274","流山",39872,"2003-8-27"],
		["t1275","八千代",21576,"2003-11-5"],
		["t1276","我孫子",53271,"2003-1-12"],
		["t1277","鴨川",81326,"2003-5-22"],
		["t1278","銚子",95142,"2003-10-18"]])

#print(df)
#
engine = create_engine('postgresql://scott:tiger123@127.0.0.1/city')
#
#
df.to_sql(name='cities',con=engine,if_exists='replace',index=None)
#
#
sys.stderr.write("*** 終了 ***\n")
# ------------------------------------------------------------------

データベースの読み込み

postgre_read.py

#! /usr/bin/python
# -*- coding: utf-8 -*-
#
#	postgre_read.py
#
#					Oct/01/2018
# ------------------------------------------------------------------
import sys
import pandas as pd
#
from sqlalchemy import create_engine
# ------------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
engine = create_engine('postgresql://scott:tiger123@127.0.0.1/city')
#
df=pd.read_sql_query('SELECT * FROM cities', con=engine)
print(df)
#
df.to_csv("tmp001.csv",header=None,index=None)
sys.stderr.write("*** 終了 ***\n")
# ------------------------------------------------------------------

実行結果

$ ./postgre_read.py 
*** 開始 ***
       0    1      2           3
0  t1271   千葉  58471   2003-9-25
1  t1272   勝浦  49523   2003-3-16
2  t1273   市原  27461   2003-6-21
3  t1274   流山  39872   2003-8-27
4  t1275  八千代  21576   2003-11-5
5  t1276  我孫子  53271   2003-1-12
6  t1277   鴨川  81326   2003-5-22
7  t1278   銚子  95142  2003-10-18
*** 終了 ***

確認したバージョン

$ python
Python 3.12.3 (main, Apr 10 2024, 05:33:47) [GCC 13.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas
>>> pandas.__version__
'2.1.4'