[Python] Web application from 0! Hands-on (3) -API implementation-

Overview

I had the opportunity to create a web application from scratch with Python, so as a summary! This article is about implementing the API.

I want this person to read

• Those who have no programming experience but want to make an app
• New web programmers
• Experienced but never written a web app in Python

goal

The goal is to create a web app with CURD functionality using HTML, CSS, JavaScript, Python, and SQL.

Things necessary

• PC（Windows OS）
• Internet line
• Exciting feelings

1. Prepare the data in the database

First of all, I will try to connect to the database from the API and get the data. For that, let's prepare test data with pgAdmin. Start pgAdmin4, right-click TodoDatabase> Schemas> public> Tables> todo and select View / Edit Data> All Rows.

title: Test created: 2020-07-31 18:39:00 is_deleted: false And press the Save Data Changes button.

• The id is automatically numbered, so you do not have to enter it.

2. Try connecting to the database from the API

Use the driver for PostgreSQL to connect the database. There are several of these, but I will use the most major (most installed) "psycopg2".

Install with the following command from the command prompt. pip install psycopg2

Then rewrite index.py as follows.

index.py

# -*- coding:utf-8 -*-

#Load an external package
from bottle import route, run
import psycopg2

#Get a connection to the database
def get_connection():
  return psycopg2.connect("host=localhost port=5432 dbname=TodoDatabase user=postgres password=postgres")

#Get Hello World
@route('/hello')
def hello():
  # Hello World!Returns the string
  return "Hello World!"

#Get Todo data
# @route('/todos')By, http://localhost:8080/You can access it with the URL todos
# with get_connection() as conn:Will automatically close the conn at the end of the scope
@route('/todos')
def getTodos():
  with get_connection() as conn:
    with conn.cursor() as cur:
      #Execute SQL
      cur.execute('SELECT * FROM todo')
      #Get 1 result of inquiry to database
      data = cur.fetchone()
      #Convert the data acquired from DB to a character string and return it
      return str(data)

#Web server execution configuration
#URL"http://[host]:[port]/[route]"Becomes the composition of
run(host='localhost', port=8080, debug=True)

Let's move it without thinking for the time being! Press F5 as you did in Hello World and select "Python File" to start the server. Then go to http: // localhost: 8080 / todos in your browser. The data retrieved from the database is displayed!

Summary

We have implemented an API that displays database data!

next time···

If it is left as it is, it is difficult to understand how the data will be returned from the API ... right? (Training) Let's convert the data in the database to json format to make it easier to use!