[공통교육-파이썬 기초] 플라스크 앱에 데이터베이스 연동

지상준·2022년 4월 1일

Daegu AI School

목록 보기

8/53

1. 학습한 내용

① SQLite

생성된 데이터베이스를 열기 (CLI 프로그램 실행하면서 데이터베이스 선택)
생성된 데이터베이스를 열기 (CLI 프로그램 실행 후 데이터베이스 선택)
데이터베이스가 없다면 생성
테이블 스키마 확인

② Python에 내장된 SQLite API

https://docs.python.org/ko/3/library/sqlite3.html
Read
Create

③ Glitch 코드를 Embed 하는 법

Settings
Copy Embed Code
Apply to Code

④ 수업 중 작성한 코드

from flask import Flask, request, redirect
import sqlite3

app = Flask(__name__)

topics = [
  {"id":1, "title":"html", "body":"html is ...."},
  {"id":2, "title":"css", "body":"css is ...."},
  {"id":3, "title":"js", "body":"js is ...."}
]


def template(content, id=None):
  contextUI = ''
  if id != None:
    contextUI = '<input type="submit" value="delete" class="btn btn-dark">'
  conn = sqlite3.connect('db.sqlite3')
  cs = conn.cursor()
  cs.execute('SELECT * FROM topics')
  topics = cs.fetchall()
  conn.close()
  liTags = ''
  for topic in topics:
    liTags = liTags + f'<li><a href="/read/{topic[0]}/">{topic[1]}</a></li>'
  return f'''
  <html>
    <head>
      <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
      <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" crossorigin="anonymous"></script>
      <style>
        h1{{
          border-bottom:10px solid green;
        }}
        h1>a{{
          text-decoration: none;
        }}
      </style>
    </head>
    <body class="container">
      <h1><a href="/">WEB</a></h1>
      <ol>
        {liTags}
      </ol>
      {content}
      <form action="/delete/{id}/" method="POST">
        <div class="btn-group" role="group" aria-label="Basic example">
            <a href="/create/" class="btn btn-dark">create</a>
            {contextUI}
        </div>
      </form>
      <input type="button" value="night">
      <!-- Copy and Paste Me -->
      <div class="glitch-embed-wrap" style="height: 420px; width: 100%;">
        <iframe
          src="https://glitch.com/embed/#!/embed/slash-nutritious-trowel?path=&previewSize=0"
          title="slash-nutritious-trowel on Glitch"
          allow="geolocation; microphone; camera; midi; encrypted-media; xr-spatial-tracking; fullscreen"
          allowFullScreen
          style="height: 100%; width: 100%; border: 0;">
        </iframe>
      </div>
    </body>
  </html>
  '''

@app.route("/")
def index():
  return template('<h2>Welcome</h2>Hello, WEB!')

@app.route("/read/<int:id>/")
def read(id):
  conn = sqlite3.connect('db.sqlite3')
  cs = conn.cursor()
  cs.execute('SELECT * FROM topics WHERE id=?', (id,))
  topic = cs.fetchone()
  conn.close()
  title = topic[1]
  body = topic[2]
  return template(f'<h2>{title}</h2>{body}', id)

@app.route('/create/')
def create():
  content = '''
    <form action="/create_process/" method="POST">
      <p><input type="text" name="title" placeholder="title"></p>
      <p><textarea name="body" placeholder="body"></textarea></p>
      <p><input type="submit" value="create"></p>
    </form>
  '''
  return template(content)

@app.route('/create_process/', methods=['POST'])
def create_process():
  title = request.form['title']
  body = request.form['body']
  conn = sqlite3.connect('db.sqlite3')
  cs = conn.cursor()
  cs.execute('INSERT INTO topics (title, body) VALUES(?,?)',(title,body))
  id = cs.lastrowid
  conn.commit()
  conn.close()
  return redirect(f'/read/{id}/')


@app.route('/delete/<int:id>/', methods=['POST'])
def delete(id):
  
  conn = sqlite3.connect('db.sqlite3')
  cs = conn.cursor()
  cs.execute('DELETE FROM topics WHERE id = ?',(id,))
  conn.commit()
  conn.close()
  
  return redirect('/')
 
# @app.route('/update/')
# def update():
#   return 'Update'

app.run()