FastAPI: MariaDB に接続

こちらのサンプルのデータベースを Redis から MariaDB に変えました。
FastAPI: 簡単な WebAPI を作成

mariadb01.py

# ------------------------------------------------------------------
#	mariadb01.py
#
#					Mar/18/2022
# ------------------------------------------------------------------
from fastapi import FastAPI
import mysql.connector
from dotenv import load_dotenv
import sys
import os

# ------------------------------------------------------------------
def get_data_proc(sql_str):
	sys.stderr.write(sql_str + "\n")
	conn = mysql.connector.connect(user=user_aa, password=password_aa, \
                              host=host_aa,database=data_base)

	cursor = conn.cursor(dictionary=True)
	cursor.execute (sql_str)
	rows = cursor.fetchall ()
	cursor.close()
	conn.close()
#
	return rows
#
# ------------------------------------------------------------------
dotenv_path = '.env'
load_dotenv(dotenv_path)

host_aa=os.environ.get("HOST")
data_base=os.environ.get("DATABASE")
user_aa=os.environ.get("USER_AA")
password_aa=os.environ.get("PASSWORD")
#

app = FastAPI()

@app.get("/")
def say_hello():
	return {"Hello": "World"}

@app.get("/mariadb_get/")
def read_all():
	dict_aa = {}
	sql_str="select id, name, population, date_mod from cities order by id"
	rows = get_data_proc(sql_str)
#
	return rows

@app.get("/mariadb_get/{key}")
def read_item(key: str):
	sql_str="select id, name, population, date_mod from cities where id = '" + key + "'"
	rows = get_data_proc(sql_str)
	return rows[0]
#
# ------------------------------------------------------------------

.env

HOST='localhost'
DATABASE='city'
USER_AA='scott'
PASSWORD='password'

サーバーの起動

uvicorn mariadb01:app --reload

クライアントからアクセス

$ http http://127.0.0.1:8000/mariadb_get/t3328
HTTP/1.1 200 OK
content-length: 74
content-type: application/json
date: Fri, 18 Mar 2022 08:25:40 GMT
server: uvicorn

{
    "date_mod": "2012-07-26",
    "id": "t3328",
    "name": "高梁",
    "population": 352486
}

$ http http://127.0.0.1:8000/mariadb_get/
HTTP/1.1 200 OK
content-length: 606
content-type: application/json
date: Fri, 18 Mar 2022 08:26:10 GMT
server: uvicorn

[
    {
        "date_mod": "2012-09-12",
        "id": "t3321",
        "name": "岡山",
        "population": 451763
    },
    {
        "date_mod": "2012-03-15",
        "id": "t3322",
        "name": "倉敷",
        "population": 193257
    },
    {
        "date_mod": "2012-10-02",
        "id": "t3323",
        "name": "津山",
        "population": 675241
    },
    {
        "date_mod": "2022-01-09",
        "id": "t3324",
        "name": "玉野",
        "population": 241589300
    },
    {
        "date_mod": "2012-08-14",
        "id": "t3325",
        "name": "笠岡",
        "population": 769358
    },
    {
        "date_mod": "2022-01-09",
        "id": "t3327",
        "name": "総社",
        "population": 16829300
    },
    {
        "date_mod": "2012-07-26",
        "id": "t3328",
        "name": "高梁",
        "population": 352486
    },
    {
        "date_mod": "2012-10-02",
        "id": "t3329",
        "name": "新見",
        "population": 126957
    }
]

参考

Arch Linux で必要なソフトのインストール方法

sudo pacman -S uvicorn
sudo pacman -S python-fastapi
sudo pacman -S python-mysql-connector
sudo pacman -S python-dotenv