⚗️

【SQL】【PostgreSQL with SQLalchemy】導入チュートリアル

2025/02/17に公開

PostgreSQL

SQL

SQLAlchemy

tech

1. インストール

・インストール

sudo apt install postgresql

2. DB作成

・DB作成権限付与(必要な場合(デフォルトネーム以外でdbを作成したい等)のみ)
※usernameは指定しない場合、PCの名前が使用されることが多い。

sudo -u postgres createuser <username> --createdb

・DB作成

# ユーザーの指定は createdb -U <username> mydb で可能
createdb -U postgres mydb

docker-composeを使う場合の例

docker-compose.yml

services:
  app:
    build: ./app
    container_name: autock_app
    ports:
      - "8888:8888"
    volumes:
      - ./app/src:/root/app/src
    runtime: nvidia  # GPUを使用するための設定
    command: /bin/bash
    depends_on:
      - db
      - nginx
    tty: true  # 対話的なシェルを利用するための設定
    environment:
      - DATABASE_URL=${DATABASE_URL}

  db:
    image: timescale/timescaledb:2.18.1-pg14
    container_name: autock_db
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_DB=${POSTGRES_DB}   # 立ち上げ時にautock_dbという名前のデータベースが作成される
    volumes:
      - db-data:/var/lib/postgresql/data
    ports:
      - "5432:5432"

  nginx:
    build: ./nginx
    container_name: autock_nginx
    ports:
      - "80:80"

volumes:
  db-data:

.env

POSTGRES_USER=postgres
POSTGRES_PASSWORD=example
POSTGRES_DB=autock_db
DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@db:5432/${POSTGRES_DB}

・DBに接続

# \q でDBから抜ける
psql -d mydb

※dockerで立ち上げている場合

psql -h <db_docker_container_name> -U postgres -d <db_name>

・パスワード設定(docker composeでも指定可能)

ALTER USER <username> WITH PASSWORD 'password';

3. 接続

ここからPythonでSQLalchemyを使用してDBを操作していきます。
・ライブラリのインストール

pip install sqlalchemy psycopg

・例1

import os
from sqlalchemy import create_engine, Column, Integer, String, text
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker
import psycopg

# Get the database URL from the environment variable
DATABASE_URL = os.getenv('DATABASE_URL')
# or DATABASE_URL=postgresql://<POSTGRES_USERNAME>:<POSTGRES_PASSWORD>@db:5432/<POSTGRES_DB_NAME> (if not using docker-compose)


# 1. Create a SQLAlchemy engine
engine = create_engine(DATABASE_URL)

# 2. Test the connection by executing a simple query
if __name__ == '__main__':
    with engine.connect() as connection:
        result = connection.execute(text("SELECT 1"))
        print("Test query result:", result.fetchone())

# output
# Test query result: (1,)

・例

# 3. Declarativeベースを作成（ORM用のクラス定義の基底クラス）
Base = declarative_base()

# 4. モデルクラスの定義（usersテーブルに対応）
class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50))
    fullname = Column(String(50))
    nickname = Column(String(50))
    
    def __repr__(self):
        return f"<User(name={self.name}, fullname={self.fullname}, nickname={self.nickname})>"

# 5. テーブルをデータベースに作成
Base.metadata.create_all(engine)

# 6. セッションの作成(実際にDBと通信するためのobj)
Session = sessionmaker(bind=engine)
session = Session()

# 7. 新しいユーザの追加（Create）
new_user = User(name='Alice', fullname='Alice Wonderland', nickname='alice')
session.add(new_user)
session.commit()  # 変更をコミットしてデータベースに反映

# 8. データのクエリ（Read）
user = session.query(User).filter_by(name='Alice').first()
print("取得したユーザ：", user)

# 9. ユーザ情報の更新（Update）
user.nickname = 'ally'
session.commit()  # 更新をコミット

# 10. ユーザ情報の削除（Delete）
session.delete(user)
session.commit()

# output
# 取得したユーザ： <User(name=Alice, fullname=Alice Wonderland, nickname=alice)>

1. インストール

2. DB作成

3. 接続

Discussion