python - 执行查询以从 python flask sqlalchemy 中的文件创建函数时出错

Question

我尝试执行 sql 文件以在我的数据库中创建 postgresql 函数。但我有错误。

  File "/home/saputra/Documents/Development/Exercises/Python/flask/ujicoba06/smartapps/__init__.py", line 2, in <module>
    from smartapps.app import create_app
  File "/home/saputra/Documents/Development/Exercises/Python/flask/ujicoba06/smartapps/app.py", line 30, in <module>
    from smartapps.commands import func_db
  File "/home/saputra/Documents/Development/Exercises/Python/flask/ujicoba06/smartapps/commands/func_db.py", line 18
    while current_app.open_resource("commands/func_query.sql") as f:
                                                                ^
SyntaxError: invalid syntax

我的结构：

.
|-- smartapps
|   |-- app.py
|   |-- commands
|   |   |-- func_db.py
|   |   |-- func_query.sql
|   |   |-- init_db.py
|   |   |-- __init__.py

func_query.sql

--function to get staff who don't have user
CREATE OR replace FUNCTION get_staff_not_have_user() 
RETURNS TABLE (
    real_name varchar,
    email varchar
)
AS $$
BEGIN
    RETURN query SELECT 
        st.real_name, 
        st.email  
    FROM 
        public.staff st 
    LEFT JOIN 
        public.users us on us.email = st.email
    WHERE 
        us.email IS NULL;
END;
$$ LANGUAGE 'plpgsql';

func_db.py

# -*- coding: utf-8 -*-
import os
import click
from flask import current_app
from flask.cli import with_appcontext
from sqlalchemy.sql import text
from smartapps.extensions import db


def init_func():
    """Clear existing function and create new one"""
    while current_app.open_resource("commands/func_query.sql") as f:
        db.session.execute(f.read().decode("utf8"))

@click.command("init-func")
@with_appcontext
def func_query_command():
    """Execute sql script"""
    init_func()
    click.echo('Initialized function for SMART Systems.')

def init_app(app):
    app.cli.add_command(func_query_command)

当我从终端运行时，出现错误。如何更正代码以从文件执行查询以在 postgresql 数据库中创建函数。

谢谢

标签： pythonflask

Answer 1

语法错误是由于该行中的关键字错误：

while current_app.open_resource("commands/func_query.sql") as f:

第一个词应该with不是while。