PythonからPostgreSQLを操作する方法まとめ

'''
psycopg2を使った操作
'''
from psycopg2 import connect

# DB接続
connection = connect(
    user='ユーザ名',
    password='パスワード',
    host='ホスト名',
    port='ポート番号',
    database='データベース名'
)

# テーブル作成
cursor = connection.cursor()
create_teble_query = '''CREATE TABLE テーブル名(
    id SERIAL, # SERIALにすることで1から自動採番される
    created\_at DATE,
    body TEXT,
    PRIMARY KEY(id)
);'''
cursor.execute(create_teble_query)
connection.commit()

# テーブル削除
with connection as conn:
    with conn.cursor() as cur:
        cur.execute('DROP TABLE テーブル名')


# データ取得
with connection as conn:
    with conn.cursor() as curs:
        curs.execute('''SELECT \* FROM テーブル名 WHERE id=%s;''', (id,))
        data = curs.fetchall()
# 留意点
# タプル内のidの後ろにカンマが付いていることに注意。1個でもカンマがないとエラーになる。

# データ更新
id = '3'
value1 = '10000'
value2 = 'abcde'

with connection as conn:
    with conn.cursor() as curs:
        curs.execute('''UPDATE テーブル名 SET value1=%s, value2=%s WHERE id=%s;''',
        (value1, value2, id))

# データ削除
id = '3'

with connection as conn:
    with conn.cursor() as curs:
        curs.execute('''DELETE FROM テーブル名 WHERE id=%s;''', (id,))

'''
sqlalchemyを使った操作
'''
from sqlalchemy import create_engine
import pandas as pd

# DB接続
connection_config = {
    'user': 'ユーザ名',
    'password': 'パスワード',
    'host': 'ホスト名',
    'port': 'ポート番号',
    'database': 'データベース名'
}
engine = create_engine(
    # postgres://～はエラーになるので注意（特にHeroku）
    'postgresql://{user}:{password}@{host}:{port}/{database}'.format(
        **connection_config
    )
)

# DFをテーブルに書き出し
df = pd.read_csv('hoge.csv')
df.to_sql(
    'テーブル名',
    con=engine,
    if_exists='append', # 上書きの場合はreplace
    index=False
)

# テーブルからDFを取得
sql='''
SELECT
  column1 AS 列名1,
  column2 AS 列名2
FROM テーブル名
'''

df = pd.read_sql(sql=sql, con=engine)