Error when execute query to create function from file in python flask sqlalchemy

Question

I try to execute sql file to create postgresql function in my database. But I have error.

  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

My structure:

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

When I run from terminal, I got error. How to correct code to execute query from file to create function in postgresql database.

Thanks

zwirbeltier · Accepted Answer · 2019-07-11 12:07:12Z

1

The syntax error is due to the wrong keyword in the line:

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

The first word should be with not while.

answered Jul 11, 2019 at 12:07

zwirbeltier

9569 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sukma Saputra Over a year ago

Thank you, it's my fault. I don't notice if I wrong spelling.

zwirbeltier Over a year ago

Happy to have helped ;-)

Sukma Saputra · Accepted Answer · 2019-07-11 12:16:08Z

This the correct code for func_db.py. I add db.session.commit() after db.session.execute(). This I share the solve code.

func_db.py

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)

I hope this can save someone one day. Happy coding!

Collectives™ on Stack Overflow

Error when execute query to create function from file in python flask sqlalchemy

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related