problem to access of the properties of an edge when using plpython3u in PostgreSQL to create functions

Question

I implement python function for for the following query and i get correct answer, but when i try to create functions in postgreSQL by using plpython3u extension, it doesn't have any error but still doesn't return the results to the table.

Also, I should mention that the following function works when i want return all the paths, i mean without considering the following part:

for i in range(len(flight) - 1):
     arrival_time = datetime.strptime(flight[i]["arrival_time"], '%Y-%m-%d %H:%M')
     next_departure_time=datetime.strptime(flight[i+1]"departure_time"], '%Y-%m-%d %H:%M')
     if arrival_time < next_departure_time:
            is_continuous = False
            break

That's why i think the problem is the how i want to access to the edge properties. anyone knows how can i fix it?

CREATE OR REPLACE FUNCTION graph.paircpath(
    origin text,
    edge text,
    destination text)
    RETURNS TABLE(city1 ag_catalog.agtype, airport1 ag_catalog.agtype, flight ag_catalog.agtype, airport2 ag_catalog.agtype, city2 ag_catalog.agtype) 
    LANGUAGE 'plpython3u'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
    from datetime import datetime
    import age
    import psycopg2

    def paircpath(origin, edge, destination):
        conn = psycopg2.connect(host="localhost", port="5432", dbname="postgres", user="postgres", password="13711992")
        with conn.cursor() as cursor:
            try:
                cursor.execute("SET search_path = ag_catalog, public, graph;")
                cursor.execute("LOAD 'age';")
                cursor.execute("GRANT USAGE ON SCHEMA ag_catalog TO postgres;")

                query = f"""SELECT * FROM cypher('graph', $$ 
                    MATCH (a)-[:LocatedAt*]->(c:City {{name: '{origin}'}})
                    MATCH (a:Airport)-[e:{edge}]->(b:Airport) 
                    MATCH (b)-[:LocatedAt*]->(c1:City {{name: '{destination}'}}) 
                    RETURN c, a, e, b, c1 
                $$) AS (city1 agtype, airport1 agtype, flight agtype, airport2 agtype, city2 agtype); """

                cursor.execute(query)
                paths = cursor.fetchall()

                for row in paths:
                    city1 = row[0]
                    airport1 = row[1]
                    flight = row[2]
                    airport2 = row[3]
                    city2 = row[4]
                    is_continuous = True
                    for i in range(len(flight) - 1):
                        arrival_time = datetime.strptime(flight[i]["arrival_time"], '%Y-%m-%d %H:%M')
                        next_departure_time = datetime.strptime(flight[i+1]["departure_time"], '%Y-%m-%d %H:%M')
                        if arrival_time < next_departure_time:
                            is_continuous = False
                            break
                    if is_continuous:
                        yield (city1, airport1, flight, airport2, city2)

            except Exception as ex:
                print(type(ex), ex)

    for result in paircpath(origin, edge, destination):
        yield result

$BODY$;

ALTER FUNCTION graph.paircpath(text, text, text)
    OWNER TO postgres;

The function doesn't return and just run like below: results

but by removing the condition:

for i in range(len(flight) - 1):
     arrival_time = datetime.strptime(flight[i]["arrival_time"], '%Y-%m-%d %H:%M')
     next_departure_time=datetime.strptime(flight[i+1]"departure_time"], '%Y-%m-%d %H:%M')
     if arrival_time < next_departure_time:
            is_continuous = False
            break

the function shows all the paths like below: results without condition

user21741413 · Accepted Answer · 2023-04-26 04:49:35Z

0

It seems like there is a problem in the way you are checking if the flight is continuous. Change the following code:

if arrival_time < next_departure_time:
    is_continuous = False
    break

to the code:

if arrival_time > next_departure_time:
    is_continuous = False
    break

answered Apr 26, 2023 at 4:49

user21741413

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

2 Comments

blueogive Over a year ago

Welcome to SO, and thank you for your contribution. Your answer could be improved by explaining how the proposed code change is likely to meet OP's need. In this case, I can see how the code change is likely to get the function to return records, but they are unlikely to be the records OP wants.

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

problem to access of the properties of an edge when using plpython3u in PostgreSQL to create functions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related