PythonWeb - #003 操作PostgreSQL（Flask）

操作PostgreSQL（Flask）

1 目标

本文的目的是使用flask操作PostgreSQL实现增删改查，并实现HPPT谓词接口。

2 代码

from flask import Flask, request, jsonify
import psycopg2
from psycopg2 import sql

# 数据库连接配置
db_config = {
   
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "",
    "database": "postgres"
}

app = Flask(__name__)

def get_db_connection():
    conn = psycopg2.connect(**db_config)
    return conn

# 创建表
@app.route('/create_table', methods=['POST'])
def create_table():
    create_table_query = """
    CREATE TABLE IF NOT EXISTS contacts (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        tel VARCHAR(20),
        geometry GEOMETRY
    );
    """
    try:
        conn = get_db_connection()
        with conn.cursor() as cur:
            cur.execute(create_table_query)
            conn.commit()
        return jsonify(message='Table "contacts" created successfully')
    except Exception as err:
        return jsonify(error=str(err))
    finally:
        conn.close()

# 按名称查询数据
@app.route('/contacts', methods=['GET'])
def query_data_by_name():
    name = request.args.get