I want to export several tables to .json file. First, I have query for those tables I want, matching some condition, the second query, build the json object that I want to store. I am using psycopg2, but when it runs, the loop for the 1st item do its job, and then when the 2nd item in the list is starting it returns:

no results to fetch

    try:
    
        connection = psycopg2.connect(user...)
    
    
        with connection.cursor() as cursor:
            try:
                cursor = connection.cursor()
                cursor.execute(
                    "SELECT table_name FROM information_schema.views WHERE table_schema = 'public' and table_name LIKE 'text%' ORDER BY table_name ASC;")
                tablenames = cursor.fetchall()
                table_names_list = [row[0] for row in tablenames]
                for table in table_names_list:
                    print(table)
                    query=(
                        " SELECT jsonb_build_object( ...) AS feature FROM ( SELECT * FROM {tb} LIMIT 10) inputs ".format(tb=table))
                    sql_to_file=("COPY ({0}) TO STDOUT").format(query)
                    rows=cursor.fetchall()
                    filename = "%s.json" % table
                    print('File: ' + filename + ' created')
                    with open(filename, "w") as f:
                        cursor.copy_expert(sql_to_file, f)
                connection.commit()
            except Exception as e:
                connection.rollback()
                print(e)
    except (Exception, psycopg2.Error) as error:
        print("Error while fetching data from PostgreSQL", error)
    
    finally:
        # closing database connection.
        if connection:
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

I have tried to follow some instruction in the psypcopg2 site and some other threads, but cannot find information regarding this error or how to solve it.