Flask - PosgreSQL 연동

psycopg2를 이용한 db 연동

pip install psycopg2

import psycopg2

from flask import Flask
from credentials import DATABASE as DB


app = Flask(__name__)
api = Api(app)

db = psycopg2.connect(dbname=DB['database'],
                      user=DB['user'],
                      host=DB['host'],
                      password=DB['password'],
                      port=5432)
cur = db.cursor()

cur.execute("""CREATE TABLE FRUITS (
                    id INT ,
                    name TEXT,
                    color TEXT
            )""")

cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
               VALUES (1, 'Apples', 'green')""")

cur.execute("""SELECT id, fruit_name, color, price 
             FROM fruits""")

rows = cur.fetchall()

for row in rows:
    print(row)

if __name__ == '__main__':
    app.run(debug=True)

flask_sqlalchemy를 이용한 db 연동

pip install flask_sqlalchemy

from flask import Flask
from flask_restful import Resource, Api
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Column, TEXT, INTEGER

from credentials import DATABASE as DB


app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = f"postgresql://{DB['user']}:{DB['password']}@{DB['host']}/{DB['database']}"
app.debug = True

api = Api(app)
db = SQLAlchemy(app)


class Fruit(db.Model):
    __tablename__ = "test"

    id = Column(INTEGER, autoincrement=True, primary_key=True)
    name = Column(TEXT)
    color = Column(TEXT)


class Check(Resource):
    def get(self):
        rows = Fruit.query.all()
        result = [{
            'id': row.id,
            'name': row.name,
            'color': row.color
        } for row in rows]
        return result


api.add_resource(Check, '/fruit')


if __name__ == '__main__':
    app.run(debug=True)

curl http://127.0.0.1:5000/fruit

[
    {
        "id": 1,
        "name": "apple",
        "color": "red"
    },
    {
        "id": 2,
        "name": "banana",
        "color": "yellow"
    }
]