在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

功能_查询.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';

功能_双倍

# -*- 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数据库中修改执行文件查询创建函数的代码。你知道吗

谢谢

Answer 1

这是func的正确代码_双倍. 我在db.session.execute()之后加上db.session.commit()。这是我共享的求解代码。你知道吗

功能_双倍

import os
import click
from flask import current_app
from flask.cli import with_appcontext
from smartapps.extensions import db


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

    @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)

我希望有一天这能救人。 快乐的编码！你知道吗

Answer 2

语法错误是因为行中的关键字错误：

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

第一个单词应该是with，而不是while。你知道吗